VBA applies logic behind the scenes. Files Must be saved as macro-enabled workbooks (.xlsm as opposed to .xlsx) Enable macros by selecting →File→Options→Trust Center→Trust Center Settings→ Macro Settings Definitions Objects correspond to nouns, properties correspond to adjectives, methods correspond to verbs, and arguments correspond to adverbs. Collection objects (these are plural) contain all individual objects of a certain type Help Object Browser (<F2>) Lists all objects, properties, and methods The Visual Basic Editor (VBE) The VBE is the programming workspace. It’s easy to toggle back and forth (<ALT>< F11>) between the Excel worksheets and the VBE.
Code is stored in a module. →Insert→Module Start a SUB (subroutine) →Insert→Procedure [type in a name] →OK (the name must be a single word) Put all commands between: “Public Sub name()” and “End Sub” In general, have separate SUBS for separate tasks (a macro is a SUB). To run a SUB, press <F5>. To step through 1 line at a time, press <F8> Setup →Tools→Options→Editor→Require Variable Declaration Uncheck: →Tools→Options→Editor→Auto Syntax Check Typing Indent for readability (use the <Tab> key or →Edit→Indent). To remove an indentation, use <Shift><Tab> or →Edit→Outdent)
If a variable appears to the left of an equals sign, then its new value in memory becomes whatever is on the right of the equals sign. To assign a value to a variable: unitCost = 1.20 unitsSold = 20 totalCost = unitCost * unitsSold totalCost = totalCost + 20 TeamName = “Tigers” Input Boxes and Message Boxes Two VBA built-in functions: InputBox(prompt to user in quotes, title in quotes) (the title is optional) The following creates an input box called “Selling price” that asks the user to “Enter the product’s unit price.” The value is stored in the variable called “price”. price = InputBox(“Enter the product’s unit price.”, “Selling price”) MsgBox(statement in quotes, button, title in quotes) (the button indicator and title are optional) The following creates a message box called “Selling price” that states the price and includes an “information” button. MsgBox “The product’s unit price is $2.40.”, vbInformation, “Selling price”
To concatenate two strings together, use the ampersand (&). The following SUB gets a product’s name and then displays it in a message box: Sub GetProductName() Dim product As String product = InputBox("Enter the product's name.") MsgBox "The product's name is " & product & ".", vbInformation End Sub To insert a carriage return (start on a new line) in a message box, put the following between the first line and the second line: & vbNewLine & Exercise 1 Open a new workbook and save it as Input Output 1.xlsm. Then create a SUB called RevenueCalc that does the following: (1) It asks the user for the unit price of some product and stores it in the variable unitPrice, defined as Double type; (2) it asks the user for the number of items sold and stores it in the variable quantitySold, defined as Long type; (3) it calculates the revenue from this product and stores it in the variable revenue, defined as Double type; and (4) it displays a message such as “The revenue from this product was $380.”
→Insert→Procedure [type in a name] →[Click on Function]OK (the name must be a single word) Place the arguments [defined As Type between the ()] Put As Type after the parenthesis (where type is the variable type of the returned value) Put all other commands between: “Public Function name()” and “End Function” A function subroutine can be used in one of two ways. It can be called by another SUB (or even another function subroutine), or it can be used as a new function in an Excel formula. To make your function available in all future workbooks, save it in your Personal.xlsb library file. Warning: A few math commands are different in VBA than in Excel. In particular: Natural Logarithm of 20 = log(20), not LN(20) Square Root of 20 = sqr(20), not SQRT(20)