|
Developer FAQs - LanguageThis page contains several FAQs on developing in Excel using VBA Language. |
What is the difference between an Else followed by an If-EndIf and the
If-ElseIf-EndIf structure?
Keywords: Elseif Readability
Posted October 16, 1996
Nothing. The two procedures given below are identical in functionality. If the logic of your program is amenable to using ElseIf and there are more than a couple of nested conditions, use ElseIf. In such cases, using Else followed by If will make your program slither away into the Land of the Unreadable Code.
Sub TestElse()
Dim oSheet As Object
For Each oSheet In ActiveWorkbook.Sheets
If Len(oSheet.Name) = 3 Then
MsgBox oSheet.Name, , "Three Character Name"
Else
If oSheet.Name Like "dlg*" Then
MsgBox oSheet.Name, , "Dialog-Like"
Else
MsgBox oSheet.Name, , "Neither"
End If
End If
Next oSheet
End Sub
Sub TestElseIf()
Dim oSheet As Object
For Each oSheet In ActiveWorkbook.Sheets
If Len(oSheet.Name) = 3 Then
MsgBox oSheet.Name, , "Three Character Name"
ElseIf oSheet.Name Like "dlg*" Then
MsgBox oSheet.Name, , "Dialog-Like"
Else
MsgBox oSheet.Name, , "Neither"
End If
Next oSheet
End Sub
Which is better, Select-Case or If-ElseIf? Doesn't If-ElseIf involve more checks?
Keywords: Select Case Elseif
Posted October 16, 1996
As explained below, the Select-Case and If-ElseIf statement structures serve different purposes. There are situations when both can be used. For instance,
Select Case iValue
Case Is < 1:
MsgBox "Zero or less."
Case 1:
MsgBox "Case 1."
Case 2:
MsgBox "Case 2."
Case 3 To 10
MsgBox "Case 3-10."
Case Else
MsgBox "Case 11 and above."
End Select
This can also be written as
If iValue < 1 Then
MsgBox "Zero or less."
ElseIf iValue = 1 Then
MsgBox "Case 1."
ElseIf iValue = 2 Then
MsgBox "Case 2."
ElseIf iValue > 2 And iValue < 11 Then
MsgBox "Case 3-10."
Else
MsgBox "Case 11 and above."
End If
Here, the conditions being checked are such that both structures are usable. Which one you use is more a question of stylistic preference.
Use Select-Case wherever you can, when the logical condition has the same variable or expression on the left side of the condition. When the checks are more complex, use If-ElseIf. Specifying a range of values and many Or conditions is easier in the Case statement. For a more detailed comparison, see the next FAQ.
Coming to the question of more checks, no, Select Case does not behave differently from If-ElseIf. Each Case condition is evaluated in sequence until a condition is satisfied so if you called a function in each check, the function might get called multiple times.
When should I use If-ElseIf instead of Select Case?
Keywords: Select Case Elseif Guidelines color="#0000FF"
Posted October 16, 1996
First of all, under the following conditions, you cannot use a simple Select Case structure.
Though it is possible to mix If-EndIf checks within Case statements, in the above situations, consider using the If-ElseIf structure.
When both structures are usable, use the following as guidelines:
''' Example code snippets illustrating above points
Sub Example1() ''' Use ElseIf when left side has different expressions
If EmpType = "Senior Management" Then
IgnoreRules
ElseIf EmpTrackRecord = "OK" Then
ApplyMinimumRules
ElseIf EmpType = "Middle Management" Then
ApplyAllRules
Else
ApplyRules
End If
''' *** See how the above looks when rewritten using a Select Case
Select Case EmpType
Case "Senior Management"
IgnoreRules
Case "Middle Management"
If EmpTrackRecord = "OK" Then
ApplyMinimumRules
Else
ApplyAllRules
End If
Case Else
If EmpTrackRecord = "OK" Then
ApplyMinimumRules
Else
ApplyRules
End If
End Select
End Sub
Sub Example2() ''' Use ElseIf when you need Like comparison
If City Like "A*" Then
MsgBox "A-class city."
ElseIf City Like "Hi*" Then
MsgBox "Hi-class city."
ElseIf City Like "*[*]*" Then
MsgBox "Starred city."
Else
MsgBox "Simply city."
End If
End Sub
Sub Example3() ''' Use ElseIf when conditions read better in a sequence
If Age > 65 Then
MsgBox "Rating 1."
ElseIf Age > 15 And Age < 36 Then
MsgBox "Rating 2."
ElseIf Age > 5 Or Special = True Then
MsgBox "Rating 3."
Else
MsgBox "Rating 4."
End If
End Sub
How do I specify And conditions in a Select Case structure? I know I can use commas
for Or conditions?
Keywords: Select Case And color="#0000FF"
Posted October 16, 1996
The Case statement does not provide a direct way to use And and Like. Use If-ElseIf for such situations. There is a way to use Select Case for complex conditions but it is a poor substitute for If-ElseIf.
''' Nut Case
Sub UseSelectCase()
Select Case True
Case szName Like "A??" And szType = "OK"
MsgBox "OK 3."
Case szName Like "Bo*" And (iValue <0 Or iValue> 9)
MsgBox "Bo OK."
Case Else
MsgBox "Thou shalt not use If-ElseIf."
End Select
End Sub
Why does Format(Now, "mm:ss") fail in VBA erratically whereas the TEXT()
worksheet function works?
Keywords: Date Format VBA Month Minutes color="#0000FF"
Posted September 10, 1996
Microsoft Excel interprets the format "mm:ss" as minutes and seconds while Visual Basic interprets it as months and seconds. This is by design. To display minutes and seconds, Visual Basic requires the format to be specified as "nn:ss". However, if your format string is "hh:mm:ss", Visual Basic guesses correctly that you need hours, minutes and seconds! That explains the erratic behavior. Verify this using:
MsgBox Application.Text(Now, "mm:ss"), , "Text is okay" MsgBox Format(Now, "mm:ss"), , "Format fails with mm" MsgBox Application.Text(Now, "nn:ss"), , "Funny Text" MsgBox Format(Now, "nn:ss"), , "Format works with nn" MsgBox Format(Now, "hh:mm:ss"), , "Format works with hh:mm"
What's the difference between the XLS and the XLA file created using Tools, Make
Add-In...?
Keywords: XLS XLA Differences Add-In color="#0000FF"
Posted May 18, 1996
In an XLS some modules may not be compiled. In an XLA, all modules are compiled when you make the add-in. An Add-in (XLA) also has the following features:
An in-depth discussion of the VBA differences can be found in our VBA Performance white paper color="#0000FF">.
Is VBA a compiled language?
Keywords: VBA Compiled color="#0000FF"
Posted May 16, 1996
Partially. VBA is compiled to a p-code (packed code). This is currently not as fast as a fully compiled language like C or Pascal but it's often dramatically faster than the older XLM style macros.
For a detailed study of VBA and the compile process, download our white paper titled VBA - Maximum Performance vs. File Size
Can I use my VBX and OCX custom controls in VBA? color="#000000"
Keywords: VBX OCX Custom Controls color="#0000FF"
Posted May 16, 1996
No. Excel 5 & 95 don't support the custom controls that work with Visual Basic 3.0 and Visual Basic 4.0. Future versions of Microsoft Excel will probably support OCX custom controls.
Can my code span multiple rows?
Keywords: Line Continuation Span Multiple Rows
Posted May 16, 1996
Yes, VBA provides a "line continuation" character (an underscore) which allows you to continue your code on a second line. In practice, you need a space and the underscore. This allows you to format your code so it will show up on a standard monitor without scrolling left to right. Here's an example: color="#000000">
Selection.Sort Key1:=Range("C2"), Order1:=xlDescending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _
xlTopToBottom
As a general rule, it's best to indent lines that are continued. This alerts you to the fact that the indented lines are associated with the unindented line above.
How do I trap keystrokes so that my macro is run whenever, say, Ctrl-S is pressed?
Keywords: Keyboard Custom OnKey Macro color="#0000FF"
Posted May 8, 1996
Use the OnKey method of the Application object to assign your custom macros to specified key combinations. You can specify any key combination including the Ctrl, Shift and Alt modifiers, function keys (F1 to F15), and special keys like TAB and ENTER. The following examples show you how to do this.
(See important note below examples)
''' Trap and call the OurSaveProcedure() macro when Control-S is pressed
Application.OnKey "^s", "OurSaveProcedure" ''' Prefix ^ (caret) for Ctrl key
''' Examples of bad practice. Assign standard keys to unrelated functions
Application.OnKey "a", "SomeMacro"
Application.OnKey "^c", "SomeProc"
''' Disable the shortcut key for Cut, namely, Ctrl-X
Application.OnKey "^x", ""
''' Re-enable the shortcut key for Paste, namely, Ctrl-V
Application.OnKey "^v" ''' Omit second parameter to reset
''' Assign the exit macro to Alt-X
Application.OnKey "%x", "ExitApp" ''' Prefix % (percent) for Alt key
''' Call the validation routine when Shift-F4 is pressed
Application.OnKey "+{F4}", "ValidateInput" ''' Prefix + (plus) for Shift key
''' To pass arguments, enclose entire string in single quotes
Application.OnKey "^s", "'OurSaveProcedure 1, 2"
Use the power of OnKey, keeping in mind the following guidelines:
How do I disable a keyboard shortcut like Ctrl-A?
Keywords: Disable Keyboard Shortcut OnKey color="#0000FF"
Posted May 8, 1996
Using the OnKey method of the Application object, you can assign custom macros or disable specified key combinations. To disable a key combination, pass a blank string ("") as the second argument to OnKey. See the following examples:
''' Disable the shortcut key for Select All, namely, Ctrl-A
Application.OnKey "^a", ""
''' Re-enable the shortcut key
Application.OnKey "^a" ''' Omit second parameter to reset
''' Assign Ctrl-A to our own version of select all
Application.OnKey "^a", "OurSelect"
How can I create VBA code that runs on both 16-Bit and 32-Bit platforms?
Keywords: VBA API Compilation GetPrivateProfileString
Posted March 22, 1996
The primary obstacle to creating an add-in that will run on both 16-bit and 32-bit platforms is the use of API calls. Two techniques are currently available for working around the problem of API calls.
Conditional Compilation
Conditional compilation is only available in Visual Basic 4.0 currently. Conditional
compilation allows you to write code like this:
#If Win32 Then
lRes = RegOpenKeyEx(HKEY_CLASSES_ROOT, szClass, 0, 1, lRoot)
#Else
lRes = RegOpenKey(HKEY_CLASSES_ROOT, szClass, lRoot)
#End If
The downside of this is that once compiled your code is platform specific. In Visual Basic 4.0 this means an executable for each platform. This is not as bad as it sounds. Since Visual Basic 4.0 is a compiled language it creates platform specific executables by the very nature of compiling. The upside is that it allows a user to maintain a single set of source code.
Conditional Execution
Conditional execution is the solution of choice for Office applications where all code is
interpreted and has the promise of being platform independent between Win16, Win32, and
the Macintosh. VBA, as implemented in Microsoft Office, does not load a DLL until the DLL
is called for the first time. This means that the programmer can declare all versions of
an API call and then at runtime only call the one that is appropriate to the platform the
code is running on. This technique can sometimes cause collisions in the name space for
functions. Judicious use of the Declare statements Alias option can solve this problem
quite handily and eliminate much confusion. An example of this technique is presented
below:
'''
''' Function: IsWin16
'''
''' Comments: Used to determine if in Win32 or Win16
'''
''' Returns: True for Win16, False for Win32
'''
''' Keywords: WIN16 WIN32
'''
''' Date Programmer Comments
''' -------------------------------------------------------------
''' 1/10/95 Chris Kinsman Created
Function IsWin16()
IsWin16 = InStr(1, Application.OperatingSystem, "32-bit") = 0
End Function
'''
''' Win16 Declares
'''
Declare Function GetPrivateProfileString16 Lib "KERNEL" Alias "GetPrivateProfileString" (ByVal lpszSection As String, ByVal lpszEntry As String, ByVal lpszDefault As String, ByVal lpszReturnedString As String, ByVal iSize As Integer, ByVal lpFileName As String) As Integer
Declare Function WritePrivateProfileString16 Lib "KERNEL" Alias "WritePrivateProfileString" (ByVal lpszSection As String, ByVal lpszEntry As String, ByVal lpszString As String, ByVal lplFileName As String) As Boolean
Declare Function GetPrivateProfileInt16 Lib "KERNEL" Alias "GetPrivateProfileInt" (ByVal lpSectionName As String, ByVal lpszEntry As String, ByVal iDefault As Integer, ByVal lpszFilename As String) As Integer
Declare Function GetPrivateProfileKeys16 Lib "KERNEL" Alias "GetPrivateProfileString" (ByVal lpszSection As String, ByVal lEntry As Long, ByVal lpszDefault As String, ByVal lpszReturnedString As String, ByVal iSize As Integer, ByVal lpFileName As String) As Integer
'''
''' Win32 Declares
'''
Declare Function GetPrivateProfileString32 Lib "KERNEL32" Alias "GetPrivateProfileStringA" (ByVal lpszSection As String, ByVal lpszEntry As String, ByVal lpszDefault As String, ByVal lpszReturnedString As String, ByVal iSize As Integer, ByVal lpFileName As String) As Integer
Declare Function WritePrivateProfileString32 Lib "KERNEL32" Alias "WritePrivateProfileStringA" (ByVal lpszSection As String, ByVal lpszEntry As String, ByVal lpszString As String, ByVal lplFileName As String) As Boolean
Declare Function GetPrivateProfileInt32 Lib "KERNEL32" Alias "GetPrivateProfileIntA" (ByVal lpszSection As String, ByVal lpszEntry As String, ByVal iDefault As Integer, ByVal lpszFilename As String) As Integer
Declare Function GetPrivateProfileKeys32 Lib "KERNEL" Alias "GetPrivateProfileStringA" (ByVal lpszSection As String, ByVal lEntry As Long, ByVal lpszDefault As String, ByVal lpszReturnedString As String, ByVal iSize As Integer, ByVal lpFileName As String) As Integer
'''
''' Function: GetPrivateProfileString
'''
''' Comments: This routine places a wrapper around the API call, GetPrivateProfileString.
''' The appropriate Win16 or Win32 routine is called.
'''
''' Arguments: lpszSection - String containing the name of the section
''' lpszEntry - String containing the name of the entry
''' lpszDefault - String to return if lpszEntry not found
''' lpszReturnedString - String containing return value. Should be filled using Space(255) prior to call.
''' iSize - Number of bytes in lpszReturnedString. Use Len(lpszReturnedString).
''' lpszFileName - String containing the name of the .INI file.
'''
''' Returns: int - Number of characters retrieved.
'''
''' Date Developer Action
''' -----------------------------------------------------------------------------------
''' 1/10/95 Chris Kinsman Created
Function GetPrivateProfileString(ByVal lpszSection As String, ByVal lpszEntry As String, ByVal lpszDefault As String, ByRef lpszReturnedString As String, ByVal iSize As Integer, ByVal lpszFilename As String) As Integer
''' Check if space is allocated in lpszReturnedString, if not allocate some
If Len(lpszReturnedString) = 0 Then
lpszReturnedString = Space(255)
iSize = Len(lpszReturnedString)
End If
''' Call appropriate API
If IsWin16() Then
GetPrivateProfileString = GetPrivateProfileString16(lpszSection, lpszEntry, lpszDefault, lpszReturnedString, iSize, lpszFilename)
Else
GetPrivateProfileString = GetPrivateProfileString32(lpszSection, lpszEntry, lpszDefault, lpszReturnedString, iSize, lpszFilename)
End If
End Function
Will my XLM macros continue to run in Excel 5 and Excel 7?
Keywords: XLM Macro Compatible color="#0000FF"
Posted January 16, 1996
Yes, they will run, but new features added to Excel 7 may not be available in the XLM macro language. In other words, you can still read and write XLM macros, but the available feature set and support is fixed at the Excel 5 level. We recommend you work toward developing all new materials in VBA, but continue to run and enhance existing projects in either XLM or VBA. In our case our Baarns Utilities product is a mix of XLM and VBA. We developed the original version under Excel 4 and then upgraded it through Excel 5 and Excel 7. Any new features are added with VBA but older features are maintained in XLM. From the user's experience, there's no difference. From a developer's perspective, VBA is the future and is a much stronger development environment then XLM.
Where's the utility to translate XLM macros to VBA?
Keywords: XLM Translate VBA
Posted January 16, 1996
There isn't one! XLM and VBA are so different in concept that any translator (if you could do it) would be a major undertaking and the quality of the code produced would certainly be questionable at best. Considering that XLM continues to run without modification, it doesn't need to be translated.
What's the difference between XLM (AKA Excel 4 macro language) and VBA (Visual Basic
for Applications) macros?
Keywords: XLM VBA Difference
Posted January 16, 1996
XLM was the original macro language of Microsoft Excel and was a giant step up from the keystroke macro languages that preceded it. It was based on a function metaphor and created programming constructs that resembled functions used in regular spreadsheets. Expert Excel developers can create complete custom applications programming in XLM. This language is very powerful but limited by its very specific, spreadsheet based design roots.
VBA is a more general purpose, next generation development language with roots in Visual Basic object model concepts. The language itself is strong and flexible enough to be used in multiple products, including Excel 5 & 7, Access 7, Visual Basic 4.0, Project, PowerPoint and future versions of Word. This allows users to leverage their knowledge between products and not learn a "spreadsheet" programming language and a "word processor" language.
VBA makes application integration much easier. With XLM the developer was required to use DDE to control other applications. DDE was slow and fragile. VBA uses a new technique based on OLE 2.0 called OLE Automation. OLE Automation allows for the control of other applications using a variant of the "object model" that the programmer is already used to.
Back to the FAQ Table of Contents
 What do you get when you cross Lee Iacocca with Dracula? - AUTO EXEC BAT |
Copyright© 1996-1999, Baarns Consulting Group, Inc. - All rights reserved. |