Monday, June 11, 2007

Maximum Length of Macros

Alan Wyatt has some great Excel tips.

I found this one the other day.
There actually is a maximum length for a VBA macro. Fortunately there are some solutions if you get this situation occurring. Read Alan's write up about it.

Labels: ,


Posted by: Jethro | 10:26 AM |

Friday, May 25, 2007

Excel VBA Macro Shortcut Keys

VBA Macro shortcut keys can be created when recording a Macro. It pays to make sure you use a combination NOT used by some other shortcut. Eg don't use CTRL + S.
I had a client who recorded two macros and used CTRL + S and CTRL + Z for them. I found them when I tried to save the file and undo an edit.

Fortunately you can change them.
Go to Tools | Macro | Macros and select the Macro you want to change the shortcut key for. Click Options and a Dialog Box will appear allowing you to reset (or set) a shortcut key.

Remember that this only works for the macro in the specific workbook that this Macro is recorded in.

Labels: , ,


Posted by: Jethro | 11:02 AM |

Thursday, May 03, 2007

List of Macro Shortcuts in All Open Workbooks

Allen Wyatts Excel Tips has a beauty on extracting fromyour VBA the list of Macro Shortcut Keys in your VBA project.

Summary: Need a list of macro shortcut keys? It's not as easy in Excel as in some other Office applications, such as Word. It can be done, however, with a little ingenuity, as described in this tip. (This tip works with Microsoft Excel 97, Excel 2000, Excel 2002, Excel 2003, and Excel 2007.)

If you develop a lot of macros, you may want to list all those macros along with the shortcut keys used to initiate them. Of course, coming up with the code to list the shortcut keys is the tricky part of this problem, as such an ability is not built into Excel directly. (You can do it in Word, but not in Excel. Go figure.)

Read the whole article and code on his site.

Labels: , ,


Posted by: Jethro | 9:52 AM |

Tuesday, February 13, 2007

VBA Declaration of Variables

In VBA you are not required to declare a variable. This is bad programming practice but nevertheless is allowed. There is an option to force the declaration of variables and that is to use the Option Explicit command at the top of a module.

However the next poor programming “feature” within VBA is that variable declarations can be made without assigning the type.
E.g. Dim testString. This will create a variable called testString as a type variant which is the default. It would be better to declare the variable as Dim testString As String. This actually forces the variable to be a string. This makes it easier to trap errors and reduces the memory used.

However VBA also allows you to declare multiple variables on one line. E.g. Dim upper, lower As Single. Unfortunately as I found out today although the syntax for this is valid, only the very last variable in this line will be assigned as the variable type Single. All others will be given Variant (as the default). The way to get around this is to write the line as following; Dim upper As String, lower As String.
This then achieves the desired result of all variables declared with the correct type.

Labels: ,


Posted by: Jethro | 3:24 PM |