Microsoft 365 Excel VBA Programming For Dummies

No one is going to become a VBA expert in one day. Excel VBA is a journey of time and practice. The good news is that plenty of resources are out there that can help you on your path to Excel VBA prowess. Here, you discover ten of the most useful places to turn to for Excel VBA help when you need an extra push in the right direction.

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.

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.

As you become proficient with Excel VBA, you spend lots of time working in Code windows. Macros that you record are stored in a module, and you can type Excel VBA code directly in a VBA module. Minimizing and maximizing VBA code windows If you have several projects open, the VBE may have lots of Code windows at any given time.

If you are trying to get a good grasp on Excel VBA, you probably could benefit from a few examples to develop that visual basic prowess. Here, you find a few Excel VBA examples so that you can get the hang of this event-handling business. Excel VBA example: The Open event for a workbook One of the most commonly used Excel VBA events is the Workbook Open event.

This Excel add-in example discusses the basic steps involved in creating a useful add-in which you can use to package your VBA procedures. This Excel VBA example is based on the Change Case text conversion utility. Setting up the Excel workbook The Excel workbook consists of one blank worksheet, a VBA module, and a UserForm.

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.Standard Visual Basic Editor shortcut keysAs you work with the Visual Basic Editor, you might want to navigate the windows using keyboard shortcuts instead reaching for the mouse.

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.

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.

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.

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.

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.

Excel VBA provides numerous built-in functions. Some of these Excel VBA functions take arguments, and some do not. Excel VBA function examples Here, you find a few examples of using VBA functions in code. In many of these examples, the MsgBox function displays a value in a message box. Yes, MsgBox is a VBA function — a rather unusual one, but a function nonetheless.

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 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.

Like humans, pets, and hurricanes, every Excel VBA Sub and Function procedure must have a name. Although it’s perfectly acceptable to name your dog Hairball Harris, it’s usually not a good idea to use such a freewheeling attitude when naming Excl VBA procedures. When naming Excel VBA procedures, you must follow a few rules: You can use letters, numbers, and some punctuation characters, but the first character must be a letter.

In some cases, you may want the user to select an Excel VBA range while a dialog box is displayed. An example of this type of Excel VBA range selection occurs in the Create Table dialog box, which is displayed when you choose Home → Insert → Tables → Table. The Create Table dialog box has a range selector control that contains Excel's guess regarding the range to be converted — but you can use this control to change the range by selecting cells in the worksheet.

Debugging your code is an important part of working with Excel Macros. Although there are ways to use the debugging features through the Visual Basic Editor menu options, you may find these keyboard shortcuts to be a much more efficient way to debug your code. What to Press What It Does F5 Runs the current procedure or continues after pausing.

Want to navigate your visual basic projects without reaching for the mouse? Try using these keyboard shortcuts to move between projects and modules: What to Press What It Does Up arrow Moves up the project list one item at a time. Down arrow Moves down the project list one item at a time. Home Moves to the first file in the project list.

At some point, you may find yourself working with many macros at one time. It can be tedious trying to navigate between and within macro procedures by clicking around with the mouse. These keyboard shortcuts allow you to quickly jump to a target procedure, navigate modules, and even find the starting point for variables.

As you work with Visual Basic Editor, you may want to navigate the windows using keyboard shortcuts instead reaching for the mouse. These short cuts allow you to navigate the Visual Basic Editor interface. What to Press What It Does Alt+F11 Toggles between the VBE and Excel windows. Shift+F10 Displays the active window's shortcut menu (replicates right-clicking).

A Range object has dozens of properties. You can write VBA programs nonstop for the next 12 months and never use them all. Here, you get a brief overview of some of the most commonly used Excel VBA Range properties. For complete details, consult the Help system in the VBE. (Check out these additional resources for help with Excel VBA.