Personal website of Gregory K. Maxey, Commander USN (Retired)
Do you have ad-blocking software enabled? While I respect your right to do so, your donations and the minimal advertisements on this site help to defray internet and other costs of providing this content. Please consider excluding this website from blocking or turning off the blocker while browsing this site.
The information, illustrations and code contained in my "Microsoft Word Tips" are provided free and without risk or obligation.
However, the work is mine. If you use it for commercial purposes or benefit from my efforts through income earned or time saved then a donation, however small, will help to ensure the continued availability of this resource.
If you would like to donate, please use the appropriate donate button to access PayPal. Thank you!
This Microsoft Word Tips & Microsoft Word Help page is provided for aspiring VBA (Visual Basic for Applications) enthusiasts as a collection of VBA Basics.
Disclaimer: I have had no formal training in computer programming or VBA, so if you close and exit this page now I will completely understand. However, I have had years of hands-on use and experience developing VBA solutions across a range of complexity. In the Navy, on submarines, we called that kind of experience School of the Boat, where hard lessons learned were usually remembered.
In my experience I found that I had written some fairly complex solutions before I had any real understanding of the tools that I was using. In this tips page, I hope to discuss and explain some of those tools in a manner that may save you some hair pulling and help you to avoid tripping over the same stumbling blocks that I have fallen over . Perhaps it will even help you write more efficient code
Acknowledgments: Much of what I have learned and share here is the product the generous help and support that I have received over the years from some of the Titans of Word VBA such as Jay Freedman, Jonathan West, Doug Robbins, Tony Jollans and many others. As is often the case, friend and Word MVP Graham Mayor has been untiring and merciless in checking my work and offering suggestions for improvement. Thanks All!!
VBA is a programming language included as part of Microsoft Office Word (and other Office suite applications) that provides the tools needed to create a solution to virtually any task or objective you face using Word. It can take over when the "Macro Recorder" available from the Word user interface (UI) falls short. It is such a powerful programming tool that, with the appropriate skill, you can completely customize Word and the Word UI.
VBA is a structured programming language, where individual statements are defined using the various building blocks of VBA such as objects, methods, and properties. These VBA statements are grouped in larger blocks called procedures. A procedure is a named group of statements that run as a unit to perform a specific task or calculate a specific result.
Note: The term "macro" is sort of tired slang for "VBA procedure." While the terms "Macro" and "Macros" are used exclusively in the Word UI, a macro is more accurately defined as a recording of VBA code statements which are used to automate a whole series of actions carried out directly from Word.
Accordingly all macros are procedures, but all procedures are not really macros. A macro is generally less effective than defining and using a properly constructed VBA procedure. For more on using the Macro recorder and its shortcomings, see the: Create a Macro Using the Recorder and: How to Modify a Recorded Macro.
If you looked at those articles, then some of the terms in this next block may be familiar. I recommend that you try to grasp and fully understand these basics now rather than later. A little more review and discussion can’t hurt.
The first step to learning how to create procedures is to learn about the building blocks.
VBA is an object-oriented programming language, which means the statements you create in VBA act on specific objects rather than being general commands. The Word application, and individual Word documents, are made of objects that you can manipulate through VBA statements. In fact a document is an object itself, as are individual paragraphs, sentences and words. There are many more types of objects defined in the Word object model. In many cases an object (e.g., Document) is a container for the collection of other objects (e.g., paragraphs, tables, etc.).
A collection is the group of all like objects contained in a parent object. For example, a document object functions as the container object for the collection of many other objects such as section objects, paragraph objects, or table objects. As paragraph object contains collections of sentence and words. A VBA statement that makes reference to an object in a collection uses the objects name or index. For example you might reference a named document object or an indexed paragraph object:
'Return the path of an open document in the documents collection named "VBA Basics.doc" MsgBox Documents("VBA Basics.doc").Path 'Return the text of a first paragraph object in the ActiveDocument object MsgBox ActiveDocument.Paragraphs(1).Range.Text
A method is an action that can be performed on an object. VBA objects are separated from their methods by periods. For example, if you wanted to save a particular file as part of a VBA program you could include the following statement in the code:
Remember, a method is an "action" that you perform on an object. The following screen shot shows some of methods and properties (discussed later) that are associated with the ActiveDocument object (using the Auto List Members feature). Notice the symbol for a method looks a bit like the "action" of hurling a green brick.
Note: I recommend that you keep "Auto List Members" in the VB Editor options checked. "Auto List Members" shows the various methods and properties that can be associated with objects after you type the period "." following an object when writing your code statements.
Properties are used to describe an object. Some properties are read-only, while others are read/write. For example, the document VBA Basics.doc is saved to a particular path on my computer. That path is a property of the document object. The .Path property is read-only as it cannot be changed, without saving the file to a different location. Properties are separated from objects by periods just as methods are. The following statement will display the current path of "VBA Basics.doc", as it is defined on my computer, in an onscreen message box:
If the property is read/write and you set the property equal to something, it changes the current value of that particular property and therefore changes the description of the object. Otherwise VBA can tell you the properties current value. For example, the following statements change the .Name property of the selected text to “Times New Roman” then reports the .Name property applied:
Note: Sometimes a property returns an object. In the example above the "Selection.Font" property returns or sets a "Font" object that represents the character formatting of the specified object.
Also, in the example above, "Selection" is a global property of the global application property of a document object. It returns a selection object that represents the "selected range" or the insertion point in a document. Global objects or properties are top level and do not need to be preceded by the parent object as the following code statements should illustrate:
Each of the statements return the same value. As the "Selection" property is Global it does not require a reference to the "Application" property or the "ActiveDocument" object.
For a structured exercise demonstrating most of the material covered so far see my: VBA Exercise Tutorial
Functions provide information or perform calculations that are useful in building VBA procedures. In the previous examples, the VBA MsgBox function was used to display information on the screen. Other examples of functions include returning the current date or time, or converting data types:
'Date function returns current system date MsgBox Date 'Time function returns current system time MsgBox Time 'Val function returns numbers in a string as a numeric _ value of appropriate type MsgBox Val("1") + Val("1")
1. VBA functions should not be confused with Function procedures. Function procedures will be discussed later.
2. You can use the underscore character to split a single code statement (or comment) over two or more lines. For more on this see: Break VBA Code Lines Using Underscore
An "Event" is an action initiated either by user action or by other VBA code. An "Event Procedure" is a Sub procedure that you design according to the specification of the event. The procedure is called automatically by Word when the event occurs.
For example, a Document object has an "Open" and "Close" event. If you have properly programmed the event procedure for the Open event, Word will automatically call that procedure, always named Document_Open and always located in the "ThisDocument" module of the project, whenever the document is opened.
The following code provides a simple demonstration of the Document_Open and Document_Close events:
Option Explicit 'Module level declaration Dim tStart As Date Private Sub Document_Open() tStart = Now lbl_Exit: Exit Sub End Sub Private Sub Document_Close() Dim tStop As Long tStop = DateDiff("s", tStart, Now) MsgBox "This document has been open for: " & ConvertTimeString(tStop, True) & "." lbl_Exit: Exit Sub End Sub
1. Event procedures must be coded in Class modules. The "ThisDocument" and MSForm modules are special types of class modules. Modules and module types are discussed later.
2. The function "ConvertTimeString" used in the example above is included in the demonstration document that you can download and the end of this tips page.
3. A very experience coder once told me that as a matter of practice he did not like to let his procedures run to the "End Sub" statement and he always provided an "Exit Sub" statement. While I see no harm in running to an End Sub statement, I typically include a label "lbl_Exit:" and Exit Sub statement in my code templates and these examples.
For more on Events and Event procedures see Bill Coan's: Take Control of Microsoft Word Through Events
A "Project" (document or template) is the top level VBA container for storage and organizing your VBA solutions. A project consists of one or more modules.
A module can be one of four types: Code module (or Standard module), Document module (i.e., ThisDocument), MSForm module (or userform module), or Class module.
Note: The document module (ThisDocument) and MSForm modules are actually special purpose class modules.
Code within a module consists of individual lines of code (or statements). There are three types of statements:
Module level statements appear at the top of the module before any procedures (discussed later).
'Declaration statement Dim oRng as Word.Range 'Assignment statement Set oRng = ActiveDocument.Range 'Executable statement oRng.Delete
Note: In addition to the three statement types shown above, you can add "comments" to your code lines. I've already provided several examples of comments. Comments consist of text preceded by the apostrophe character. They may be placed in lines at the end of code statements or in their own lines before or after code statements.
Procedures are the meat and potato organization and storage units of a VBA solution. They are the blocks that get things done.
There are three types of VBA procedures:
Procedures are executed or run (same meaning) in order to apply their statements. When a procedure is run, its statement are processed in a top-down line by line fashion performing the defined operations.
Procedure names can contain a combination of as many as 254 letters, numbers, and the underscore character ( _ ). However, variable names cannot begin with a number, nor can you use reserved keywords that have special meaning to the VBA compiler
Sub Procedure - The most commonly used procedure is the Sub. A Sub procedure is a series of one or more Visual Basic statements enclosed by the Sub and End Sub statements that performs an action or actions but doesn't return a value.
[Private | Public] [Static] Sub name ([Arglist]) [instructions] [Exit Sub] [instructions] End Sub
The following example illustrates a basic Sub procedure:
'The Sub statement (Note: All procedures are public by default) Sub SubExample() 'The instructions 'On Error GoTo Err_Hanlder With Selection.Font 'Set font color property .Color = wdColorRed 'Set font size property .Size = 14 End With 'The Exit statement Exit Sub Err_Handler: 'The End statement End Sub
Note: The Exit Sub statement typically precedes any error handling code statements. For more on error handling see my: Error Handling 101
A Sub procedure can take "parameters," such as constants, variables, or expressions that are passed to it as "arguments" by another "calling" procedure. The following is an example of a Sub that takes parameters passed as arguments from a calling procedure:
Sub Main() 'Other code could go here 'Call and pass arguments to another sub 'Property values for font color _ and size are passed as arguments FormatFontAtSelection Selection.Range, wdColorRed, 14 'Other code could go here lbl_Exit: Exit Sub End Sub Sub FormatFontAtSelection(ByRef oRng As Range, oColor As Long, oSize As Long) 'Parameters With oRng.Font .Color = oColor .Size = oSize End With lbl_Exit: Exit Sub End Sub
Function Procedure - A Function procedure is a series of Visual Basic statements enclosed by the Function and End Function statements.
The following is an example of a Function procedure called from a Sub procedure. In the example the Function procedure returns a value declared with data type "Double" to the calling procedure:
Sub ConvertTemp() Dim dblFahTemp As Double 'Variable declaration Dim dblCelTemp As Double 'Variable declaration 'Use the Inputbox and CDbl functions to get and convert a user input to a double _ variable data type dblFahTemp = CDbl(InputBox("Enter the temperature in degrees Fahrenheit", "Temperature")) 'Get the converted temperature value by passing the Fahrenheit temperature _ as a variable to a Function procedure dblCelTemp = Celsius(dblFahTemp) 'Use the MsgBox function to report the results MsgBox dblFahTemp & " degrees Fahrenheit is " & dblCelTemp & " degrees Celsius." lbl_Exit: Exit Sub End Sub Function Celsius(ByRef dblFahTemp As Double) As Double 'Perform calculations to convert the Fahrenheit value to a Celsius value Celsius = (dblFahTemp - 32) * (5 / 9) 'Apply appropriate number formatting Select Case True Case Celsius = Int(Celsius) Celsius = Format(Celsius, "0;-0") Case Else Celsius = Format(Celsius, "0.00;-0.00") End Select lbl_Exit: Exit Function End Function
Note: The example above included a declaration and extra executable statements for clarity. In practice you can nest the call to the function within the MsgBox function:
Sub ConvertTemp() Dim dblFahTemp As Double 'Variable declaration dblFahTemp = CDbl(InputBox("Enter the temperature in degrees Fahrenheit", "Temperature"))" MsgBox dblFahTemp & " degrees Fahrenheit is " & Celsius(dblFahTemp) & " degrees Celsius." lbl_Exit: Exit Sub End Sub
Property Procedure - A procedure that creates and manipulates properties for a class module. A Property procedure begins with a Property Let, Property Get, or Property Set statement and ends with an End Property statement.
Note: A detailed discussion property procedures, class and MSForm modules are beyond the scope of this tips page, but I have include working examples in the demonstration document that you can download and the end of this tips page.
Variables/Constants are used to store information temporarily. As a procedure is executed, it holds values temporarily in memory. Variables/constants define the name and data type that the procedure associates with specific locations in memory. Sometimes this information will change during the execution of the code (variable) and sometimes it will be static (constant).
Each variable/constant has a specific type that indicates how much memory the data requires and the operations that can be performed on that kind of data.
Note: There will be more on using variables, including declaration statements, data types and scope later in this tips page.
Dim (stands for dimension) and Const (stands for Constant) statements are used to declare variables and constants and allocate storage space. They can appear in a "General Declarations" section at the top of a code module -or- immediately following a procedure declaration. For example:
Sub ConstantVariableDemo() 'Constant value that doesn't change during execution Const pStr As String = "ABCD" 'Variable value that changes during execution (from 1 through 4) Dim i As Long For i = 1 To Len(pStr) MsgBox Mid(pStr, i, 1) Next i For i = 1 To Len(pStr) MsgBox Left(pStr, i) Next i lbl_Exit: Exit Sub End Sub
In almost all cases, you should make it point to declare all variables and constants used in your code and include in the declaration an explicit "data type" clause (i.e., ... as Integer, ... as Long , ... as String, etc.) for the following reasons:
Note: Range is an object (object data type) in the Word object model.
To explicitly declare multiple variables on a single line, you must include the data type for each variable:
To help (or prod) you to use declared variables, I strongly recommend that you include the Module level statement "Option Explicit" as the first statement in all of your project modules. Using the Option Explicit statement forces you to declare every variable using a Dim, Private, Public, ReDim, or Static statement before you can use it. This may appear to add additional lines of code to your solution, but the benefit in manageable code far outweighs the effort involved.
Note: You can ensure that the Option Explicit statement will be added automatically to each new module that you create by checking "Require Variable Declaration" in the VB Editor options.
Note: The compiler will default to the type Variant for a variable that does not have a type specified. A Variant behaves like a chameleon, as it can become whatever type is required for the data assigned to it. This is usually undesirable because it is not memory efficient, it slows performance as VBA has to determine what type of data the Variant represents, and it can result in problematic type conflict errors. However, variables of data type variant can and do serve a useful purpose when it is known that the variable type cannot be determined - such as when capturing freeform entry by users
When you declare variables, you should choose meaningful variable names that describe the variable's purpose. Variable names must meet the same criteria as procedure names. There are 3 levels at which we can declare or dimension (Dim) variables/constants. These are:
In each of these levels the variable/constant differs in scope and lifetime. This is discussed below:
Procedure-Level - These are probably the most widely used. They are declared inside the Procedure itself using the Dim or Const statements. See example below:
Sub ProcLevelDeclaration() Dim i As Long Const pText As String = " seconds and counting" For i = 10 To 1 Step -1 MsgBox i & pText Next i MsgBox "Blast off" lbl_Exit: Exit Sub End Sub
Variables/constants declared at the procedure level are not available to other procedures and they only retain their values for the life of that procedure. As soon as the procedure finishes, the variable/constant and its value are destroyed. This refers to a variable's/constant's scope.
Module-Level (Private) - These are variables/constants that are declared outside the individual procedures at the top of the module. See example below:
Option Explicit Private Cnt As Long Private Const pStr As String = "Testing " Sub Procedure1() For Cnt = 1 To 4 MsgBox pStr & Cnt If Cnt = 4 Then Cnt = 3 Exit For End If Next Cnt Procedure2 lbl_Exit: Exit Sub End Sub Sub Procedure2() For Cnt = Cnt To 1 Step -1 MsgBox pStr & Cnt Next Cnt MsgBox "Test Complete" lbl_Exit: Exit Sub End Sub
Variables/Constants declared using the Private or Private Const statements at the module-level (or within a form's General Declarations section) are available to all procedures within that module or form and they retain their assigned values, unless the Document closes or the End statement is used. However, these variables are not available to procedures outside the module in which they are declared.
Note: Variables and constants declared at the module level are Private by default. You could omit "Private" in the declaration statements shown above.
You can declare multiple variables in a single code statement but be sure to declare each variable explicitly. For example:
'OK Dim pStr1 as String, pStr2 as String, pStr3 As String 'Don't use: Dim pStr1, pStr2, pStr3 As Sting. 'In the case only pStr3 is explicitly declared as a string data _ type. pStr1 and pStr2 are treated as variant data type
Project-Level, Document Level, or Public Module-Level - These are variables/constants that are declared "Public" at the module-level (or within a form's General Declarations section). See example below:
Option Explicit Public Counter As Long Public Const pString As String = "Testing " Sub ThisModProcedure1() For Counter = 1 To 4 MsgBox pString & Counter If Counter = 4 Then Counter = 3 Exit For End If Next Counter 'Call procedure in another module OtherModule.Procedure1 lbl_Exit: Exit Sub End Sub
Variables/Constants declared as Public at the module level are available to all procedures, in all modules within the same project the variables are declared in. Their values are retained unless the Document closes or the "End" statement is used.
Note: Take care in declaring your variables and constants as it is a better practice to use the narrowest possible scope.
Remember, the "End" statement and "End Sub" or "End Function" statements are not the same. If the first procedure in the example above was split into two procedures as shown below, you could run the first procedure and then run the second procedure and the variable/constant values are preserved.
Sub ThisModProcedure2() For Counter = 1 To 4 MsgBox pString & Counter If Counter = 4 Then Counter = 3 Exit For End If Next Counter lbl_Exit: Exit Sub End Sub Sub ThisModuleProcedure2() OtherModule.Procedure1 End Sub
However, if an "End" statement is used in the first procedure then the variable value is not retained when you run the second procedure. You can see this for yourself in the in the examples document that you can download at the end of this tips page.
Static Declaration - Procedures and variables/constants may also be declared using the "Static" statement.
When the Static statement is used to declare a procedure, the procedure's variable remain in scope and retain their values until the document closes or the End statement is used:
Sub RunStaticProcDemo() 'Attempt call to Demo 9 times Demo Demo Demo Demo Demo Demo Demo Demo Demo lbl_Exit: Exit Sub End Sub Static Sub Demo() 'Each time run the value the variable i is preserved Dim i As Long i = i + 1 MsgBox i 'When i grows to a value > 6 stop all code execution If i > 6 Then End lbl_Exit: Exit Sub End Sub
When the Static statement is used to declare a variable, the variable remains in scope and retains its values until the document closes or the End statement is used. Run the following procedure several times as an example:
Sub CostOfPurchase() 'Static variables remain in scope and retain their values after procedure is run. 'Declare variables Static sngTotal As Single Dim sngCostThisItem As Single sngCostThisItem = CSng(InputBox("Enter the cost of a purchase:")) sngTotal = sngTotal + sngCostThisItem 'Display results MsgBox "The cost of a new purchase is: " & sngCostThisItem MsgBox "The running cost is: " & sngTotal lbl_Exit: Exit Sub End Sub
The following table summarizes variable/constant data types.
|Byte||1 byte||none||Positive whole number ranging from 0 through 255 that can be represented as a binary value.|
|Boolean||2 bytes||none||True or False|
|Integer||2 bytes||%||Whole numbers ranging from -32,768 through 32,767.|
|Long (long integer||4 bytes||&||Whole numbers ranging from -2,147,483,648 through 2,147,483,647.|
|Single||4 bytes||!||Single-precision floating-point number (with decimal points) ranging from -3.402823E38 to 3.402823E38.|
|Double||8 bytes||#||Double-precision floating-point number (which is more precise for very large or very small numbers) ranging from -1.79769313486232E308 to 1.79769313486232E308.|
|Currency||8 bytes||@||Large numbers between -922,337,203,685,477.5808 and 922,337,203,685,477.5807 (15 digits to left of decimal and 4 digits to the right of the decimal).|
|Date||8 bytes||none||Represents dates from January 1, 100 through December 31, 9999.|
|Object||4 bytes||none||An instance of a class or object reference.|
|String||10 bytes + 1 byte per char||$||Series of any ASCII characters.|
|String (fixed-length)||length of string||none||Series of any ASCII characters, of a pre-defined length.|
|Variant||min 16 bytes||none||Any kind of data except fixed-length String data and user-defined types.|
Dim Prompt$, varUserInput As Variant
Initialize a String variable with an instruction to appear in an InputBox
Prompt$ = "Please enter something."
' Apply your String variable as an argument for the InputBox function. Use a Variant variable to capture
' the user's entry in an InputBox
VarUserInput = InputBox(Prompt$)
' Display the user's entry by applying your variable within a message dialog
A couple of things to notice about this example are how a type-declaration character (that was the $ sign in the Prompt$ variable) can be applied to define the String variable. Also, you can see how a Variant was purposely used since the user could enter either numbers or text in the InputBox.
For tips on the correct methods for using parenthesis in routines that call and pass arguments to sub-routines or functions, see: Using Parentheses In Code Statements and Calls
When you ran the example, did you notice how the variable values in the main procedure were altered by the changes made to the parameters by the called procedure?
This is the default "ByRef" behavior when passing arguments where a reference to the memory address storing the variable data is passed and changes made to the parameter result in changes to data in the memory address. Since "ByRef" is the default behavior you don't have to explicitly use "ByRef" in your code, but it is a good practice. The following statements are functionally the same:
When ByVal is used the actual value of the argument is passed and changes made to the parameter in the called procedure are not reflected in the variable value of the calling procedure as the following should illustrate:
Sub Main_Procedure2() Dim lngArg As Long, Dim strArg As String lngArg = 1 strArg = "I am what I am." MsgBox "Passing: " & lngArg & " - " & strArg CalledSub_Routine2 lngArg, strArg MsgBox "After Passing: " & lngArg & " - " & strArg lbl_Exit: Exit Sub End Sub Sub CalledSub_Procedure2(ByVal lngPar As Long, ByVal strPar As String) lngPar = lngPar + 1 strPar = "I ain't what I used to be." MsgBox "lngPar = " & lngPar & " - strPar = " & strPar lbl_Exit: Exit Sub End Sub
For additional information and examples for passing arguments ByRef or ByVal see the module "Passing_Arguments" in the VBA Demonstration Document.
Now I would like to move past the basics tools of VBA and discuss just a few points that I feel are best practices.
A range object represents a contiguous area in a document and is defined using a starting and ending character position.
Unlike the Selection object, where you can only have one at time, you can have as many range object variables declared and assigned as you like, in one or many different open documents. A range object has many of the attributes of the Selection object and most (but not all) of the methods that can used with the Selection object can also be applied to a range object.
To start out on this, take a relatively small macro that you have written using the Selection. Put the following line at the start:
You should see that from the material presented earlier that those statements declare a range object variable and assign it so that it is located at the same position as the current selection (i.e., the start and end character position of the range object = the start and end character position of the selection.)
Then, wherever you have a line of code that says Selection.something, replace it with ORng.something.
Remember most, but not all, of the methods that you can use with the selection object can also be used with a range object. You may get some compile errors when you first attempt to execute your modified macro, so take a look and see what the problem is. It is very likely that you have used the HomeKey method of the Selection object to move you to the top of the document, or used some other method that can't be done with a range object.
There is almost always one or more ways of doing the same thing using a method appropriate for a range object. In the case of Selction.HomeKey, you could use:
Once you have gotten used to the idea of using a range object instead of the Selection, you can start getting more ambitious.
Dim oRng As Word.Range Set oRng = ActiveDocument.Range 'Other lines of code would go here. oRng.Move Unit:=wdStory, Count:=-1 oRng.Select Set oRng = Nothing
Note: It is always a good practice to explicitly destroy variable values when you are finished with their value as shown in the final assignment statement in the example above.
If you want to transfer unformatted text between two places (or even between two documents), then you can set a range object equal to the two places and use the properties and methods of the range object to perform the task.
Dim oRng1 As Word.Range Dim oRng2 As Word.Range Set oRng1 = ActiveDocument.Paragraphs(1).Range Set oRng2 = ActiveDocument.Range oRng2.InsertAfter oRng1.Text Set oRng1 = Nothing Set oRng2 = Nothing
Almost everything within a document has a range, so you don't necessarily need to define a range variable. If you know that you want to set the contents of the second cell of the third row of the first table in the document, then you can do it this way:
You can even copy *formatted* text within and between documents without the clipboard, by making use of the FormattedText property.
This example copies the text and formatting from the selection into a new document:
Ranges have a number of advantages over the Selection. They typically require fewer lines of code to accomplish as task and manipulating a range object does not incur the overhead associated with Word having to move or change the selection "highlight" in the active document. This usually results in faster execution.
There are a few occasions there the selection has an advantage, where a range won't really do:
'Shade the 3rd column of the selected table With Selection .Tables(1).Columns(3).Select .Shading.BackgroundPatternColor = wdColorGray60 End With
Other than these 5 specific circumstances, I try to use a range object whenever possible.
As shown earlier, a MsgBox function can be used to display information on the screen. It can also return a user response to a query and when used with other code statements direct the course of execution.
Select MsgBox in a VBA code statement and press F1. The VBA Help file will present a help topic on the MsgBox function with an example. Part of that example is shown below (I have removed the part associated with a help file because I don't have a help file builder to work with).
Dim Msg, Style, Title, Response, MyString 'Define message. Msg = "Do you want to continue?" 'Define buttons. Style = vbYesNo + vbCritical + vbDefaultButton2 'Define title. Title = "MsgBox Demonstration" Response = MsgBox(Msg, Style, Title) 'User chose Yes. If Response = vbYes Then 'Perform some action. MyString = "Yes" 'User chose No. Else 'Perform some action. MyString = "No" End If
While the example works to illustrate the use and purpose of the MsgBox function, I see several things in this example that I don't agree with. First all of the variables are declared as variants. Secondly there really isn't any need for variables at all.
A VBA function can be used with other statements to streamline coding. A more efficient way to use the MsgBox function is shown below where the MsgBox function is combined with the If ... Then … Else statement:
Sub MsgBoxDemo() 'All together in one statement. If MsgBox("Do you want to continue?", vbCritical + cbYesNo + vbDefaultButton2, _ "MsgBox Demonstration") = vbYes Then 'Perform some action. Else 'Perform some other action or do nothing. End If lbl_Exit: Exit Sub End Sub
That's it now! Keep an eye on this page for future additions. I hope you have found this tips page useful and informative.
You can download a Word document containing the examples shown in this tips page plus a working Class and MSForm module: VBA Basics Demonstration Document. For more on userforms see my: Create and Employ a Userform
Do you want to make a payment for consulting work or donate to help support this site?
PayPal is a safe, easy way to pay online.
Use the appropriate currency "Donate" button to make a payment or donation.