John Walkenbach

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.

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.

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.

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.

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.

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.

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.

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.

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.