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

Click Options.

Select Options.

Select Customize Ribbon and tick the Developer check box.

Click Customize Ribbon and tick the Developer check-box.

Click OK.

Click OK.

Click Developer.

Click the Developer tab in the Ribbon menu.

Click Macro Security.

Click Macro Security.

Select the option to enable all macros and click OK.

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

Tip

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

Click Record Macro.

Click Record Macro from the Developer tab.

Enter the name, keyboard shortcut and description for your macro.

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

Tip

    • 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.
Select where you want the macro to be stored.

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

Click OK.

Perform the actions you're saving to the macro and click Stop Recording.

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.

Click Visual Basic from the Developer tab.

Double-click the sheet in which you want to add scripts.

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

Type or paste your VBA script coding.

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

Select Run Sub/UserForm from the Run menu.

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

Tip

  • The F5 key also runs your script.

Adjust your script if it doesn't work properly.

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.

Items you will need