How To Write Visual Basic Code In Excel

To write Visual Basic code in Excel, open the desktop application, enable the Developer tab, and choose Visual Basic. Insert a standard module, write a VBA procedure, then run it and check the result. Save the workbook as a macro-enabled file so your code is.

Prepare Excel for VBA

Excel uses Visual Basic for Applications (VBA) to automate workbook tasks. A macro can format cells, calculate totals or process a worksheet. You write the instructions in the Visual Basic Editor, which is included in desktop Excel.

The steps below use Excel for Windows. Excel for the web cannot create, edit or run VBA macros; open the workbook in the desktop application to work with them, as explained in Microsoft’s guidance on VBA in the browser.

1. Open a blank workbook or a copy of a workbook you can practise on.
2. Choose File, then Options, then Customize Ribbon.
3. Under Main Tabs, select Developer and click OK.
4. Open Developer and select Visual Basic. You can also press Alt + F11.
5. In Project Explorer, select the project for your practice workbook. Choose Insert, then Module.

A standard module is a suitable place for a general-purpose macro. Worksheet and workbook modules are also available, but they are commonly used for event procedures, such as code that responds to a worksheet change.

Write and run your first macro

Type the following three lines in the new module, putting each statement on its own line:

Sub HelloWorld()

MsgBox "Hello, World!"

End Sub

Sub begins a procedure named HelloWorld. The parentheses are empty because this macro takes no arguments. MsgBox displays the greeting, and End Sub marks the end of the procedure.

1. Click anywhere inside the HelloWorld procedure.
2. Press F5 or choose Run, then Run Sub/UserForm.
3. Check that the greeting appears, then click OK to dismiss it.
4. Return to Excel. Choose Developer, then Macros, select HelloWorld and click Run to launch it from the workbook.

Opening or saving a workbook does not automatically run this macro. You must start it using one of these methods or assign it to a control.

Save the code with the workbook

Choose File, then Save As, and select Excel Macro-Enabled Workbook (*.xlsm). The ordinary .xlsx format does not retain VBA code. Microsoft’s instructions for saving a macro explain the format choice.

If Excel displays a security prompt, enable macros only for a workbook whose code you trust. On a managed computer, your organisation’s policy may restrict macro execution.

Refer to the correct workbook, worksheet and cells

Excel’s object model gives your code access to workbooks, worksheets and ranges. An object represents something you can work with; its properties describe values or settings, while its methods perform actions.

For example, ThisWorkbook.Worksheets("Sheet1").Range("B1").Value refers to the value in cell B1 on Sheet1 of the workbook containing the code. ThisWorkbook is useful when several workbooks are open because it identifies where the code is stored.

A bare reference such as Range("B1") in a standard module normally uses the active sheet. Qualifying the workbook and worksheet makes the target explicit and avoids relying on whichever sheet happens to be selected.

Calculate a total and write it to a cell

In your practice workbook, name a worksheet Sheet1 and enter some numbers in A1:A10. Leave B1 available for the result. Add this procedure below HelloWorld, again placing each statement on its own line:

Sub CalculateTotal()

Dim ws As Worksheet

Dim total As Double

Set ws = ThisWorkbook.Worksheets("Sheet1")

total = Application.WorksheetFunction.Sum(ws.Range("A1:A10"))

ws.Range("B1").Value = total

End Sub

Run CalculateTotal and compare B1 with the sum of your input numbers. The worksheet variable ws holds a reference to Sheet1. The total variable stores the result returned by Excel’s SUM function, which the final assignment writes into B1.

This macro overwrites B1 each time it runs. It writes a calculated value rather than a worksheet formula, so changing A1:A10 afterwards will not update B1 until you run the macro again. For a total that should recalculate automatically, a normal SUM formula is usually more suitable.

Use variables and control the flow of code

Variables give names to values your procedure needs to remember. Declare them with Dim and choose a type that suits the information:

Place Option Explicit at the very top of the module, before any Sub or Function procedures. It requires variables to be declared and helps catch misspelt variable names when the project is compiled.

Use If...Then...Else when the next action depends on a condition, such as whether a required cell is empty. Use For...Next to repeat instructions with a counter, or For Each...Next to work through cells in a range. Keep the range small while learning so you can inspect every change.

A Sub performs actions, such as writing the total into B1. A Function returns a value and can be useful for a reusable calculation. Start with a Sub when your task changes the workbook.

Find errors before using real data

1. In the editor, choose Debug, then Compile VBAProject to check for compilation errors.
2. Click inside your procedure and press F8 to step through executable statements individually.
3. Inspect variable values using the Locals window, available from the View menu.
4. To pause at a particular executable line, select it and press F9 to set a breakpoint. Run the macro, then continue stepping when it pauses.
5. Check both the intended result and any cells the macro should leave untouched.

If CalculateTotal reports that a subscript is out of range, check that Sheet1 exists in the workbook containing the code. If the calculation fails, inspect the input range for worksheet errors. Fix the cause before adding error handling; suppressing errors can hide an incorrect result.

As your code grows, refer directly to objects instead of repeatedly using Select or Activate. Keep procedures focused on a clear task, and test changes on a copy of the data.

Run a tested macro from a worksheet button

1. Return to the worksheet and choose Developer, then Insert.
2. Under Form Controls, select Button and draw it on the sheet.
3. In Assign Macro, select CalculateTotal and click OK.
4. Change the button text to Calculate total, then click it to check the result.

For another small interface task, explore coding a clear button in Visual Basic. If your workflow needs a yes-or-no choice, the guide to coding a checkbox in Visual Basic introduces related control logic. When adapting examples, check that their controls and syntax apply to Excel VBA.

You can find further tutorials in the Visual Basic blog. Next, change the input numbers in your practice workbook, run CalculateTotal from its button, and verify B1 before adapting the macro to your own worksheet.