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.
Click Customize Ribbon and tick the Developer check-box.
Click the Developer tab in the Ribbon menu.
Click Macro Security.
Select the Enable all macros (not recommended; potentially dangerous code can run) radio button and click OK.
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.
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.
Click Record Macro from the Developer tab.
Enter a name, shortcut and description for your macro in the appropriate fields.
- 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.
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.
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.
Click Visual Basic from the Developer tab.
Find the sheet you want to add a VBA script to from the Project - VBAProject pane and double-click the sheet.
Type out or paste the VBA code segment you want to add to your sheet into the Code window.
Click Run followed by Run Sub/UserForm to test your VBA script.
The F5 key also runs your script.
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.