Getting Started with Visual Basics and Excel

Getting Started with Visual Basics and Excel

Visual Basic is a Microsoft language still used in many applications including Excel automation. VB is directly integrated into Microsoft's productivity suite, but it's mainly used in Excel spreadsheets. Excel is often used to gather data, archive large amounts of data, and even scrape information from the web.

First, You Need to Enable Developer Tools

Before you get started, you need to set up your Excel program. Excel includes developer tools, but they aren't readily available after you install the program. You must enable them, which adds them to your Excel ribbon toolbar.

Follow these steps to enable developer tools in Excel.

1. Open Excel and right-click on the main ribbon toolbar. You can right click anywhere on the ribbon as long as it's in an area where there is no button.

2. Click the "Customize the Ribbon" option in the opened context menu. An "Excel Options" window opens. You'll see several tabular options in the left panel.

3. Click the "Customize Ribbon" option in the left panel. Several options are then shown in the right panel.

4. Select "Main tabs" from the dropdown. Notice that all of the ribbons you currently see in Excel are checked. The Developer option is not checked. Check this box and click OK.

Once the Excel Options window closes, you can now see the Developer ribbon in the main Excel toolbar. To get a feel for the tools, click the ribbon to see a list of options.

What Types of Files Support Excel Macros?

Excel has several file formats, but not every one of them supports macros. When you decide to set up your system, you should know which file formats support macros before you pour hours into creating your macros only to find out that they won't run in the file format that you want to use.

.xlsx

The xlsx file format replaces the older xls file format used in Excel versions previous to 2007. Since we're working with ribbons, you should have at least Excel 2007, but you can also use macros with the older xls format.

This file format is used by default. It does not support macros, which provides a layer of security when users create workbooks. We'll get into security in the next sections, but just know that this file format protects from malicious macros, which is why it's set as the default. If you just want to store data and share it across a network or with customers, this is the file format you should use to prevent security issues. Neither xlsx nor xls supports macros.

.xlsm

The xlsm file format is similar to the xlsx format, except it allows for macros to run in your workbooks. This is the main file format used by developers to create workbooks with macros. This is the default file format if you decide to create workbooks. Microsoft saves files in XML format using xlsm, so you have the raw data and programming without the compiled data.

.xlsb

The xlsb file format is the binary form of an xlsm file. Binary files are compiled programs that run much faster than standard XML documents or uncompiled code. Binary files are compiled and cannot be read by humans. These file formats are often used when the programmer doesn't want the user to see or edit the code.

Binary formats are also macro enabled, but they are the least secure for the recipient. Binary files should never run on a computer unless the recipient trusts the sender and the file has been scanned for any malware. These files are also filtered by email systems, because they are used by malware creators.

.xltx

This file format is the common Excel template extension. When you want to create a workbook with standard formats and pre-entered data, you create a template. It's a starting point for your current Excel files. These files don't contain macros, but you can use them to start other macro enabled files.

.xltm

The xltm file format is the alternative to the xltx format with macro support. If you want to create a workbook with a macro already enabled, coded and configured, then you use this file format. Remember that these are template files, so you don't save any changes to these files unless you want to change your macro files starting point.

.xlam

An xlam file is an Excel add-in. These files are used when you want to add some kind of functionality or additional tools to Excel. They have macro support, so you can create add-ins with macros using xlam files.

For the most part, you will work mainly with xlsm files with the occasional xlsb file when you need to compile and distribute your code without allowing others to edit it. We'll show you how to work with add-in files in future chapters.

Configuring Excel to Save to a Macro Enabled Format by Default

As we mentioned, Excel automatically saves to the xlsx file format. This is an XML format that allows other third-party developers to import data and use the Excel workbook with their applications. It's also the default format for security reasons. Since we want to create files that allow macros, it's easier to configure Excel to save to the xlsm file format by default.

Follow these steps to save Excel file formats in the xlsm file format by default.

1. Open Excel and click the Microsoft Office button in the top left corner.

Interested in learning more? Why not take an online Visual Basic for Excel course?

2. Click the Excel Options button at the bottom of the menu. An options window opens with a list of categories in the left panel.

3. Click the "Save" option in the left panel. Several options are shown in the center of the window.

4. Select "Excel Macro-Enabled Workbook xlsm" from the first dropdown menu labeled "Save files in this format."

5. 5, Click "OK" for the settings to take effect.

Once you configure this setting, each time you click the "Save as" option, Excel will prompt you to save the file as an xlsm file instead of xlsx.

Excel Macro Security and Configurations

As we mentioned earlier, macros create a security issue for users. Even some email security applications automatically filter attachments with macros. However, they are useful when used internally within the organization of trusted personnel. You should never run a macro-enabled Excel file from a stranger. Macros can be used to download data and files from the Internet. With malicious macros, they are used to download trojans and other types of malware such as ransomware.

By default, Microsoft runs a scan on macro enabled files. Most antivirus software or corporate anti-malware software blocks files with macros for their security issues. Even with the file format's possible security issues, internal employees use macros to automate procedures and help interact with external data. For this reason, Microsoft added a Trusted Location option for macro files. These trusted locations are used to bypass security scans for files that you know are from trusted senders.

Some users disable antivirus and anti-malware checks to avoid having macro enabled files quarantined by the system. This type of change on security software leaves a large security hole on the system, so it's not a suggested way of working with macro files.

The alternative is to set up Trusted Locations and move macro files to this location. The location can be anywhere on the hard drive.

The following are the steps you take to set up a trusted location on your hard drive.

1. Open Excel and then click the Microsoft Office button.

2. Click Excel Options at the bottom of the opened menu.

3. Click "Trust Center" in the left options menu. Microsoft shows you several privacy statements and options. Click the "Trusted Locations" button to open a new options window.

4. Click "Trusted Locations" in the left panel.

5. Click "Browse" to add a new location.

6. Click "OK" to save your settings.

Microsoft already sets several trusted locations by default. If you feel that these locations are inappropriate, you can also remove them or edit them to a different folder on your hard drive.

Enabling Macros in the Toolbar

By default, Microsoft always displays a warning message to the user when a macro is included in a file. This security warning is to stop macros from automatically running every time a user opens a file. This was an added feature in newer versions of Excel, because older versions allowed viruses and worms to spread using Word or Excel attachments. No security blocked the macro, so it was easy for a malware writer to spread malicious content through email.

When you open a file with a macro, a yellow warning button displays. Click the "Enable Content" button to enable the macro in your file.

When you click the button in the toolbar, it's a one-time setting. Each time you re-open a file, the notification will reappear and ask you to verify that you want to run the macro. This is the preferred way to manage macros, but you might want to disable macro warnings just as you code your applications for Excel. It will save you the tedious requirement of enabling macros as you code your projects.

You can enable all macros and disable notifications in the Trust Center, which is found in the Excel options. Use the following steps to enable macros only if you are developing VB applications and don't need the security.

1. Click the Microsoft Office button in the top left corner.

2. Click "Excel Options" in the opened context menu. This opens a configuration window.

3. Click the "Trust Center" option in the left panel. In this window, click Trust Center Settings.

4. Click "Macro Settings" in the left panel. Several macro settings options show in the center configuration window.

5. Click "Enable all macros ."

6. Click "OK" to save your settings.

Notice that Microsoft does not recommend this setting. When you are finished coding your VB project, you should always change this option to notify you again. Never open Excel files from third-party senders with this option set and while you program your applications.

When you are finished programming your applications, return the setting too "Disable all macros with notification." This will return the notification and the toolbar button when you open a file with a macro.

Once you complete these settings changes, you're ready to get started with your coding. Just remember that you must save your files in a macro enabled file format. We'll use mostly xlsm to illustrate concepts and store macros for future use. You can use any format that is necessary for your project.

Also remember that macros are a security risk for the network and your individual PC. If you decide to code large projects that will take you several days, make sure you avoid opening Excel or Word documents as attachments in email. Macros can spread viruses on the network when other users have infected computers, so even network files should be avoided when you disable the security notice.

With your Excel application configured, you can now get started with your macros.

Using the Macro Recorder

In this section, we will learn how to record macros using the recorder in the Developer tab we enabled in the last lesson. The macro recorder captures specific activities that you can then use later in your programs. It's a convenient way to automate tasks you do in Excel frequently.

Why Use the Macro Recorder?

The macro recorder is a basic way to store automation tasks in Excel. It's distinct from complex macros you can write in Visual Basic. It's a basic tool used to record clicking buttons, entering data, and any other task you do in Excel.

Excel stores each step as a list of commands within your macro-enabled file. After you record the macro, you can use it each time you need to perform those steps. Using macros can save you hours of time for tasks that you do repetitively. Even if the task takes you 5 minutes each run, the macro will perform the task in several seconds. The time saved adds up for people who work in Excel frequently.

Recording a Macro

If you recall, all macro capabilities are stored in the Developer ribbon. After you enable the ribbon, you should see it at the top of your screen with the other ribbons and tabs. If you use an earlier version of Excel, you will see tabs and not ribbons.

In the Developer tab, you see a button named Record Macro. This is the recording area where you can capture each task that you perform. Click the button in the Developer tab to open the main recording dialog box. In the main dialog box, you will see a list of recorded macros that you've already created, if you've created any.

The first step when creating your macro is to give it a name. Macros have specific naming rules. They are a part of a program, and every programming language has its own naming rules and syntax requirements.

For Excel and naming macros, you need to always start the name with a letter. The naming dialog box won't allow you to enter a name if it does not start with a letter. In addition to the first letter being a letter, you also cannot use spaces. This is another standard programming requirement. All programming languages don't all spaces in any variable or name, so this requirement is standard across all development languages. When we move on to Visual Basic programming, we will cover variables and naming them using proper syntax.

The way you name your macro matters, although this is a styling requirement and not a Visual Basic syntax requirement. You always want to give your macros a meaningful name. The name should be something you and your users will recognize, so they know immediately what will happen when they use the macro button. Malicious macros are often given vague names or ones that don't make any sense to the user. You don't want your users deleting a macro because they think it's unnecessary.

The name should also indicate what the macro does. Long recorded macros can do several things to a spreadsheet, so you don't want your users accidentally using the wrong one. It can take too much effort to reverse what was done from a macro, so always be clear with the macro name.

The next option is to set a shortcut key. You're required to set a name for your macro, but setting a shortcut key is completely optional. You can assign any shortcut key you want, but this key shouldn't be a commonly used key. A shortcut key is used for quicker access and makes it more convenient to the user. If you use a commonly used key, you make it inconvenient for the user.

Most macro developers use the function keys to assign a shortcut. You wouldn't use a letter or number key, or every time the user enters data into the spreadsheet, there is a chance the macro will accidentally run. The function keys are the ones at the top of your keyboard and start with the letter F. Every keyboard has function keys from F1 to F12.

One issue to note is function keys are laptops. Some laptop or even netbook keyboards don't have easy to use function keys. If you have users that use laptops, just remember that the function keys might not be convenient. For a majority of users, though, the function keys are the standard way to run macros.

The Excel application also has shortcut keys assigned to it. For instance, if you press Ctrl+C, the application copies a cell's data onto the Windows clipboard. If you use this key combination for your own macro, it overrides the default shortcut. This can also be inconvenient to the user especially when they use common shortcuts such as the copy shortcut. In other words, you always want to create a shortcut key or key combination that doesn't get in the way of the user's normal behavior. Just remember that the shortcut must be convenient but without overriding natural shortcuts.

The next option is where you want to save your macro. The place you save your macro will depend on how you can use it and where you can use it. You have three main options.

The following three options are what you can choose from.

This Workbook
New Workbook
Personal Macro Workbook
When you choose "This Workbook," you save the macro to the current workbook you have opened in Excel. A workbook is the main object in Excel. It's often used in exchange of the term "Excel file." The workbook is a collection of worksheets. The worksheets are the sheets that contain your data. If you view the bottom of the Excel file window, you see Sheet1, Sheet2, and any other sheets created. You always have at least Sheet1 in a workbook when you create a new file. You can add and delete sheets as necessary.

This distinction is important when you save the macro. When you save the macro to the current workbook, you can only use it in the current workbook and no other future files that you create. This option is beneficial when you're creating a quick macro that you just need for the current workbook, and it won't be relevant to future files.

The "New Workbook" lets you create a new macro that can be used in future workbooks that you create while you have the current workbook open. This option is beneficial when you decide to create several workbooks in a sitting.

The final option is to save a separate file or a "Personal Macro Workbook." This option is beneficial when you want to create a macro that you can then use in other workbooks without the need to recreate the recording.

The final text box in the macro recording window is the Description text box. This should be used to explain exactly what your macro does when the user runs it. It should explain any specific entries the user must have in the workbook or if the user should expect a certain output when the macro is finished. Be descriptive but simple enough for the user to understand what happens when the macro runs on the workbook. Any major changes should be explained, so there are no surprises for the user.

After you are finished setting up the details for your macro, click "OK." Note that when you click the OK button, the macro recorder starts to record your actions. Any click of a button, menu choice, data entry, and any other changes you make to the file will be recorded during the session. You should also notice that there is a "Stop" button present while you record your actions. This tells the macro to stop recording.

If you're unhappy with the recording or you make a mistake while you perform your actions, you can re-record your macro and start over. Any of these actions will be overwritten when you re-record your macro, so make sure you have all your steps in order before you overwrite the current macro.

After you record the macro, you can run it from the Developer ribbon. Most developers run the macro a few times to ensure that there are no bugs or mistakes in the recording.

You can record as many macros as you need to create an automated workbook in Excel. Remember that macros are only beneficial if you have a task that you frequently need to do with every workbook. They aren't very useful if you do different tasks with each workbook. In this case, it takes more time to create macros for each workbook than it does to complete the data entry and programming you need to finish the workbook.

When you have repetitive tasks, macros can make workbook creation much more convenient. They can save you hours of time when you have large data files that require the same task over and over. Recorded macros are very convenient when you need to pass a file to another user and can't explain how to perform certain tasks. They also save you time by performing a task in a few seconds when it will take you several minutes. Use the macro recording process when you want to automate certain procedures without creating a complex Visual Studio code.