/vba

Visual Basic for Applications (VBA macros) - my notes

Primary LanguageVBAMIT LicenseMIT

Visual Basic for Applications (VBA)

Office Visual Basic for Applications (VBA) is an event-driven programming language that enables you to extend Microsoft Office applications.

Open Visual Basic Editor

Step 1: Open a Workbook

Step 2 Option A: Press Alt + F11
This is the standard shortcut to open the VBE.

Step 2 Option B: Developer Tab --> View Code
First, the Developer Tab must be added to the ribbon.
Go to File -> Options -> Customize Ribbon, then check the box for developer.
Then, go to the developer tab and click "View Code" or "Visual Basic"

Step 2 Option C: View tab > Macros > Click Edit button to open an Existing Macro

How to get help

'Require Variable Declaration
Option Explicit

Sub temp1()

' 1) Write:
inp
  ' press Ctrl+Space together for autocompletion
  ' ==>
  ' InputBox

' 2)
  InputBox(

' 3) Get HELP:
  InputBox()
  ' move the cursor to the InputBox (select that keyword in the code)
  ' hit F1

End Sub

In Visual Basic Editor, select Tools, Options
choose: Require Variable Declaration
==> Option Explicit

Debug.Print ( Immediate Window )

  • In Excel, Open the Visual Basic Editor (VBE)
  • Click View ==> Immediate Window to open the Immediate Window (or ctrl + G).

You should see the Immediate Window at the bottom on VBE.
This window allow you to directly test some VBA code.

So let's start, type in this console :
?Worksheets.
==>
?Worksheets.Count

If you have "Debug.Print" in your code, then "Immediate Window" should be enabled, and then the macro should be run from VBE.

How to run Excel macro?
Click on the green "play" arrow (or press F5) in the VBE toolbar to run the program,
while the cursor is inside the Sub procedure.
( Or Click Run ==> "Run Sub/UserForm F5". )

How to export source code

In Visual Basic Editor ...

  1. Open Module1
    (Choose Project Explorer: Select View, Project Explorer)
    In Project Explorer, double click on Module1 to open it.

  2. Export source code
    Select File, Export File
    ==> Module1.bas

HOW TO COPY SOME BAS FILE TO THE NEW EXCEL FILE

  1. Open Excel and save new Excel xlsm file

Win-s (search), excel, Enter
Choose "Blank Workbook"

Excel, File, Save As
to Documents

Enter File Name: test
Excel Workbook xlsm (m- with macros)
Save

  1. Download some bas file:

On the github portal, open: create_table_of_contents_of_worksheets.bas
Click on the "Download raw file". ==> C:\Users\username\Downloads\

  1. Copy the contents of bas file to Excel

Open "Visual Basic Editor": Alt+F11
Click on the Insert, Module ==> Module1
Paste the contents of bas file to Module1, and Save.
Click on the Debug, Compile VBAproject.


References 1 (devtut - Excel VBA)

References 2 (devtut - VBA)

References 3

References 4