Skip to content
Tutorials Center
My learning

Topics

Lesson 3 of 37 ยท Getting Started

Your First Macro: Sub, MsgBox and Running Code

Write and run your first VBA macro: create a module, declare a Sub, use MsgBox and Debug.Print, run with F5, attach a button and fix simple errors.

Time to write real code. In this lesson you create a module, write a Sub, run it three different ways and learn the two ways VBA prints results: MsgBox and Debug.Print.

Step by step

  1. Open Excel, press Alt+F11.
  2. Right-click your project in the Project Explorer and choose Insert > Module.
  3. Type this in the code window:
VB
Sub HelloWorld()
    MsgBox "Hello, world!"
End Sub
  1. Click inside the procedure and press F5. A dialog appears.

That is a macro. Let us read it.

  • Sub starts a procedure and End Sub ends it. The name (HelloWorld) must start with a letter, contain no spaces and be at most 255 characters.
  • The parentheses hold parameters. A macro you can run from the Macros list has an empty pair.
  • Indentation (four spaces or Tab) is not required, but every programmer does it. The editor will not do it for you.
  • VBA is not case sensitive, but the editor fixes capitalisation of keywords for you. msgbox becomes MsgBox.

Statements and lines

Each line is normally one statement. To put two on a line, separate them with a colon (x = 1: y = 2), though that hurts readability. To continue a long statement on the next line, end the line with a space and an underscore:

VB
MsgBox "This is a long message " & _
       "split across two lines."

Comments start with an apostrophe. Everything after it is ignored:

VB
' This whole line is a comment
MsgBox "Hi"   ' and so is this ending

Printing to the Immediate window

MsgBox stops the program until you click OK, which is annoying for testing. Debug.Print writes to the Immediate window (Ctrl+G) silently:

VB
Sub CountToThree()
    Debug.Print "One"
    Debug.Print "Two"
    Debug.Print "Three"
End Sub

Running it fills the Immediate window with:

Text
One
Two
Three

Use Debug.Print while developing and MsgBox when the user needs to read something.

Stand-in: Sub and Debug.Print in JavaScript

This JavaScript stand-in (not VBA) shows the same shape: a named procedure that you call, and printing that does not block.

JS Stand-in: a procedure that prints

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Running a macro: four ways

  1. F5 inside the procedure in the VBE.
  2. Alt+F8, pick the macro and click Run.
  3. A button on the sheet: Developer > Insert > Button (Form Controls), draw it, then choose the macro in the dialog that appears.
  4. A shortcut key: in Alt+F8 choose Options and assign Ctrl+letter. Pick Ctrl+Shift+letter to avoid overriding Ctrl+C and friends.

You can also add a macro to the Quick Access Toolbar from File > Options.

Working with the sheet

Even the first macro can touch the workbook:

VB
Sub WriteGreeting()
    Range("A1").Value = "Hello from VBA"
    Range("A1").Font.Bold = True
    Range("A1").Interior.Color = RGB(255, 240, 150)
End Sub

Range("A1") is an object; .Value, .Font.Bold and .Interior.Color are properties you set with =. After running, cell A1 holds bold text on a yellow background. You will study objects properly in Objects and the Object Model.

Calling one macro from another

VB
Sub Main()
    WriteGreeting
    MsgBox "Done"
End Sub

Without arguments you just write the name. You can add the keyword Call (Call WriteGreeting) but nobody needs it.

Fixing the first errors

Message Likely cause
Compile error: Sub or Function not defined A misspelled procedure name
Compile error: Expected: end of statement Missing operator or comma
Run-time error 1004 Excel could not do what you asked, e.g. wrong sheet name
Macros disabled Security bar not enabled, see security

When an error stops code, the editor highlights the line in yellow. Click Reset (the square button) before running again. Press F8 to step one line at a time and watch what happens.

Always save as .xlsm: if you choose .xlsx Excel warns that the code will be lost.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which keyword pair wraps a macro?

    A macro is a Sub procedure without parameters.
  2. 2. What starts a comment in VBA?

    VBA comments begin with an apostrophe (or the old Rem keyword).
  3. 3. Where does Debug.Print write?

    Debug.Print writes to the Immediate window and does not stop the program.
  4. 4. How do you continue a statement on the next line?

    The line continuation character is a space followed by an underscore at the end of the line.

Summary

  • A macro is a Sub in a module; run it with F5, Alt+F8, a button or a shortcut key.
  • MsgBox shows a dialog; Debug.Print writes to the Immediate window.
  • Comments start with an apostrophe; use _ to continue long lines.
  • Save macro workbooks as .xlsm.

Next: Recording Macros