John Walkenbach

Article / Updated 01-10-2022

The following table provides a list of commonly used VBA statements that you might use when creating macros for Excel. For more information on a particular statement, consult Excel’s Help system. VBA Statement What It Does AppActivate Activates an application window Beep Sounds a tone via the computer's speaker Call Transfers control to another procedure ChDir Changes the current directory ChDrive Changes the current drive Close Closes a text file Const Declares a constant value Date Sets the current system date Declare Declares a reference to an external procedure in a Dynamic Link Library (DLL) DeleteSetting Deletes a section or key setting from an application's entry in the Windows Registry Dim Declares variables and (optionally) their data types Do-Loop Loops through a set of instructions End Used by itself, exits the program; also used to end a block of statements that begin with If, With, Sub, Function, Property, Type, or Select Erase Re-initializes an array Error Simulates a specific error condition Exit Do Exits a block of Do-Loop code Exit For Exits a block of For-Next code Exit Function Exits a Function procedure Exit Property Exits a property procedure Exit Sub Exits a subroutine procedure FileCopy Copies a file For Each-Next Loops through a set of instructions for each member of a collection For-Next Loops through a set of instructions a specific number of times Function Declares the name and arguments for a Function procedure Get Reads data from a text file GoSub...Return Branches to and returns from a procedure GoTo Branches to a specified statement within a procedure If-Then-Else Processes statements conditionally (the Else part is optional) Input # Reads data from a sequential text file Kill Deletes a file Let Assigns the value of an expression to a variable or property Line Input # Reads a line of data from a sequential text file Load Loads an object but doesn't show it Lock...Unlock Controls access to a text file Mid Replaces characters in a string with other characters MkDir Creates a new directory Name Renames a file or directory On Error Gives specific instructions for what to do in the case of an error On...GoSub Branches, based on a condition On...GoTo Branches, based on a condition Open Opens a text file Option Base Changes the default lower limit for arrays Option Compare Declares the default comparison mode when comparing strings Option Explicit Forces declaration of all variables in a module Option Private Indicates that an entire module is Private Print # Writes data to a sequential file Private Declares a local array or variable Property Get Declares the name and arguments of a Property Get procedure Property Let Declares the name and arguments of a Property Let procedure Property Set Declares the name and arguments of a Property Set procedure Public Declares a public array or variable Put Writes a variable to a text file RaiseEvent Fires a user-defined event Randomize Initializes the random number generator ReDim Changes the dimensions of an array Rem Specifies a line of comments (same as an apostrophe [']) Reset Closes all open text files Resume Resumes execution when an error-handling routine finishes RmDir Removes an empty directory SaveSetting Saves or creates an application entry in the Windows Registry Seek Sets the position for the next access in a text file Select Case Processes statements conditionally SendKeys Sends keystrokes to the active window Set Assigns an object reference to a variable or property SetAttr Changes attribute information for a file Static Declares variables at the procedure level so that the variables retain their values as long as the code is running and the project hasn't been reset. Stop Pauses the program Sub Declares the name and arguments of a Sub procedure Time Sets the system time Type Defines a custom data type Unload Removes an object from memory While...Wend Loops through a set of instructions as long as a certain condition remains true Width # Sets the output line width of a text file With Allows a shorthand way of accessing multiple properties for an object Write # Writes data to a sequential text file

Article / Updated 01-10-2022

The VBA language contains a number of functions that you can use to build code in Excel. The following table provides descriptions of the most useful functions. When writing code, type the word VBA followed by a period, and you’ll see a drop-drop list of these functions. See Excel’s Help system for more details. VBA Function What It Does Abs Returns the absolute value of a number Array Returns a variant that contains an array Asc Converts the first character of string to its ASCII value Atn Returns the arctangent of a number CBool Converts an expression to Boolean data type CByte Converts an expression to byte data type CCur Converts an expression to currency data type CDate Converts an expression to date data type CDbl Converts an expression to double data type CDec Converts an expression to decimal data type Choose Selects and returns a value from a list of arguments Chr Converts an ANSI value to a character CInt Converts an expression to integer data type CLng Converts an expression to long data type Cos Returns the cosine of a number CreateObject Creates an OLE Automation object CSng Converts an expression to single data type CStr Converts an expression to string data type CurDir Returns the current path CVar Converts an expression to variant data type CVDate Converts an expression to date data type CVErr Returns a user-defined error type Date Returns the current system date DateAdd Returns a date with a specific date interval added to it DateDiff Returns the difference between two dates as a time interval DatePart Returns an integer containing a specific part of a date DateSerial Returns a date for a specified year, month, and day DateValue Converts a string to date Day Returns the day of the month of a date Dir Returns the name of a file or directory that matches a pattern DoEvents Yields execution so the operating system can process other events EOF Returns True if the end of a text file has been reached Error Returns the error message that corresponds to an error number Exp Returns the base of the natural logarithms (e) raised to a power FileAttr Returns the file mode for a text file FileDateTime Returns the date and time when a file was last modified FileLen Returns the number of bytes in a file Fix Returns the integer portion of a number Format Returns an expression in a particular format FormatCurrency Returns a number as a string, formatted as currency FormatDateTime Returns a number as a string, formatted as a date and/or time FormatNumber Returns a number as a formatted string FormatPercent Returns a number as a string, formatted as a percentage FreeFile Returns the next file number available for use by the Open statement GetAll Returns a list of key settings and their values (originally created with SaveSetting) from an application’s entry in the Windows registry GetAttr Returns a code representing a file attribute GetObject Retrieves an OLE Automation object from a file GetSetting Returns a key setting value from an application’s entry in the Windows registry Hex Converts from decimal to hexadecimal Hour Returns the hour of a time IIf Returns one of two parts, depending on the evaluation of an expression Input Returns a specific number of characters from an open text file InputBox Displays a box to prompt a user for input, and returns the value entered InStr Returns the position of a string within another string InStrRev Returns the position of a string within another string, beginning at the back end of the string Int Returns the integer portion of a number IsArray Returns True if a variable is an array IsDate Returns True if a variable is a date IsEmpty Returns True if a variable has not been initialized IsError Returns True if an expression is an error value IsMissing Returns True if an optional argument was not passed to a Procedure IsNull Returns True if an expression contains no valid data IsNumeric Returns True if an expression can be evaluated as a number IsObject Returns True if an expression references an OLE Automation object Join Returns a string created by joining a number of substrings contained in an array LBound Returns the lower bound of an array LCase Returns a string converted to lowercase Left Returns a specified number of characters from the left of a string Len Returns the length of a string, in characters Loc Returns the current read or write position of a text file LOF Returns the number of bytes in an open text file Log Returns the natural logarithm of a number LTrim Returns a copy of a string with no leading spaces Mid Returns a specified number of characters from a string MidB Returns a specified number of bytes from a specified position in a string string Minute Returns the minute of a time Month Returns the month of a date MonthName Returns a string indicating the specified month MsgBox Displays a modal message box and returns the ID of the button clicked Now Returns the current system date and time Oct Converts from decimal to octal Replace Returns a string in which one substring is replaced with another RGB Returns a number representing an RGB color value Right Returns a specified number of characters from the right of a string Rnd Returns a random number between 0 and 1 Round Rounds a number to a specific number of decimal places RTrim Returns a copy of a string with no trailing spaces Second Returns the second of a time Seek Returns the current position in a text file Sgn Returns an integer that indicates the sign of a number Shell Runs an executable program Sin Returns the sine of a number Space Returns a string with a specified number of spaces Split Returns an array consisting of a number of substrings Sqr Returns the square root of a number Str Returns a string representation of a number StrComp Returns a value indicating the result of a string comparison StrConv Returns a string variant converted as specified String Returns a repeating character or string StrReverse Returns the characters of a string in reverse order Switch Evaluates a list of expressions and returns a value associated with the first expression in the list that is True Tab Positions output in an output stream Tan Returns the tangent of a number Time Returns the current system time Timer Returns the number of seconds since midnight TimeSerial Returns the time for a specified hour, minute, and second TimeValue Converts a string to a time serial number Trim Returns a string without leading and spaces and replaces multiple spaces with a single space TypeName Returns a string that describes the data type of a variable UBound Returns the upper bound of an array UCase Converts a string to uppercase Val Returns the numbers contained in a string VarType Returns a value indicating the subtype of a variable Weekday Returns a number representing a day of the week Weekday Name Returns a string indicating the specified weekday Year Returns the year of a date

Article / Updated 01-10-2022

There are two types of events you can use in VBA programming for Excel 2016 that are not associated with objects: time and keypresses. Because time and keypresses aren’t associated with a particular object such as a workbook or a worksheet, you program these events in a normal VBA module. The OnTime event The OnTime event occurs when a particular time of day occurs. The following example demonstrates how to get Excel to execute a procedure when the 3 p.m. event occurs. In this case, a robot voice tells you to wake up, accompanied by a message box: Sub SetAlarm() Application.OnTime 0.625, “DisplayAlarm” End Sub Sub DisplayAlarm() Application.Speech.Speak (“Hey, wake up”) MsgBox “ It’s time for your afternoon break!” End Sub In this example, the OnTime method of the Application object is used. This method takes two arguments: the time (0.625 or 3:00 p.m.) and the name of the Sub procedure to execute when the time event occurs (DisplayAlarm). This procedure is quite useful if you tend to get so wrapped up in your work that you forget about meetings and appointments. Just set an OnTime event to remind yourself. Most people find it difficult to think of time in terms of the Excel numbering system. Therefore, you may want to use the VBA TimeValue function to represent the time. TimeValue converts a string that looks like a time into a value that Excel can handle. The following statement shows an easier way to program an event for 3 p.m.: Application.OnTime TimeValue(“3:00:00 pm”), “DisplayAlarm” If you want to schedule an event relative to the current time (for example, 20 minutes from now), you can use a statement like this: Application.OnTime Now + TimeValue(“00:20:00”), “DisplayAlarm” You can also use the OnTime method to run a VBA procedure on a particular day. You must make sure that your computer keeps running and that the workbook with the procedure is kept open. The following statement runs the DisplayAlarm procedure at 5 p.m. on December 31, 2016: Application.OnTime DateValue(“12/31/2016 5:00 pm”), “DisplayAlarm” This particular code line could come in handy to warn you that you need to go home and get ready for the New Year’s Eve festivities. Here’s another example that uses the OnTime event. Executing the UpdateClock procedures writes the time to cell A1 and also programs another event five seconds later. This event reruns the UpdateClock procedure. The net effect is that cell A1 is updated with the current time every five seconds. To stop the events, execute the StopClock procedure (which cancels the event). Note that NextTick is a module-level variable that stores the time for the next event. Dim NextTick As Date Sub UpdateClock() ‘ Updates cell A1 with the current time ThisWorkbook.Sheets(1).Range(“A1”) = Time ‘ Set up the next event five seconds from now NextTick = Now + TimeValue(“00:00:05”) Application.OnTime NextTick, “UpdateClock” End Sub Sub StopClock() ‘ Cancels the OnTime event (stops the clock) On Error Resume Next Application.OnTime NextTick, “UpdateClock”, , False End Sub The OnTime event persists even after the workbook is closed. In other words, if you close the workbook without running the StopClock procedure, the workbook will reopen itself in five seconds (assuming that Excel is still running). To prevent this, use a Workbook_BeforeClose event procedure that contains the following statement: Call StopClock The OnTime method has two additional arguments. If you plan to use this method, you should refer to the Help system for complete details. If you’d like to see a rather complicated application, check out this analog clock application. The clock face is actually a chart, and the chart is updated every second to display the time of day. Useless, but fun. An analog-clock application. Keypress events While you work, Excel constantly monitors what you type. Because of this, you can set things up so a keystroke or a key combination executes a procedure. Here’s an example that reassigns the PgDn and PgUp keys: Sub Setup_OnKey() Application.OnKey “{PgDn}”, “PgDn_Sub” Application.OnKey “{PgUp}”, “PgUp_Sub” End Sub Sub PgDn_Sub() On Error Resume Next ActiveCell.Offset(1, 0).Activate End Sub Sub PgUp_Sub() On Error Resume Next ActiveCell.Offset(-1, 0).Activate End Sub After setting up the OnKey events by executing the Setup_OnKey procedure, pressing PgDn moves you down one row. Pressing PgUp moves you up one row. Notice that the key codes are enclosed in braces, not in parentheses. For a complete list of keyboard codes, consult the Help system. Search for OnKey. In this example, On Error Resume Next is used to ignore any errors that are generated. For example, if the active cell is in the first row, trying to move up one row causes an error that can safely be ignored. And if a chart sheet is active, there is no active cell. By executing the following routine, you cancel the OnKey events: Sub Cancel_OnKey() Application.OnKey “{PgDn}” Application.OnKey “{PgUp}” End Sub Using an empty string as the second argument for the OnKey method does not cancel the OnKey event. Rather, it causes Excel to simply ignore the keystroke. For example, the following statement tells Excel to ignore Alt+F4. The percent sign represents the Alt key: Application.OnKey “%{F4}”, ““ Although you can use the OnKey method to assign a shortcut key for executing a macro, you should use the Macro Options dialog box for this task. If you close the workbook that contains the code and leave Excel open, the OnKey method will not be reset. As a consequence, pressing the shortcut key will cause Excel to automatically open the file with the macro. To prevent this from happening, you should include code in your Workbook_BeforeClose event code to reset the OnKey event.

Article / Updated 01-10-2022

If you’re eager to jump into Excel VBA programming, hold your horses for just a moment. First, you need to know some essential background information that assists you in becoming an Excel programmer. VBA advantages You can automate almost anything you do in Excel. To do so, you write instructions that Excel carries out. Automating a task by using VBA offers several advantages: Excel always executes the task in exactly the same way. (In most cases, consistency is a good thing.) Excel performs the task much faster than you can do it manually (unless, of course, you’re Clark Kent). If you’re a good macro programmer, Excel always performs the task without errors (which probably can’t be said about you or me, no matter how careful we are). If you set things up properly, someone who doesn’t know anything about Excel can perform the task by running the macro. You can do things in Excel that are otherwise impossible — which can make you a very popular person around the office. For long, time-consuming tasks, you don’t have to sit in front of your computer and get bored. Excel does the work while you hang out at the water cooler. VBA disadvantages It’s only fair that equal time is given to listing the disadvantages (or potential disadvantages) of VBA: You have to know how to write programs in VBA. Fortunately, it’s not as difficult as you might expect. Other people who need to use your VBA programs must have their own copies of Excel. It would be nice if you could press a button that transforms your Excel/VBA application into a stand-alone program, but that isn’t possible (and probably never will be). Sometimes, things go wrong. In other words, you can’t blindly assume that your VBA program will always work correctly under all circumstances. Welcome to the world of debugging and, if others are using your macros, technical support. VBA is a moving target. As you know, Microsoft is continually upgrading Excel. Even though Microsoft puts great effort into compatibility between versions, you may discover that the VBA code you’ve written doesn’t work properly with older versions or with a future version of Excel.

Article / Updated 01-10-2022

Sometimes, the best route for entering code for Excel 2016 is the most direct one. Entering VBA code directly involves … well, entering the code directly. In other words, you type the code by using your keyboard. Entering and editing text in a VBA module works as you might expect. You can select, copy, cut, paste, and do other things to the text. Use the Tab key to indent some of the lines to make your code easier to read. Indenting isn’t necessary, but it’s a good habit to acquire. A single line of VBA code can be as long as you need it to be. However, you may want to use the line-continuation characters to break up lengthy lines of code. To continue a single line of code (also known as a statement) from one line to the next, end the first line with a space followed by an underscore (_). Then continue the statement on the next line. And don’t forget the space. An underscore character that’s not preceded by a space won’t do the job. Here’s an example of a single statement split into three lines: Selection.Sort Key1:=Range(“A1”), _ Order1:=xlAscending, Header:=xlGuess, _ Orientation:=xlTopToBottom This statement would perform exactly the same way if it were entered in a single line (with no line-continuation characters). Notice that the second and third lines of this statement are indented. Indenting is optional, but it helps clarify the fact that these lines are not separate statements. The white-coated engineers who designed the VBE anticipated that people would be making mistakes. Therefore, the VBE has multiple levels of undo and redo. If you deleted a statement that you shouldn’t have, click the Undo button on the toolbar (or press Ctrl+Z) until the statement shows up again. After undoing, you can use the Redo button to perform the changes you’ve undone. Are you ready to enter some real-live code? Try the following steps: Create a new workbook in Excel. Press Alt+F11 to activate the VBE. Click the new workbook’s name in the Project window. Choose Insert → Module to insert a VBA module into the project. Type the following code in the module: Sub GuessName() Msg = “Is your name “ & Application.UserName & “?” Ans = MsgBox(Msg, vbYesNo) If Ans = vbNo Then MsgBox “Oh, never mind.” If Ans = vbYes Then MsgBox “I must be psychic!” End Sub Position the cursor anywhere within the text you typed and press F5 to execute the procedure. F5 is a shortcut for the Run → Run Sub/UserForm command. If you entered the code correctly, Excel executes the procedure, and you can respond to the simple dialog box. Remember, the text in the dialog box will be different. The GuessName procedure displays this dialog box. When you enter the code listed in Step 5, you might notice that the VBE makes some adjustments to the text you enter. For example, after you type the Sub statement, the VBE automatically inserts the End Sub statement. And if you omit the space before or after an equal sign, the VBE inserts the space for you. Also, the VBE changes the color and capitalization of some text. This is all perfectly normal. It’s just the VBE’s way of keeping things neat and readable. If you followed the previous steps, you just wrote a VBA Sub procedure, also known as a macro. When you press F5, Excel executes the code and follows the instructions. In other words, Excel evaluates each statement and does what you told it to do. (Don’t let this newfound power go to your head.) You can execute this macro any number of times — although it tends to lose its appeal after a few dozen times. For the record, this simple macro uses the following concepts: Defining a Sub procedure (the first line) Assigning values to variables (Msg and Ans) Concatenating (joining) a string (using the & operator) Using a built-in VBA function (MsgBox) Using built-in VBA constants (vbYesNo, vbNo, and vbYes) Using an If-Then construct (twice) Ending a Sub procedure (the last line) Not bad for a beginner, eh?

Article / Updated 01-07-2022

In VBA programming, a function returns a value. You can execute Function procedures and call the function in Excel 2016. Functions, unlike Sub procedures, can be executed in only two ways: By calling the function from another Sub procedure or Function procedure By using the function in a worksheet formula Try this simple function. Enter it in a VBA module: Function CubeRoot(number) CubeRoot = number ^ (1 / 3) End Function This function is pretty wimpy; it merely calculates the cube root of the number passed to it as its argument. It does, however, provide a starting point for understanding functions. It also illustrates an important concept about functions: how to return the value. (You do remember that a function returns a value, right?) Notice that the single line of code that makes up this Function procedure performs a calculation. The result of the math (number to the power of 1/3) is assigned to the variable CubeRoot. Not coincidentally, CubeRoot is also the name of the function. To tell the function what value to return, you assign that value to the name of the function. Calling the function from a Sub procedure Because you can’t execute a function directly, you must call it from another procedure. Enter the following simple procedure in the same VBA module that contains the CubeRoot function: Sub CallerSub() Ans = CubeRoot(125) MsgBox Ans End Sub When you execute the CallerSub procedure, Excel displays a message box that contains the value of the Ans variable, which is 5. Here’s what’s going on: The CubeRoot function is executed, and it receives an argument of 125. The calculation is performed by the function’s code (using the value passed as an argument), and the function’s returned value is assigned to the Ans variable. The MsgBox function then displays the value of the Ans variable. Try changing the argument that’s passed to the CubeRoot function and run the CallerSub macro again. It works just like it should — assuming that you give the function a valid argument (a positive number). By the way, the CallerSub procedure could be simplified a bit. The Ans variable is not really required unless your code will use that variable later on. You could use this single statement to obtain the same result: MsgBox CubeRoot(125) Calling a function from a worksheet formula Now it’s time to call this VBA Function procedure from a worksheet formula. Activate a worksheet in the same workbook that holds the CubeRoot function definition. Then enter the following formula in any cell: =CubeRoot(1728) The cell displays 12, which is indeed the cube root of 1,728. As you might expect, you can use a cell reference as the argument for the CubeRoot function. For example, if cell A1 contains a value, you can enter =CubeRoot(A1). In this case, the function returns the number obtained by calculating the cube root of the value in A1. You can use this function any number of times in the worksheet. Like Excel’s built-in functions, your custom functions appear in the Insert Function dialog box. Click the Insert Function toolbar button, and choose the User Defined category. The Insert Function dialog box lists your very own function. The CubeRoot function appears in the User Defined category of the Insert Function dialog box. If you want the Insert Function dialog box to display a description of the function, follow these steps: Choose Developer → Code → Macros. Excel displays the Macro dialog box, but CubeRoot doesn’t appear in the list. (CubeRoot is a Function procedure, and this list shows only Sub procedures.) Don’t fret. Type the word CubeRoot in the Macro Name box. Click the Options button. Enter a description of the function in the Description box. Click OK to close the Macro Options dialog box. Close the Macro dialog box by clicking the Cancel button. This descriptive text now appears in the Insert Function dialog box. Check out the CubeRoot function being used in worksheet formulas. Using the CubeRoot function in formulas.

Article / Updated 01-07-2022

In some situations, you may want to modify the Excel Ribbon automatically when a workbook or add-in is opened. Doing so makes it easy for the user to access your macro. It also eliminates the need for the user to modify the Ribbon manually by using the Excel Options dialog box. You can make automatic changes to the Ribbon with Excel 2007 and later versions, but it’s not a simple task. Modifying the Ribbon involves writing XML code in a text editor, copying that XML file into the workbook file, editing a bunch of XML files (which also are stashed away inside the Excel file, which in reality is nothing more than a zipped container of individual files), and then writing VBA procedures to handle the clicking of the controls you put in the XML file. Fortunately, software is available to assist you with customizing the Ribbon — but you still need to be on familiar terms with XML. Follow these steps exactly: Create a new Excel workbook. Save the workbook, and name it ribbon modification.xlsm. Close the workbook. Launch the Custom UI Editor for Microsoft Office. If you don’t have this software, you need to find it and install it. Refer to the nearby sidebar “Get the software.” In the Custom UI Editor, choose File  →  Open and find the workbook you saved in Step 2. Choose Insert  →  Office 2007 Custom UI Part. Choose this command even if you’re using Excel 2010, Excel 2013, or Excel 2016. Type the following code in the code panel (named customUI.xml) displayed in the Custom UI Editor: RibbonX code displayed in the Custom UI Editor. <customUI xmlns=‘http://schemas.microsoft.com/office/2006/01/customui’> <ribbon> <tabs> <tab idMso=‘TabHome’> <group id’Group1’ label=‘Excel VBA For Dummies’> <button id=‘Button1’ label=‘Click Me’ size=‘large’ onAction=‘ShowMessage’ imageMso=‘FileStartWorkflow’ /> </group> </tab> </tabs> </ribbon> </customUI> Click the Validate button on the toolbar. If the code has any syntax errors, you get a message that describes the problem. If any errors are identified, you must correct them. Click the Generate Callback button. The Custom UI Editor creates a VBA Sub procedure that is executed when the button is clicked. This procedure is not actually inserted into the workbook, so you need to copy it for later use (or memorize it, if you have a good memory). The VBA callback procedure that is executed by clicking the Ribbon button. Go back to the customUI.xml module and choose File  →  Save (or click the Save icon on the toolbar). Close the file by choosing the File  →  Close command. Open the workbook in Excel, and click the Home tab. You should see the new Ribbon group and Ribbon button. But it doesn’t work yet. Press Alt+F11 to activate the VBE. Insert a new VBA module; paste (or type) the callback procedure that was generated in Step 9; and add a MsgBox statement, so you’ll know whether the procedure is actually being executed. The procedure is Sub ShowMessage(control As IRibbonControl) MsgBox “Congrats. You found the new ribbon command.” End Sub Press Alt+F11 to jump back to Excel, and click the new button on the Ribbon. If all goes well, you see the MsgBox. Proof that adding a new Ribbon command using XML is actually possible. In the Custom UI Editor, when you choose Insert  →  Office 2007 Custom UI Part, you insert a UI part for Excel 2007. The Custom UI Editor also has an option to insert a UI part for Excel 2010 (the software I used has not been updated for Office 2013 or Office 2016). For maximum compatibility, use the Excel 2007 Custom UI Part. You probably realize that modifying the Ribbon using XML is not exactly intuitive. Even with a good tool to help (such as the Custom UI Editor), you still need to understand XML. If that sounds appealing to you, search the web or find a book devoted exclusively to customizing the Ribbon interface in Microsoft Office.

Article / Updated 01-07-2022

If your VBA procedure needs to ask the user for a filename, you could use the InputBox function and let the Excel user do some typing. An input box usually isn’t the best tool for this job, however, because most users find it difficult to remember paths, backslashes, filenames, and file extensions. In other words, it’s far too easy to screw up when typing a filename. For a better solution to this problem, use the GetOpenFilename method of the Application object, which ensures that your code gets its hands on a valid filename, including its complete path. The GetOpenFilename method displays the familiar Open dialog box (a dead ringer for the dialog box Excel displays when you choose File  →  Open  →  Browse). The GetOpenFilename method doesn’t actually open the specified file. This method simply returns the user-selected filename as a string. Then you can write code to do whatever you want with the filename. The syntax for the GetOpenFilename method The official syntax of the GetOpenFilename method is as follows: object.GetOpenFilename ([fileFilter], [filterIndex], [title],[buttonText], [multiSelect]) GetOpenFilename method takes the optional arguments below. Argument What It Does FileFilter Determines the types of files that appear in the dialog box (for example, *.TXT). You can specify several filters for the user to choose from. FilterIndex Determines which of the file filters the dialog box displays by default. Title Specifies the caption for the dialog box’s title bar. ButtonText Ignored (used only for the Macintosh version of Excel). MultiSelect If True, the user can select multiple files. A GetOpenFilename example The fileFilter argument determines what appears in the dialog box’s Files of Type drop-down list. This argument consists of pairs of file filter strings followed by the wildcard file filter specification, with commas separating each part and pair. If omitted, this argument defaults to the following: All Files (*.*), *.* Notice that this string consists of two parts, separated by a comma: All Files (*.*) and *.* The first part of this string is the text displayed in the Files of Type drop-down list. The second part determines which files the dialog box displays. For example, *.* means all files. The code in the following example opens a dialog box that asks the user for a filename. The procedure defines five file filters. Notice that the VBA line-continuation sequence is used to set up the Filter variable; doing so helps simplify this rather complicated argument. Sub GetImportFileName () Dim Finfo As String Dim FilterIndex As Long Dim Title As String Dim FileName As Variant ‘ Set up list of file filters FInfo = “Text Files (*.txt),*.txt,” & _ “Lotus Files (*.prn),*.prn,” & _ “Comma Separated Files (*.csv),*.csv,” & _ “ASCII Files (*.asc),*.asc,” & _ “All Files (*.*),*.*” ‘ Display *.* by default FilterIndex = 5 ‘ Set the dialog box caption Title = “Select a File to Import” ‘ Get the filename FileName = Application.GetOpenFilename (FInfo, _ FilterIndex, Title) ‘ Handle return info from dialog box If FileName = False Then MsgBox “No file was selected.” Else MsgBox “You selected “ & FileName End If End Sub Here is the dialog box Excel displays when you execute this procedure. The appearance may vary, depending on the version of Windows you use and the display options you’ve set. The GetOpen Filename method displays a customizable dialog box and returns the selected file’s path and name. It does not open the file. In a real application, you would do something more meaningful with the filename. For example, you might want to open it by using a statement such as this: Workbooks.Open FileName Notice that the FileName variable is declared as a Variant data type. If the user clicks Cancel, that variable contains a Boolean value (False). Otherwise, FileName is a string. Therefore, using a Variant data type handles both possibilities.

Article / Updated 01-07-2022

Ready to do some Excel 2016 VBA programming? Hopefully, you are because here comes the hands-on part. To start recording your first macro, you need to first find the Macro Recorder, which is on the Developer tab. Unfortunately, Excel comes out of the box with the Developer tab hidden — you may not see it on your version of Excel at first. If you plan to work with VBA macros, you’ll want to make sure that the Developer tab is visible. To display this tab: Choose File  → Options  → Customize Ribbon. The Customize the Ribbon dialog appears. In the Customize the ribbon section on the right side of the dialog, click the Developer checkbox. Click OK. The Developer tab is now on the ribbon. Now that you've got the Developer tab on the ribbon, just follow these instructions carefully and you’ll be well on your way: Create a new workbook. Select a cell. Any cell will do. Choose Developer  →  Code  →  Record Macro or click the macro recording button on the status bar. The Record Macro dialog box appears. The Record Macro dialog box appears when you’re about to record a macro. Enter a name for the macro. Excel provides a default name (something like Macro1), but it’s better to use a more descriptive name. NameAndTime (with no spaces) is a good name for this macro. Click the Shortcut Key box, and enter Shift+N (for an uppercase N) as the shortcut key. Specifying a shortcut key is optional. If you do specify one, you can execute the macro by pressing a key combination — in this case, Ctrl+Shift+N. Make sure the Store Macro In setting is This Workbook. You can enter some text in the Description box, if you like. This step is optional. Some people like to describe what the macro does (or is supposed to do). Click OK. The Record Macro dialog box closes, and Excel’s macro recorder is turned on. From this point, Excel monitors everything you do and converts it to VBA code. Type your name in the active cell. Move the cell pointer to the cell below and enter this formula: =NOW() The formula displays the current date and time. Select the formula cell, and press Ctrl+C to copy that cell to the Clipboard. Choose Home  →  Clipboard  →  Paste  →  Values (V). This command converts the formula to its value. With the date cell selected, press Shift+up arrow to select that cell and the one above it (which contains your name). Use the controls in the Home  →  Font group to change the formatting to Bold and make the font size 16 point. Choose Developer  →  Code  →  Stop Recording. The macro recorder is turned off. Congratulations! You just created your first Excel VBA macro. You may want to phone your mother and tell her the good news.

Cheat Sheet / Updated 12-08-2021

You can get things done faster and be more productive if you know the keyboard shortcuts for your work environment. This cheat sheet provides a list of useful keyboard shortcuts for Excel VBA programmers. If you're new to VBA, try learning just a few of them at a time until they're part of your work habits.