Dialogue with Microsoft VBA Excel team

[Andy Nguyen]

Mr. Riyaz Habibbhai (Program Manager, Microsoft) recently contacted QuantNetwork to seek feedback from our community members. He works with the VSTO, VBA for Excel team at MS and would like to use this thread as a dialogue with Quantnet community members to learn more about the user experience, pet peeves as well as suggestions to make the future products better.

If you work with VBA/Excel on a daily basis at work, this is a good chance to have your comments directly heard by the people working directly on the products.

The comments on this thread will be used by Mr. Riyaz's team as well as other product managers at Microsoft.

Mr. Riyaz is a current member of QuantNetwork

This is not intended as a VBA/Excel problems troubleshoot thread. Please use the Microsoft support forum instead.

 

[doug reich]

A few things off the top of my head.

1. Threading. Now that the calculation engine for excel is threaded, VBA feels like a sucker for not allowing it. And I could get a big speed up that way. (For those haters out there, yes, I am also working on code in a "real" language. But in actuality, you can meet most of our needs by getting a little more speedup from VBA.)

In the VBA Editor:
2. Let me enter VBA "edit" mode, so that if I have events and timed processes on a workbook, I can just pause them while I edit or develop code instead of having VBA throw a fit and screw up the editing. (To see what I mean, set a timer, and then write an incomplete statement somewhere in the same module and just wait with your cursor on that line; VBA gets very upset.)

A bug? (I don't know if this is within the scope of this thread or belongs on support forum)
3. I have some macros that copy sheets, and I find that the behavior in Excel 2003 and 2007 is not only different, but doesn't seem to be configurable with respect to macro links. That is, if I have a UDF or a command button in the original sheet (let's say it's in book A) and those refer to A, then when I copy that sheet (to book B), in Excel 2003 the links will still be to A, but in Excel 2007 they will refer to book B.
In 2007 this happens even if you refer to your macro in A with "BookA.xlsm!myUDF", which to me is the exact wrong behavior.

I'll definitely be back with more things later once I'm warmed up in excel.

 

[John Jurcevic]

Make the look and feel of the VBA code editor the same as that of Visual Studio. I hate that the default debugging keystrokes are different between the two.

Even though it is not related to VBA, the trendline fitting seriously needs an option to fit about an offset. Instead of fitting to x, allow a trendline fit about (x - c) with c being a parameter is determined in the fitting process or optionally set by the user. This would make trendline feature infinitely more useful.

 

[Roger Trimble]

VBA help is not very useful. Typically my search queries yield explanations of error messages. If I want to figure out how to do something in VBA I have to go to the internet and search different forums.

One example: I wanted to dynamically resize named ranges to facilitate manipulating data taken from the worksheet. There is no topic that even comes close to describing how to do this in help.

Another example: Using VBA to connect to an Oracle database is easy. But learning how to do it with VBA help is not.

I also second the edit mode request.

 

[doug reich]

Another thing I recall as a major pet peeve: moving data around worksheets. If I want to copy a single value from one cell to another, it's easy:

' If cellRange1 and cellRange2 are valid Range variables
cellRange1.Value = cellRange2.Value

If I want to set a range of cells equal to an array, it's also easy:

' where cellRange1 is a valid Range, and myArray is a 2x2 Array
cellRange1.Range("A1:B2").Value = myArray

But copying a range of cells into an array or copying several cell values into another range is very hard. The obvious code doesn't work, to my knowledge. The best solution is to do:

cellRange1.Copy 
cellRange2.PasteSpecial paste:=xlPasteAsValues

However, then your copy buffer is all messed up and, at least in Excel 2007, this seems to make it impossible to use copy on the whole system if you're running a macro that does this frequently. (Speaking from immediate experience)

 

[kirylm]

How about the simple fact that you cannot stop the program that is executing and takes very long time to finish? I had this issue few times, when I had to hard kill excel.

 

[doug reich]

Sure you can - Control+Break. It won't break out of a compiled excel function, but once any of those functions finish, you will see the Debug message and you can hit "End".

 

[Riyaz]

Thank you for all the feedback on VBA. Please continue to share your thoughts and concerns. I will follow up with clarifying questions to understand the scenarios better.


Riyaz