The Developer tab is disabled by default. This is a hint that you should at least think about what you are getting into before enabling it. If you don’t see it, customize the ribbon, Alt-F,T,C, and set the checkbox next to Developer on the right. This tab has a Code section with VBA tools as well as Controls to add buttons and other interactive tools to your project.
To Code or Not To Code?
VBA is a powerful tool and a must if you are serious about being an Excel developer, but it is not for the novice. It is another language. Like any language, you can easily pick up casual phrases, but sustaining a meaningful conversation requires significant investment. Avoiding offense and articulating nuance is a full-immersion commitment. Using it in public without full command risks your reputation.
When your workbook includes VBA code you cross the border from “.xlsx” to “.xlsm” file type. This alerts anyone who opens it, unless they’ve disabled macro security, to beware and be sure that they trust the source. This is like going through customs, warning of an inherent risk in distributing code.
Opening a macro-enabled file from an untrusted source can have serious consequences. Hackers can write malicious code that runs automatically. Digital certificates provide assurance on public files, and allow you to trust other files from the same publisher. Go to the macro security page, Alt-L,A,S. I would not recommend making any changes in the Trust Center, but it is a good first step to learn how security works.
With colleagues and acquaintances who share macro-enabled files with you, you know and trust the author, but you might not know their level of programming skill. Initially this may leave you feeling uncomfortably dependent. This feeling will pass, but don’t forget how others might perceive your files. VBA is very opaque to non-developers.
The most obvious and strongest lesson on macros is justification. When you have a compelling need, use it with care. The more significant the user value, the more successful your macros will be. But many macros provide only marginal value over standard Excel and can be seen as frivolous. To avoid this, consider VBA as part of a mature and elegant design.
I don’t mean to discourage you but to help your reputation. Don’t let your method dominate your message. VBA will not make your tool cool. Elegance makes it cool. Applied appropriately, VBA can make it elegant. First, compress your work to its essence. Don’t automate work you can eliminate or simplify.
Exploit all native Excel tools before creating your own. For example, let’s say you have groups on a sheet and you want to toggle the outline button display. You search and find the DisplayOutline property of the window, and write a clever macro. Then someone tells you, “oh, I just use Ctrl+8”. Hmm, yeah, it’s cooler to use your first language well before showing off your second. Some macro buttons do nothing but select another sheet. If VBA is justified for other reasons, small tasks can be included, but are usually not justifiable by themselves.
Ok, but what if the user doesn’t know there’s a shortcut, and I want to make the function accessible to them? Accessibility is a valid reason to code, but think it through. To make my macro accessible I’ll need to put a button somewhere, or assign a hotkey and inform the user. Instead of a button, I can put a “Help” cell that looks like a button, which when selected displays a Data Validation message to inform them of a shortcut tip. It is barely any more effort for them, less for me, avoids code and helps the user become more fluent.
That said, macro scope need not be complex to be worthy. Think about your project scope, dumbed-down. Not to look down on your users, but don’t expect them to either know how or care enough to perform tasks that don’t add value for them. Most macros simply protect the user from the need to learn and execute actions the long way.
For example, if you have defined data scenarios, it adds value for a user to be able to see and compare them. What doesn’t add value is the process to go find Scenario Manager and select a different one. Your audience might not be familiar with scenarios, or have the time to make the connection between scenario description and data. Instead, a macro can cycle scenarios with a spinner button, and can display a scenario number and description on the sheet. This saves user effort and missed opportunity.
More complex cases like data manipulation call for a more involved strategy. Useful information comes from well-analyzed and well-presented data, which Excel itself does so nicely, but getting the raw data in the first place is often the challenge and justification for macros. It is also where an inexperienced coder can do damage. If the data are not already Excel-friendly, or are stored in multiple tables, retrieval is a daunting task of formatting and joining tables and defining which fields and records from each table to output.
In the corporate environment, it is important to work with the IT department to develop solutions that are secure, scalable and supportable. The strategy is to minimize the distance and delay from data source to destination, but data integrity is the implicit governor. Macros can serve to prototype or mock up the business requirements for more permanent solutions, but because they are so open they usually cannot be supported.
For a small business, you might be the IT department, and Excel your only solution. In that case it’s up to you to make sustainable design a deliberate strategy.
Let discretion guide you to code or not to code. Do it for the sake of the user, not your resume. What will they take away from the experience you give them? You might elevate their skill a little, but the primary objective is to shortcut the result. When they reach that without even realizing how they got there, you have closed the sale.
VBA Tips
VBA training is available in many forms. Microsoft has an excellent introductory page, look for “getting started with VBA in office”, which includes a section on when and why to use VBA. There are many great tutorials and examples on the web. Below are a few tips from my experience, with more elaboration in the projects.
Basics
Start with navigation, learn your way around. Alt-L,R to record a macro; Alt+F8 to run one; Alt+F11 to open VBA. In VBA, look through the menus, try them out, review each screen section. Open the Object Browser, F2, and spend time browsing it.
To simplify, Objects are nouns, Methods are verbs, Properties are adjectives. Like some human languages, order is not like English, and some verbs are implied. To “select B2” is Range(“B2”).Select; to “make it red” is Selection.Interior.Color = 255; the verb is implied by the equals sign. Once the essential syntax is learned, work on building your vocabulary with Object Browser. Use the help and study Concepts and Object model in the Excel VBA reference: https://docs.microsoft.com/en-us/office/vba/api/overview/excel.
Recording vs. Typing
Object Browser and the Excel VBA reference library are essential references, but cannot teach you how to speak the language. To begin learning on your own, recording and reading macros is like listening to a language tape, and repeating the words. Once you begin to understand VBA syntax, start typing, adding your own variations – “I like to eat chicken... I like to eat rice...”.
Not all Excel actions are recorded, and often, many more things are recorded than you actually do. For example, if you record while setting fill color, in addition to the .Color property, VBA will list several other format properties that you didn’t set, like .Pattern, .TintAndShade, etc. In this way it packages related properties to recreate the exact effect. If you run the macro on a cell that has a pattern fill, it will overwrite the pattern in addition to setting the fill color you recorded. You can remove that effect by removing or commenting out the respective lines from the macro.
You can record with Use Relative References in order to apply the action anywhere, but you’ll also need to learn range referencing within VBA. The Excel VBA reference has a section in Concepts all about cells and ranges.
Before reapplying a macro on other sheets, think what can happen. Is there a chance your macro will overwrite data, or mess up the formatting? You can edit the macro to check the affected range before taking action, but you can’t record that kind of governance.
There are many VBA techniques that are not based on Excel actions, such as branching, looping, input box and message box. To truly leverage its power and avoid its dangers, you’ll need to learn concepts and methods of programming. You will get a lot more from such training once you have mastered the basics.
Even when you learn to type complete macros, recording is useful to capture the relevant object and properties that you want to control, and the enumeration of property values. For example, to set fill color you need to know the color number or RGB values, or you can record while setting the color. In this sense recorder is like a translator app that saves time from reading the dictionary.
Error Handling
Early success can make you cocky until you encounter an embarrassing run-time error. The lesson is to be humble, expect errors, and learn how to find and handle them. Test repeatedly with various inputs and conditions, and build in error handling. First you need to learn the different kind of errors.
Syntax errors appear as you type, within the offending line. This is like grammar check at the sentence level, for example, telling you that you have not provided required arguments for a function, or you’ve written an If statement without Then, etc.
Compile errors are raised at runtime. These are like grammar checks at the paragraph level, such as “Block If without End If” or “Expected End With”. Sometimes these are a little inaccurate, for example I might get “End With without With” when in fact the error is “For without Next”.
Runtime errors are like fact-check flags. If you call an object which does not exist, for example, the code cannot run even if it is grammatically correct.
Finally, you can trap and resolve all the above errors, and still have incorrect results. A key task of the programmer is to validate and document the logic of the program.
Don’t use “On Error Resume Next” too easily, until you understand potential errors. It does bypass nuisance errors, but also hides serious errors that you need to manage. Set traps at key points in the code to see what’s happening.
Of course, if we knew what errors would occur, we would prevent them, right? Limiting values, using If...Then statements, and other techniques reduce risk, and rigorous testing exposes it. Learn to find subtle errors by getting familiar with the Debug menu. Cause intentional errors to practice.
The bottom line is that the user should never be dragged into VBA debug mode because of a runtime error. If there’s any chance of one, they should rather be given a helpful message within Excel. This is like the Data Validation of VBA, where it’s important to customize the default message.
Modular Code
VBA chapters are called modules, but modular construction refers to the “Lego” concept of packaging reusable code segments for efficient assembly. As a principle, code should be as compact as possible, and never repeat itself. That may not be entirely possible, but it’s a guideline. For example, if you have several buttons or target cells that will perform similar actions, put all the common code into a subroutine that each action calls by name.
In VBA, a workbook is a VBAProject. In the Project Explorer, Ctrl+R, the project has a folder called ThisWorkbook in which you can select and code all the workbook-level events. To see what events are, double-click ThisWorkbook, and on the right you’ll see two drop-downs, with (General) and (Declarations). In the first, select Workbook. This creates Private Sub Workbook_Open(), which is the default event. To see other events, open the second drop-down list.
Project Explorer also has a folder for each sheet, which has its given number and the name on the tab. Within each sheet’s folder you can select and code the worksheet-level events, as well as any controls on the sheet. Repeat the steps to select the Worksheet and view the events, noting how they differ from Workbook events.
The folder icon in the Project Explorer toolbar toggles a folder level that separates Microsoft Excel Objects from Modules. Modules are still part of the file’s VBAProject, but apart from the workbook structure. If you record a macro, it creates a new Module, or you can manually insert one.
Your file can thus have many places to store code, and you need a strategy.
A Worksheet must contain the code linked to its own events and objects, which are private in scope, meaning they can only be run from that sheet. But, they can call other, manually created routines in the same sheet, or public routines in a module. When routines get complex, look for ways to split out segments as separate subs and functions, and plan organization.
Consider a workbook in which configuration data are stored on a special sheet, in cells named with workbook scope so they can be referred to from any sheet. This is analogous to a module of public routines that can be called from anywhere in the file. In VBA you’re not hiding anything, it’s about organization and accessibility.
A public module is a good idea for routines that are reusable, for example, it does some general function that can be applied to any sheet. A routine can be more broadly usable by feeding it arguments instead of hard-coding with fixed inputs.
Portability
If the code is specific to the sheet and could never be used elsewhere, keep it within the sheet. A benefit of this approach is portability. If you ever want to copy the sheet to a stand-alone workbook, the code that resides on the sheet is copied with it. Code called from the sheet in any other module would need to be manually copied, or you will have runtime errors.
Copying a sheet to a new file doesn’t always work perfectly. One unavoidable issue to deal with is shape controls. If you have macros on the sheet, chances are you have some shape objects to run them. When the sheet is copied, the macros assigned to the shapes still point to the origin file. The best way to manage this is by a special macro to update the .OnAction property of all the shapes on the sheet.
VBA Options
Editor options (Tools → Options...) include various features such as syntax check and quick info that are normally all on. I have turned off Auto Syntax Check. If a make a mistake it still shows in red font, but it no longer pops up and tells me. A popup is an annoyance if you occasionally leave commands to be completed later. Some people turn off Require Variable Declaration, which allows variables to be self-defined on the fly. I prefer to explicitly define variables by Dim statement at the top of the procedure or even the module, so I keep this on to enforce it.
The editor has limited text formatting features. There is no way to format selected text, but you can set the format for different text types such as normal text, keywords and comments, in Editor Format options. The default differentiates these three by font color only, which may be hard to see for some people or on some screens. You can set font, size, foreground and background color by text type. I like to give comments a background color to make them stand out. Be sure that you are not duplicating any other text type format so that you can always distinguish the types from each other.
コメント