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

• devtut - Getting started with excel-vba

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

2. 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\

3. 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)

• devtut - Getting started with excel-vba
• Last Used Row or Column in a Worksheet
• Creating a drop-down menu in the Active Worksheet with a Combo Box
• Early Binding vs Late Binding
• SQL in Excel VBA - Best Practices
• Excel-VBA Optimization
• Debugging and Troubleshooting
• VBA Best Practices
• Excel VBA Tips and Tricks
• Common Mistakes

References 2 (devtut - VBA)

• Scripting.Dictionary object
• CreateObject vs. GetObject
• Non-Latin Characters
• VBA Run-Time Errors
• Error Handling

References 3

• ScottSchaen - How To Get Started
• AllenMattson/VBA
• thesmallman - EXCEL VBA SCRIPTING DICTIONARY

References 4

• Microsoft vba
    1. VBA - Language reference for VBA
      1.1. vba - Language reference for VBA - Visual Basic conceptual topics
              * DECLARING VARIABLES
    2. Excel VBA reference