# Excel & VBA > Published Jan 01 0001, last updated Feb 02 2025 > By Ryan Fleck and written without LLMs! > Original manual at > Incredible writing of astonishing quality and insight - Happy Hacking! # Introduction {#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](https://www.youtube.com/watch?v=VI_riscmviI&list=PLJsVF3gZDcuTxcdH5FmQRTd6MiJ29X_OQ) playlist[^fn:1] 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.[^fn:2] 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](https://www.youtube.com/watch?v=ygB0ZviqXac) # Learning Resources {#learning-resources} **Documentation** 1. [Microsoft: VBA Language Refrence](https://learn.microsoft.com/en-us/office/vba/api/overview/language-reference) 2. [Microsoft: Excel VBA Reference](https://learn.microsoft.com/en-us/office/vba/api/overview/excel) 3. [Microsoft: Develop solutions and customize Excel](https://learn.microsoft.com/en-us/office/client-developer/excel/excel-home?redirectedfrom=MSDN) **Cheat Sheets** - [zerotomastery.io VBA Cheat Sheet](https://zerotomastery.io/cheatsheets/vba-cheat-sheet/) (simple) - [Analyst Cave VBA Cheat Sheet](https://analystcave.com/vba-cheat-sheet/) (comprehensive) # What are Macros? {#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 {#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 {#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. ```vba 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 {#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-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) | Keyboard Shortcut | Action | |-------------------|-------------------------------------------------------------------------------------------------------------------------------------------------------------------| | M-F11 | Open the VBA Editor | | C-g | Open the [immediate window](https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/use-the-immediate-window) (a repl for procedures) | | C-j | When hovering over an object, list its properties | | C-<SPACE> | Autocomplete | | F2 | Object browser | | F4 | Properties window | | F5 | Run the current procedure or macro | | F8 | Debug mode | | C-<BREAK> | Halt a running macro | | S-F2 | Jump to definition for a function or variable | | C-f | Find dialog | | C-h | Find and replace | ## VBA Embedded Project Structure {#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. ```vba Private Sub Workbook_Open() MsgBox "Welcome! This workbook opened successfully.", vbInformation, "Hello!" End Sub ``` ```vba 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 {#variables-data-types-functions-and-scope} VBA has the same data types as any programming language. See this [cheat sheet page](https://zerotomastery.io/cheatsheets/vba-cheat-sheet/#data) 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](https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/dim-statement) for procedure-level variables - [Static](https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/static-statement) to preserve values between calls Procedures are contained within: - [Sub](https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/sub-statement) (Sub-procedure[^fn:3]) - [Function](https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/function-statement) blocks (which by default handle args as pass-by-reference) ...both of which can be [called](https://learn.microsoft.com/en-us/office/vba/language/concepts/getting-started/calling-sub-and-function-procedures) in a similar manner. ```vba ' 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) {#conditional-statements--if-else-select-case} This example is from the [VBA Docs](https://learn.microsoft.com/en-us/office/vba/language/concepts/getting-started/using-ifthenelse-statements#testing-a-second-condition-if-the-first-condition-is-false): ```vba 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) {#loops--for-while-do-until} ## Simple User I/O {#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. ```vba Sub Macro4() Dim response As String response = InputBox("Write anything.") MsgBox ("Your input: " & response) End Sub ``` # Manipulating Tables in VBA {#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) {#the-object-model--workbooks-worksheets-ranges} ## Selecting, copying, and formatting cells {#selecting-copying-and-formatting-cells} ## Manipulating rows, columns, and tables {#manipulating-rows-columns-and-tables} # Common Tasks {#common-tasks} ## Finding Columns {#finding-columns} ```vba 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} 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. [^fn:1]: From [Hacker News](https://news.ycombinator.com/item?id=40682785) - "So there's this weird playlist about Excel by Martin Shkreli of all people..." [^fn:2]: "Visual Basic Classic" [wiki.org](https://en.wikipedia.org/wiki/Visual_Basic_(classic)) [^fn:3]: "Writing a Sub procedure" [microsoft.com](https://learn.microsoft.com/en-us/office/vba/language/concepts/getting-started/writing-a-sub-procedure) > Thank you for reading! > Find more content at > Source page: > Site index: [llms.txt](https://manuals.ryanfleck.ca/llms.txt)