class: center, middle, inverse, title-slide .title[ # An introduction to VBA ] .author[ ### Lars Relund Nielsen ] --- layout: true <div class="my-footer"> <span> <a href="https://bss-osca.github.io/tfa/mod-vba-intro" target="_blank">Notes</a> | <a href="02_vba-intro-slides.html" target="_blank">Slides</a> | <a href="https://github.com/bss-osca/tfa/blob/master/slides/02_vba-intro-slides.Rmd" target="_blank">Source</a> | <a href="https://github.com/bss-osca/tfa/blob/master/vba/vba-template.xlsm" target="_blank">Excel file</a> </span> </div> --- <!-- - Always try to use parentheses when you call subs and functions, e.g. `dtmTime = Now` will give the same result as `dtmTime = Now()` (it is not always possible when you call subs with no input). --> <!-- - Indent you code using the default standard --> <!-- - Specify return type of functions --> <!-- - Make procedures private whenever possible --> <!-- - Always use `Option Explicit` (can be se under Tools-Options: "Require variable declaration" in the VBA editor) --> ## What is VBA - A programming language intended to control and automate Microsoft Office applications (we use Excel). - VBA (Visual Basic for Applications) is an implementation of BASIC developed by Microsoft. - A [compiled language](https://en.wikipedia.org/wiki/Compiled_language#:~:text=A%20compiled%20language%20is%20a,%2Druntime%20translation%20takes%20place). That is, code need to be compiled first before running it. - You can only run VBA using the desktop version of Excel (not the web version). MS doesn't support VBA in MS 365. - With VBA you can extend Excel and automate tasks by coding different algorithms that for instance can be run by pressing a button. --- ## Setting up Excel for VBA .midi[ Use an English version of Excel, so the menus and Excel functions are in English! - <svg viewBox="0 0 384 512" style="height:1em;position:relative;display:inline-block;top:.1em;" xmlns="http://www.w3.org/2000/svg"> <path d="M318.7 268.7c-.2-36.7 16.4-64.4 50-84.8-18.8-26.9-47.2-41.7-84.7-44.6-35.5-2.8-74.3 20.7-88.5 20.7-15 0-49.4-19.7-76.4-19.7C63.3 141.2 4 184.8 4 273.5q0 39.3 14.4 81.2c12.8 36.7 59 126.7 107.2 125.2 25.2-.6 43-17.9 75.8-17.9 31.8 0 48.3 17.9 76.4 17.9 48.6-.7 90.4-82.5 102.6-119.3-65.2-30.7-61.7-90-61.7-91.9zm-56.6-164.2c27.3-32.4 24.8-61.9 24-72.5-24.1 1.4-52 16.4-67.9 34.9-17.5 19.8-27.8 44.3-25.6 71.9 26.1 2 49.9-11.4 69.5-34.3z"></path></svg>: **System settings > General > Language & Region** and add _English_ as primary language. - <svg viewBox="0 0 448 512" style="height:1em;position:relative;display:inline-block;top:.1em;" xmlns="http://www.w3.org/2000/svg"> <path d="M0 93.7l183.6-25.3v177.4H0V93.7zm0 324.6l183.6 25.3V268.4H0v149.9zm203.8 28L448 480V268.4H203.8v177.9zm0-380.6v180.1H448V32L203.8 65.7z"></path></svg>: **File > Options > Language** in Excel and under **Office display Language** choose English. For running VBA code the **Developer** tab needs to be visible in Excel. This can be done by check marking the **Developer** tab under the 'Ribbon and Toolbar' options in Excel. You find it by choosing **Excel -> Preferences -> Ribbon and toolbar** (<svg viewBox="0 0 384 512" style="height:1em;position:relative;display:inline-block;top:.1em;" xmlns="http://www.w3.org/2000/svg"> <path d="M318.7 268.7c-.2-36.7 16.4-64.4 50-84.8-18.8-26.9-47.2-41.7-84.7-44.6-35.5-2.8-74.3 20.7-88.5 20.7-15 0-49.4-19.7-76.4-19.7C63.3 141.2 4 184.8 4 273.5q0 39.3 14.4 81.2c12.8 36.7 59 126.7 107.2 125.2 25.2-.6 43-17.9 75.8-17.9 31.8 0 48.3 17.9 76.4 17.9 48.6-.7 90.4-82.5 102.6-119.3-65.2-30.7-61.7-90-61.7-91.9zm-56.6-164.2c27.3-32.4 24.8-61.9 24-72.5-24.1 1.4-52 16.4-67.9 34.9-17.5 19.8-27.8 44.3-25.6 71.9 26.1 2 49.9-11.4 69.5-34.3z"></path></svg>) or right click a tab and choose **Customize ribbon ...** (<svg viewBox="0 0 448 512" style="height:1em;position:relative;display:inline-block;top:.1em;" xmlns="http://www.w3.org/2000/svg"> <path d="M0 93.7l183.6-25.3v177.4H0V93.7zm0 324.6l183.6 25.3V268.4H0v149.9zm203.8 28L448 480V268.4H203.8v177.9zm0-380.6v180.1H448V32L203.8 65.7z"></path></svg>). In the **Developer** tab you open the VBA editor by pressing the **Visual basic** button (<svg viewBox="0 0 448 512" style="height:1em;position:relative;display:inline-block;top:.1em;" xmlns="http://www.w3.org/2000/svg"> <path d="M0 93.7l183.6-25.3v177.4H0V93.7zm0 324.6l183.6 25.3V268.4H0v149.9zm203.8 28L448 480V268.4H203.8v177.9zm0-380.6v180.1H448V32L203.8 65.7z"></path></svg> Alt + F11, <svg viewBox="0 0 384 512" style="height:1em;position:relative;display:inline-block;top:.1em;" xmlns="http://www.w3.org/2000/svg"> <path d="M318.7 268.7c-.2-36.7 16.4-64.4 50-84.8-18.8-26.9-47.2-41.7-84.7-44.6-35.5-2.8-74.3 20.7-88.5 20.7-15 0-49.4-19.7-76.4-19.7C63.3 141.2 4 184.8 4 273.5q0 39.3 14.4 81.2c12.8 36.7 59 126.7 107.2 125.2 25.2-.6 43-17.9 75.8-17.9 31.8 0 48.3 17.9 76.4 17.9 48.6-.7 90.4-82.5 102.6-119.3-65.2-30.7-61.7-90-61.7-91.9zm-56.6-164.2c27.3-32.4 24.8-61.9 24-72.5-24.1 1.4-52 16.4-67.9 34.9-17.5 19.8-27.8 44.3-25.6 71.9 26.1 2 49.9-11.4 69.5-34.3z"></path></svg> ⌥ + F11). A few useful shortcuts: - Toggle VBA editor and Excel (<svg viewBox="0 0 448 512" style="height:1em;position:relative;display:inline-block;top:.1em;" xmlns="http://www.w3.org/2000/svg"> <path d="M0 93.7l183.6-25.3v177.4H0V93.7zm0 324.6l183.6 25.3V268.4H0v149.9zm203.8 28L448 480V268.4H203.8v177.9zm0-380.6v180.1H448V32L203.8 65.7z"></path></svg> Alt + F11, <svg viewBox="0 0 384 512" style="height:1em;position:relative;display:inline-block;top:.1em;" xmlns="http://www.w3.org/2000/svg"> <path d="M318.7 268.7c-.2-36.7 16.4-64.4 50-84.8-18.8-26.9-47.2-41.7-84.7-44.6-35.5-2.8-74.3 20.7-88.5 20.7-15 0-49.4-19.7-76.4-19.7C63.3 141.2 4 184.8 4 273.5q0 39.3 14.4 81.2c12.8 36.7 59 126.7 107.2 125.2 25.2-.6 43-17.9 75.8-17.9 31.8 0 48.3 17.9 76.4 17.9 48.6-.7 90.4-82.5 102.6-119.3-65.2-30.7-61.7-90-61.7-91.9zm-56.6-164.2c27.3-32.4 24.8-61.9 24-72.5-24.1 1.4-52 16.4-67.9 34.9-17.5 19.8-27.8 44.3-25.6 71.9 26.1 2 49.9-11.4 69.5-34.3z"></path></svg> ⌘⇧´). - Run current procedure or continues execution after pausing (F5). - Auto complete code (Ctrl + Space). - Switch between subs/functions (Ctrl + Up/Down). - Use the debugger (F8). ] <!-- - How to avoid the enable macros dialog? If you have your Excel macro files in a trusted location you can add this location as trusted under `Options > Trust Center > Trust Center Settings > Trusted locations`. --> <!-- - How to avoid the "Be careful! Parts of your document may include personal information that can't be removed by the Document Inspector"? Go to `Options > Trust Center > Trust Center Settings > Privacy Options` and un-check the check box that says "Remove personal information from file properties on save". --> --- ## Let us try it out .midi[ An __algorithm__ is a finite sequence of well-defined instructions to solve a specific problem or to perform a computation. Let us try to program [this algorithm](https://github.com/bss-osca/tfa/blob/master/vba/vba-groups.xlsm) written in pseudo code: .instructions[ **Precondition**: A list of student names including e-mail and study program. **Postcondition**: A list of groups with 3-6 persons. For each program category do While students not allocated do Let n = number of students not allocated If n >= 10 allocate the first 5 students to a group Else if n = 5 or 6 create a group with the persons Else if n = 7 to 10 split the remaining students into two groups Return the student names of each group ]] --- ## Say Hello World Let us try to create our first procedure in VBA. Download the template file and 1. Add a new module by clicking the *Insert Module* icon. 2. Rename the module (named *Module1*) to *TM2_hello* (note you have to use underscores). 3. Open the module by double clicking on the module in the Process Explorer. 4. Add the code ``` ' Your first program/macro Sub TM2_SayHello() MsgBox ("Say hello world :-)") End Sub ``` 5. Run the code using the "Play" button or F5. 6. Add a button that run the procedure to the sheet *TM2*. --- ## The macro recorder Good idea as supplement to coding in the following cases: - Forgot syntax for font, format, color etc. - Help to make charts Note: Recorded code must be cleaned up and often changed. In many other cases, recording is NOT a good idea. Always indicate in your VBA code when something is recorded! ``` ' Start of recorded code ... ' End of recorded code ``` Have a look at the sub `TM2_MacroRecorder` in the template file. --- ## Basic building blocks in programming - _Variables_ store stuff in memory. - _Procedures_ (functions and subs) execute a set of instructions. - _Conditional statements_ are used to execute different instructions depending on a true/false statement. - _Loops_ are used to execute code repeatedly. - _Input/output_ are needed to read data and output the result. --- ## Variables .pull-left[ - Variables are used to store information in the program. - A variable is a box that can contain - A number - A string of text - A date - An array of integers - The variable name is the label on the box. ] .pull-right[ <div class="figure" style="text-align: center"> <img src="./img/storage.jpg" alt="Computer memory" width="120%" /> <p class="caption">Computer memory</p> </div> ] --- ## Basic datatypes A selected set of datatypes are (see all in the [documentation](https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/data-type-summary)): Name | Type | Details ------- | --------- | ------------------------------------------------------------------------------- Byte | Numerical | Whole number between 0 and 255. Integer | Numerical | Whole number between -32'768 and 32'767. Long | Numerical | Whole number between - 2'147'483'648 and 2'147'483'647. Double | Numerical | Floating decimal number between -1.79769313486232E308 and 1.79769313486232E308. String | Text | Text. Date | Date | Date and time. Boolean | Boolean | True or False. Variant | Any type | Any kind of data (default type if the variable is not declared). --- ## Memory .midi[ - Variables take up different storage place in memory. - Memory consists of bits (0/1). 1 Bit = Binary Digit, 8 Bits = 1 Byte, 1024 Bytes = 1 Kilobyte, 1024 Kilobytes = 1 Megabyte, 1024 Megabytes = 1 Gigabyte, 1024 Gigabytes = 1 Terabyte. - Size of selected datatypes: Data type | Storage size ----------------------------------------- | ------------------------ Boolean | 2 bytes Integer | 2 bytes Long (long integer) | 4 bytes Double (double-precision floating-point) | 8 bytes String | 10 bytes + string length * 2 bytes Variant (with numbers) | 16 bytes Variant (with characters) | 22 bytes + string length * 2 bytes ] - Declaring variables is good coding practice since it reduces the memory requirements and avoid type errors (use `Option explicit`). --- ## Declare variables Variables must be declared by ``` Dim {variable name} As {data type} ``` Example: ``` Dim intC As Integer intC = 2 Dim dblCost As Double dblCost = 2.45 ``` Run the procedure `TM2_DeclareVariables` in the Excel file. --- ## Procedures In VBA we deal with two kinds of procedures: - A `Sub` which can work as a “macro” in Excel, i.e. we can call it using e.g. a button. ``` Sub SubName(input As integer) ... End Sub ``` - A `Function` which can work like Excel functions, i.e. return a value. ``` Function FunctionName(input As String) As String ... End Function ``` Have a look at the function `TM2_StringJoin` in the Excel file. --- ## Using built-in functions Three types of functions: - Functions that are build into VBA - Excel functions (Worksheet/application functions) - Custom functions (yours or mine) ``` Now() ' VBA function - Date and time right now Rnd() ' VBA function - Random number between 0 and 1 WorksheetFunction.Sum(Range("D2:E5")) ' Excel function - Sum of range ``` Have a look at the sub `TM2_ExcelFunction` and `TM2_VBAFunction` in the Excel file. --- ## Communicate with the user - Simple message box: ``` MsgBox(“You are done!”) ``` - More advanced message box: ``` intAns = MsgBox("Delete Sheet 1 permanently?", vbOKCancel + vbQuestion) ``` Different buttons and their value can be seen in the [documentation](https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/msgbox-function) - Input box: ``` strAns = InputBox("What is your name?") ``` Return what is typed in as a string (if press Cancel then return an empty string) Have a look at the sub `TM2_Dialog` in the Excel file. --- ## Input/output You can use `Range` to read/write to a sheet: ``` Range("B7") = 107 ' Write 107 in cell B7 Range("H1") = "Test" ' Write Test in cell H1 Range("T4") = x ' Write value of varible x in cell T4 x = Range("B7) ' Set x equal to value in cell B7 ``` You must know which sheet you are considering (above the active one). Always refer to a sheet: ``` Worksheets("Groups").Range("B7") = 107 ' Write 107 in B7 in sheet Groups ``` As an alternative you may use `Cells`: ``` Cells(2,3) = 107 ' Write 107 to cell in row 2 and column 3 (C2) ``` Have a look at the sub `TM2_ReadWriteSheet` in the Excel file. --- ## Conditional statements (decisions) We can execute different instructions depending on a true/false statement. An example with an if statement: ``` If int < 7 Then MsgBox("Number is less than 7.") ElseIf int == 7 MsgBox("Number is 7.") Else MsgBox("Number is greater than 7.") End If ``` Have a look at the sub `TM2_CondStatement` in the Excel file. --- ## Loops Used to execute code repeatedly: ``` Sub Loops() For i = 1 To 3 MsgBox(i) Next End Sub ``` Have a look at the sub `TM2_Loops` in the Excel file.