General VBA Questions
Why does Excel contain two macro languages?
Excel's two macro languages are Visual Basic for Applications (VBA) and the Excel version 4.0 macro language (XLM). Both languages give you nearly complete control over Excel, but they are very different. Microsoft included both languages in Excel 5 and Excel 95 to ease the transition from XLM to VBA.
Do XLM macros written for previous versions of Excel work in Excel 95?
In most cases they will work perfectly.
Should I learn both macro languages?
No! If you're just starting with Excel, ignore XLM and focus on VBA. The VBA language is more flexible and much easier to learn and use than XLM. In addition, XLM is essentially a dead language, and there's a chance that it will not be enhanced to include new features in subsequent versions of Excel. VBA is destined to be Microsoft's common macro language and eventually will be included in most Microsoft applications.
I'm a Visual Basic 3.0 programmer, and have accumulated many VBX custom controls. Can I use these with VBA?
No. The current version of VBA does not support custom controls.
I'm a Visual Basic 4.0 programmer, and have accumulated many OCX custom controls. Can I use these with VBA?
No. The current version of VBA does not support custom controls.
Is VBA a compiled language?
Yes, sort of. Although it doesn't generate EXE files, the code is compiled into p-code before it is executed, making it faster than an interpreted language.
I often call special functions (including Windows API functions) in DLLs from Visual Basic 3.0 and Visual Basic 4.0. Can I do this in VBA?
Yes. You can often perform otherwise impossible tasks by calling DLL functions directly from VBA.
I've written a DLL file using Visual Basic 4.0. Can I access this DLL with VBA?
If the DLL was written properly, the answer is yes.
Will all of my Excel 5 VBA macros work perfectly with Excel 95?
Most of them will work fine -- with one exception. VBA macros that use 16-bit Windows API calls or calls to 16-bit DLLs will not work with Excel 95. These macros must be re-written to use the 32-bit Windows API or 32-bit DLLs.
Will all of my Excel 95 VBA macros work perfectly with Excel 5?
In most cases, yes. However, Excel 95 has a few enhancements to the language, so if you take advantage of any of these changes, your macro probably won't function properly with Excel 5. In addition, VBA macros that use 32-bit Windows API calls or 32-bit DLL calls will not work with Excel 5.
Can I write VBA procedures that use Windows API calls and DLL calls - and are compatible with both Excel 5 and Excel 95?
Yes you can. You need to declare both API functions (using different names). You need to determine if the code is running in 16-bit Excel or 32-bit Excel and then call the appropriate API function.
How can my VBA code determine if it's running on 16-bit Excel or 32-bit Excel?
You can use the following function, which returns True if Excel is 32-bit.
Function IsXL32Bit() As Boolean
If Left(Application.Version, 1) = "5" Then _
IsXL32Bit = False Else IsXL32Bit = True
End Function
What are the VBA enhancements in Excel 95?
The VBA enhancements generally correspond to the new features in Excel 95. For example, there is a new object (AutoCorrect), and some objects have new properties and methods. For example, the EditBox object now has a PasswordEdit property. In addition, Excel 95 supports OLE document properties, and has some new ways to deal with protected worksheets and workbooks. The enhancements are listed in the online help (search for "Changes to Visual Basic").
Does Microsoft Word for Windows 95 support VBA?
The macro language in Word for Windows 95 is WordBasic. It seems to be similar to VBA, but it's not really. However, you can control Word for Windows with Excel VBA using OLE automation. Word for Windows provides other applications with an object called "Basic." Using this object, VBA can send WordBasic instructions to Word for Windows. But Word for Windows cannot use OLE Automation to access other applications. It's quite likely that a future version of Word for Windows will include VBA.
I need to distribute a workbook to someone who's still using Excel 4. Is there a way to have Excel 95 record my actions to an XLM macro?
Yes. Choose Tools Record Macro Record New Macro, and then click the Options button. In the expanded dialog box click the MS Excel 4.0 Macro option button. And don't forget to save the workbook in the Excel 4 format.
I'm looking for a third-party utility that will convert my Excel 4 macros to VBA. Am I out of luck?
Yes you are. No such utility exists and it is very unlikely that one will be written. Such conversions must be done manually. However, since Excel 95 can execute XLM macros, there is really no reason to convert them. And even if someone would take the time to develop such a utility, the resulting code probably would not be as efficient as the original XLM macros.
I have lots of experience with Excel 4 (XLM) macros, but I'm trying to learn VBA. How can I find the equivalent VBA command for an XLM macro function?
Excel's on-line help system contains a cross-reference of VBA equivalents for XLM macro functions. Access the Contents tab of the Excel help window. Click on Microsoft Excel Visual Basic Reference, then click on More Visual Basic Information, and finally click on the help topic labeled Visual Basic Equivalents for Macro Functions and Commands. To see the VBA statement that is equivalent to an XLM macro function, click the first letter of the macro function and scroll through the list to find the macro function you're interested in.
Is there a way to automatically convert 1-2-3 or Quattro Pro macros to VBA macros?
No way.
Do I really need to be concerned with data types?
For simple VBA subroutines, no. If you don't explicitly declare your variables Excel handles all the details behind the scenes. But if speed and efficient memory use are important to you, you should really declare all of your variables to be of the proper data type.
What is a variant data type?
A variant data type is unique to VBA. Variables that aren't specifically declared are assigned as a variant type, and VBA automatically converts the data to the proper type when it's used. This is particularly useful when getting values from a worksheet cell when you don't know in advance what the cell contains. Generally, it's a good idea to specifically declare your variables (using the Dim statement) because using variants is quite a bit slower.
Sometimes when I attempt to name a cell or range using the Name Box, Excel gives me a "Type Mismatch" error. The name is perfectly valid. What gives?
Most likely, you're trying to use a name that corresponds to one of the VBA or Excel constants (such as xlYes or vbOK). Despite the error message, the name is assigned. If you use the Insert Name command, you won't get this erroneous message.
Where can I find examples of VBA code?
When you install Excel, it places a file called SAMPLES.XLS in your EXCEL/SAMPLES directory. This file has lots of VBA code to peruse. Also, VBA's on-line help contains many short segments of code to demonstrate the use of methods and properties. Look for the word "Example" directly below the help topic name and click it. You can even copy this code to a module to try it out yourself. In addition, online services and Internet web sites contain many Excel files that you can download and study.
I like VBA's on-line help file. Is there an easy way to print the entire help file?
No. However, Microsoft Press will sell you a reference book with the complete help file contents.