How Do I Create a Microsoft Excel Script?

By Shea Laverty

Microsoft Excel supports automating Excel commands and features using macros and Visual Basic for Applications scripting. Macros are a pre-recorded series of commands, set to run automatically when a specific command is given. VBA is a dialect of the Visual Basic programming language and can be used to create macros, as well as to execute automated commands based on certain conditions.

Enabling Macros and Scripts

Before you can get started creating macros and VBA scripts, enable the Developer tab on the Ribbon menu. The Developer tab is where the option to create macros and scripts is kept, as well as including other tools designed for advanced Excel users and developers. Once the option is enabled, adjust Excel's macro security setting so you can test and run your macros.

i Image courtesy of Microsoft.

Click File.

i Image courtesy of Microsoft.

Select Options.

i Image courtesy of Microsoft.

Click Customize Ribbon and tick the Developer check-box.

i Image courtesy of Microsoft.

Click OK.

i Image courtesy of Microsoft.

Click the Developer tab in the Ribbon menu.

i Image courtesy of Microsoft.

Click Macro Security.

i Image courtesy of Microsoft.

Select the Enable all macros (not recommended; potentially dangerous code can run) radio button and click OK.

Tips

When you're finished using your macros and scripts, return to this menu and set the option to either Disable all macros with notification or Disable all macros except digitally signed macros to prevent potentially malicious code from running in other Excel documents.

Creating Macros

Macros automatically run through their recorded commands and actions when a specific command is issued. Every action you take in Excel during recording is added to the macro, so do a couple of practice runs to smooth out your process before you begin recording.

i Image courtesy of Microsoft.

Click Record Macro from the Developer tab.

i Image courtesy of Microsoft.

Enter a name, shortcut and description for your macro in the appropriate fields.

Tips

Since your macro triggers using the Ctrl key and the key you specify, choose a key combination that you don't normally use or that isn't associated with a function built into Excel.
Macro names must start with a letter, cannot contain spaces, and the shortcut key must be a letter.

i Image courtesy of Microsoft.

Click the Store macro in drop-down and select where you want to keep the macro.

This Workbook limits the macro to the Excel document you're currently editing.
New Workbook creates a new Excel document specifically for storing this macro.
Personal Macro Workbook creates a hidden Excel document that stores all your macros for use with any Excel document.

i Image courtesy of Microsoft.

Click OK.

i Image courtesy of Microsoft.

Perform the actions you want to automate, and then click Stop Recording. Use the keyboard shortcut specified to use your new macro.

Using VBA Scripting

VBA takes some practice to understand and apply properly, so if you're new to the language, check out guides like Excel Easy and Home & Learn's Excel VBA courses and tutorials. Microsoft's Hey, Scripting Guy! blog series also contains some useful examples of scripts for Excel.

i Image courtesy of Microsoft.

Click Visual Basic from the Developer tab.

i Image courtesy of Microsoft.

Find the sheet you want to add a VBA script to from the Project - VBAProject pane and double-click the sheet.

i Image courtesy of Microsoft.

Type out or paste the VBA code segment you want to add to your sheet into the Code window.

i Image courtesy of Microsoft.

Click Run followed by Run Sub/UserForm to test your VBA script.

Tips

The F5 key also runs your script.

i Image courtesy of Microsoft.

If the script works as intended, continue with any further scripting or work with Excel. If not, adjust the script in the Code window until it behaves as intended.

References