Ryan's Manuals

Languages · Tools · Courses · Life · About

Excel & VBA

~7m skim, 1,372 words, updated Feb 7, 2025

Enterprise grade spreadsheeting.

Contents

Introduction¶

Man has always had the spreadsheet for working purposes. If you peer backwards into history, you will find cruneiform tablets with spreadsheets on them. Tables are an endlessly useful and logical way to organize the material world. In the new industrial era, Microsoft Excel has become the humble program to take this crown, being the preferred format for companies of all sizes to practically and effectively complete their work.

Martin Shkreli has a Finance Lessons playlist¹ with a focus on Excel.

Like all Microsoft Office products, Excel can be automated with VBA, Visual Basic for Applications, which is the primary topic of this manual. Visual Basic was first released in 1991 as an easy way (akin to BASIC) to work with Microsoft products and develop Windows applications.²

Honestly, I never expected to dig into this stuff. I assumed my knowledge of VB/VBA would never extend past this meme, but here we are -

“I’ll create a GUI interface in Visual Basic, see if I can track an IP address.” – CSI Scene/Meme

Learning Resources¶

Documentation

1. Microsoft: VBA Language Refrence
2. Microsoft: Excel VBA Reference
3. Microsoft: Develop solutions and customize Excel

Cheat Sheets

• zerotomastery.io VBA Cheat Sheet (simple)
• Analyst Cave VBA Cheat Sheet (comprehensive)

What are Macros?¶

Generally - across programming - macros (from the Ancient Greek “makros” meaning long) are meant to simplify a longer activity. In Excel, they are more akin to the concept of automation scripting - the UI is puppeteered with functions and code to complete a repetitive or difficult task.

Examples:

• Automatically formatting a report every time new data is added.
• Copying and pasting data across sheets without manual effort.
• Generating and sending automated emails based on Excel data.
• Importing and cleaning up data from external sources.

Writing Simple Macros with VBA¶

Before starting, let’s set up Excel for some hacking.

1. Enable the developer menu at File, Options, Customize Ribbon, and check the Developer option to include it in the ribbon.
2. Hit Alt-F11. This will open the VBA editor.
3. Ensure your file is saved as xlsm. If you do not save as xlsm all of your macros will be lost if you close the file.

Generally in this section we will cover the following:

• How to record a macro
• Editing recorded macros in VBA
• Assigning macros to buttons and shortcuts
• The Visual Basic for Applications (VBA) editor
• VBA syntax and structure
• Variables, data types, and scope
• Conditional statements (If/Else, Select Case)
• Loops (For, While, Do Until)

Recording a Simple Macro¶

In your sample document (whatever you want) click Developer > Record Macro, don’t bother setting a shortcut, and perform a few actions. Then hit Developer > Stop Recording. You can view the code for this macro by (assuming this is a fresh project) hitting Alt-F11 and looking in Module1 for the generated code.

Sub Macro1()
   '
   ' Macro1 Macro
   ' Set Column Types
   '

   ' Set the column to date format
   Columns("A:A").Select
   Selection.NumberFormat = "m/d/yyyy"
   Columns("A:A").EntireColumn.AutoFit
   ' Set the top item in the column
   Range("A1").Select
   Selection.NumberFormat = "General"
   Selection.Font.Bold = True
End Sub

Aside: Save your macros in your Emacs configs with M-x insert-kbd-macro

Assigning Macros to Buttons¶

To reassign the macro to another keybinding in the future, you can hit Developer > Macros and in the Options for your macro edit the shortcut and description.

Form buttons can be added to sheets with Developer > Insert > Button (Form Control) - clicking the button will then run the macro.

The Visual Basic for Applications (VBA) editor¶

The entire Excel/VBA interop system can be accessed and manipulated from the VBA editor, which can be opened with Alt + F11. On the left, in the Project pane, you will see an object for each sheet in your workbook along with one for the entire workbook.

Useful Commands (M - Meta/Alt, C - Control, S - Shift)

VBA Embedded Project Structure¶

The VBA Editor enables you to edit a tree of VBA files corresponding to:

1. One Workbook object for the entire file, a “Workbook”
2. Sheet objects for each sheet in the file
3. Modules which store macros, functions, and subroutines
4. UserForms are collections of custom UI elements
5. Class Modules define custom objects

Within Workbook and Sheet files, you can add event-triggered functions that are tied to the entire file or a specific sheet.

• Workbook_Open triggers when the file is opened
• Workbook_BeforeClose triggers before closing
• Workbook_SheetChange detects cell edits
• Worksheet_Change within a sheet’s file runs after an edit
• Worksheet_SelectionChange triggers when a cell is selected

All macros and automation logic should be stored in Modules. Use your head and split code between modules in a sensible manner.

Private Sub Workbook_Open()
   MsgBox "Welcome! This workbook opened successfully.", vbInformation, "Hello!"
End Sub

Dim var1 As Integer
var1 = 2
userInput = InputBox("Test")

Debug.Print "User input was: " & userInput & " and var1 is " & var1

Variables, data types, functions, and scope¶

VBA has the same data types as any programming language. See this cheat sheet page for a good set of examples. Notably:

• The Variant type can take anything (and is the default type)
• Booleans are capitalized True and False
• Strings use double quotes unlike other Microsoft products
• Dates are M/D/YYYY - very American
• There are three special Excel data types:
  • Range("A1:A4")
  • Worksheet("Boiler 1")
  • Workbooks(1)

All variables are created in a given scipe. When they lose scope, the value is garbage-collected. You can use the following to declare variables:

• Dim for procedure-level variables
• Static to preserve values between calls

Procedures are contained within:

• Sub (Sub-procedure³)
• Function blocks (which by default handle args as pass-by-reference)

…both of which can be called in a similar manner.

' Sub procedure definition with two arguments:
Sub SubComputeArea(Length, TheWidth)

   Dim Area As Double ' Declare local variable.

   If Length = 0 Or TheWidth = 0 Then
      ' If either argument = 0.
      Exit Sub ' Exit Sub immediately.
   End If

   Area = Length * TheWidth ' Calculate area of rectangle.
   Debug.Print Area ' Print Area to Debug window.
End Sub

Conditional statements (If/Else, Select Case)¶

This example is from the VBA Docs :

Function Bonus(performance, salary)
   If performance = 1 Then
      Bonus = salary * 0.1
   ElseIf performance = 2 Then
      Bonus = salary * 0.09
   ElseIf performance = 3 Then
      Bonus = salary * 0.07
   Else
      Bonus = 0
   End If
End Function

' In a subroutine:
Debug.Print Bonus(1, 2390) '-> 239

Loops (For, While, Do Until)¶

Simple User I/O¶

Here is a very simple example of a macro that shows a pop-up input box to the user, and after hitting OK, presents the same data to the user.

Sub Macro4()
   Dim response As String
   response = InputBox("Write anything.")
   MsgBox ("Your input: " & response)
End Sub

Manipulating Tables in VBA¶

• The Object Model (Workbooks, Worksheets, Ranges)
• Selecting, copying, and formatting cells
• Manipulating rows, columns, and tables

The Object Model (Workbooks, Worksheets, Ranges)¶

Selecting, copying, and formatting cells¶

Manipulating rows, columns, and tables¶

Common Tasks¶

Finding Columns¶

Function FindColumnByName(sheet As Worksheet, columnName As String) As Integer
   Dim rng As Range
   Dim lastCol As Integer

   ' Find the last column in the first row
   lastCol = sheet.Cells(1, sheet.Columns.Count).End(xlToLeft).Column

   ' Loop through the first row to find the column name
   For Each rng In sheet.Range(sheet.Cells(1, 1), sheet.Cells(1, lastCol))
      If Trim(LCase(rng.Value)) = Trim(LCase(columnName)) Then
	 FindColumnByName = rng.Column
	 Exit Function
      End If
   Next rng

   ' Return 0 if column not found
   FindColumnByName = 0
End Function

Sub TestFindColumn()
   Debug.Print FindColumnByName(Sheet1, "Paystop") '-> 3
End Sub

Power Automate¶

Power Automate is a shiny new toy from Microsoft that enables the easy creation of macro-style event-driven workflows in the cloud. While not as powerful as macros, there are a certain number of useful tasks that can be performed with power automate.