VBA Commands and Statements
Is there a VBA command to select the last entry in a column or row. Normally, I can use Ctrl+Shift+down arrow or Ctrl+Shift+right arrow to do this, but how can I do it with a macro?
The VBA equivalent for Ctrl+Shift+down arrow is
Selection.End(XLDown).Select
The constants used for the other directions are XLToLeft, XLToRight, and XLUp.
Is it possible to call a VBA routine from an Excel 4.0 XLM macro?
Yes, by using the Run function. For example, the macro below runs the "Test" subroutine contained in Module1 in workbook BOOK1.XLS.
=RUN(BOOK1.XLS!Module1.Test)
Is there a way to disable the shortcut menus that appear when the user clicks the right mouse button?
Yes. There are actually 25 different shortcut menus, each of which can be selectively disabled using the ShortcutMenus method. For example, to disable the shortcut menu that appears when you right-click a cell, use the following:
ShortcutMenus(xlWorksheetCell).Enabled = False
Refer to the online help for the names of the other shortcut menus.
How can I change the mouse pointer to something else, like an hourglass?
With Excel 95, use the Cursor property of the Application object. To change the mouse pointer to an hourglass, use:
Application.Cursor = xlWait
With Excel 5, you need to perform a Windows API call to change the mouse pointer.
VBA references can get very lengthy, especially when you need to fully qualify an object by referencing its sheet and workbook. Is there a way to reduce the length of these references?
Yes, create an object variable using the Set command. Here's an example:
Dim MyRange as Range
Set _MyRange=Workbooks("MyBook").Worksheets("MySheet").Range("A1")
After the Set statement is executed, you can refer to the Range object simply as MyRange. For example, you can assign a value to the range with:
MyRange.Value=10
Besides making it easier to refer to objects, using object variables can also make your code execute faster.
Is there a way to dimension an array if you don't know how many elements it will have?
Yes. You can dimension the array with the Dim command using empty parentheses, and then redimension with the ReDim command when you know how many elements it has. Use ReDim Preserve if you don't want to lose the current array contents when redimensioning it.
Is there a way to initialize an array without looping through each element?
Yes, by using the Array function - which actually returns a variant that contains an array. Here's an example that initializes an array to hold abbreviations for the first six months of the year.
Months= Array("Jan", "Feb", "Mar", "Apr", "May", "Jun")
I tried the example in the previous question and found that Months(1) returns Feb, not Jan. Is this a bug?
No. Unless you tell it otherwise, VBA uses 0 as the first index number for an array. If you want your arrays to start with 1, insert the following statement at the top of your VBA module:
Option Base 1
Or, you can specify the upper and lower bounds of an array when you dimension it. Here's an example:
Dim Months(1 to 12) as String
What's the difference between variables declared using Global and variables declared using Public?
Nothing. Global is included with VBA for compatibility with other versions of Basic. Public is preferred, but using Global does exactly the same thing.
I'm confused over the Public keyword. According to the Visual Basic User's Guide: "Variables declared using the Public statement are available to all procedures in all modules in all applications unless Option Private Module is in effect; in which case, the variables are Public only within the project in which they reside." This does not seem to be the case. Why?
For this to work, you must use the Tools References command (available only in VBA modules) to register the workbook that contains the reference you want to use.
I'm having trouble using SendKeys to send a left bracket ({) or a right bracket (}). It tells me there's an invalid procedure call.
This was a known bug in Excel 5, and it hasn't been fixed in Excel 95.
I need to prompt a user for an answer and insert the answer into a cell. Is there a way to do this without developing a custom dialog box?
Sure, the InputBox function makes this a snap. The VBA statement that follows demonstrates. In this example, the input box displays a prompt ("Enter a number") and the number entered is entered into cell B1 on the active sheet of the active workbook.
Range("B1").Value = InputBox("Enter a number")
Is there any way to read or write information to INI files using VBA?
Although VBA does not support this directly, you can use a Windows API call to read and write information to INI files. Use the GetPrivateProfileString and WritePrivateProfileString API functions.
Is there any way to read or write information to the Windows registry using VBA?
Although VBA does not support this directly, you can use a Windows API call to read and write information to the Windows registry.
I would like my VBA code to run as fast as possible. Any suggestions?
Yes. Here are a few general tips. Make sure that you declare all of your variables (use Option Explicit at the top of your modules). If you reference an object more than once, create an object variable. Use the With...End With construct whenever possible. Finally, if your macro writes information to a worksheet, turn off screen updating by using Application.ScreenUpdating = False.
I use VBA's FileCopy command to copy files. But unlike every other copy utility I've used, this command changes the file's date and time to the time the copying occurred. Feature or bug?
This was a known bug in Excel 5 and remains a known bug in Excel 95. A clumsy workaround involves changing the system's date and time to the date and time of the file before performing the copy operation. Or, you can use an API call to copy files.