Visual Basic for Applications in Microsoft Excel (1)

1. Visual Basic for Applications in Microsoft Excel(1) Week 2

2. Week 2 Macros revisited The VBA Editor The object model Using variables If statements

3. Revision Exercises Record a macro that formats highlighted cells to appear as integers (enter some decimal numbers to test it on) Create a Message Box that looks like this: (tip the icon is called vbInformation)

4. Refining macros • Your macro might look something like this: Sub Macro2() ' ' Macro2 Macro ' ' Selection.NumberFormat = "0.00" Selection.NumberFormat = "0.0" Selection.NumberFormat = "0" End Sub

5. Refining macros • Your macro should look something like this: Sub IntegerFormat() ' Format highlighted cells as integer Selection.NumberFormat = "0.00" Selection.NumberFormat = "0.0" Selection.NumberFormat = "0" End Sub

6. Refining macros • Your macro should look something like this: Sub IntegerFormat() ' Format highlighted cells as integer With Selection .NumberFormat = “0“ End With End Sub

7. Refining Macros • Formatting Fonts • Record a new macro to format the selected cells as follows: • Tahoma, Bold, Size 14 • Edit your macro using With Selection.Fontensuring that there is no unnecessary code (don’t forget to End With)

8. The VBA Editor • User friendly environment for writing VBA code • Access the VBE by pressing Alt-F11 • The programming workspace includes: • The Code window • The Project Explorer • Worksheets + Chart Sheets • ThisWorkbook • Modules (for VBA code) • User forms (for dialog boxes) • The Properties Window

9. Visual Basic Editor Code window Project explorer Properties window Immediate window

10. Good Programming Practices • Provide sufficient comments • Start the line with a single quote • The line is coloured green and ignored by VBA • Indent consistently • Provide a logical structure to your program • Use white space liberally • Use Names wisely – “Macro1” is fine now, but less helpful for future reference

11. The Object Model – Car example • Objects • Car, Engine, Spark plugs, Door(s) • Properties (Values) • Colour (red), Type (1.4l), Age (4 years), size (4-door), Locked (True) • Methods (arguments) • Do Locking (central), Turn over, Spark, Drive (10mph), Reverse • Events • Hit wall, Reach speed limit, Driver turns key

12. The Object Model • Examples of Objects: • Ranges, worksheets, charts, workbooks • Each object has properties, i.e. the attributes of the object • e.g., a cell has a Value property (either text or number in the cell), a Formula property (the formula in the cell) and a HorizontalAlignment property (left, center or right).

13. The Object Model Each object has methods, i.e. the things you can do to an object e.g., a cell has the ClearContents method to erase the content of the cell (equivalent to the delete key). Some methods have arguments, i.e. qualifiers indicating how a method is performed e.g., the Copy method has a Destination argument.

14. Working with objects using VBA • Objects can be manipulated using the collections they belong to, specifying their location in the object hierarchy using the dot notation. • Worksheets(“Sheet1”) • Worksheets(1) • Workbooks(“Book1”).Worksheets(“Sheet1”) • Worksheets(“Sheet1”).Range(“A1”) • Application.Workbooks(“Book1”)._ Worksheets(“Sheet1”).Range(“A1”)

15. The Object Browser • Online help tool. • Click on the Object Browser button in the Standard toolbar. • Select Excel Libraries • List of all objects (on the left) • List of properties and methods for each object (on the right) • Properties: hand icon • Methods: green rectangular icon • To get help on any item, select it and click on the question mark button.

16. Variables Variable: A variable is a mechanism which enables you to store information and use it while a program is running. As the name implies, it is possible to change the value during the running of a program

17. Variables Option explicit Sub hello() Dim username As String username = InputBox("Please enter your name") MsgBox "Hello " & username End Sub

18. Variable names Valid variable names can contain text or numbers but they must use a letter as the first character. You can't use a space, period (.), exclamation mark (!), or the characters @, /, &, $, # in the name. Names cannot exceed 255 characters in length (short and to the point is best) Be descriptive

19. Variable types Byte Integer Long Single Double Currency Boolean Date String Object Variant 0 to 255 -32768 to 32767 -2.1 bn to 2.1 bl Regular decimal numbers Large decimal numbers -9.22 trn to 9.22 True / False 1/1/100 to 31/12/9999 “2bn characters” Any object reference Avoid

20. Variable names and types • Suggest suitable variable names and types for variables representing: • GDP of a country • Client name • Client reference number • Price of an item of stock • Number of items in stock • Number of staff in a department • Agent’s commission rate • Invoice due date • Invoice state (paid or unpaid)

21. Exercises • Open week3.xls • Open the VBE and look at the Macro Orders • Why won't it run? • Can you fix the errors?

22. Declaring and assigning variables • Variables are declared to exist within a program • A variable can be initialised with a value • A variable can have different value while a program runs • DimxxxxAs type • Dim stands for dimension • As assigns type Dim x, y, z as integer (wrong) Dim x as integer, y as integer, z as integer (correct)

23. Option Explicit Sub Example() Dim x As Byte x = InputBox (“Enter a number between 1 and 10”) MsgBox x End Sub • Use Option Explicit at the head of a module to force variable declaration • You can automatically use Option Explicit • Tools>Options>Require variable declaration

24. Assigning variables • Variable on the LHS, Value on the RHS • x = 1 the value 1 is assigned to x • SalesRegion = “North West” • use double quotes when assigning string variables • NextFinYear = #1-April-2009# • Use # to enclose the value of a date, NB date format defaults to US (very inconvenient!)

25. Assigning variables • DeliveryDate = OrderDate + 3 • y = x • a variable y is assigned the value of x • OrderTotal = Subtotal * VatRate • OrderNo = OrderNo + 1 • increases the value of a variable called OrderNo by 1 (called incrementing) • y = Range(“B3”).Value

26. Arithmetical Operators

27. Calculations with variables Sub example1() 'declare variables Dim x as integer, y as integer, z as integer ' assign values to variables x = 3 y = 4 z = x + y ' output result Msgbox z End sub

28. Using variables sub enterNumbers () Dim Number as Integer number = InputBox("Enter number under 5000", "Enter_ numeric data") number = number * 2 MsgBox "The number multiplied by 2 is " & number, _ , ,"Greeting Box" End Sub

29. Concatenation • Concatenation operator & is used to join things together e.g. a number to text. For example: MsgBox "You will be paid " & payrate & " per hour" • “&” concatenatesstringandnumeric variables, so you should use it if you want to combine a string and a number • There is also + but this can only concatenate one string to another; if you use it to concatenate a number to text you will get a run-time error

30. Exercises Introductory Reading cells

31. Constants Used for a value that recurs but never changes throughout the programme Const taxRate = 0.28 Can now refer to taxRate during the programme and can easily update the value by changing this one line of code

32. Built-in Constants • Prefix vb or xl in the Object library • ColorConstants • Excel Direction

33. If statements If conditionThen doSomething ElseifanotherConditionThen doSomethingElse ElseIfanotherConditionThen doSomethingElse Else doSomethingElse End If

34. If statements – an example Sub Profit_Loss () Dim profit As single profit = Range("C1").Value If profit > 0 Then MsgBox "You have made a profit" ElseIf profit = 0 Then MsgBox "You have broken even" Else MsgBox "you have made a loss" End If End Sub

35. If statements – an example Sub Profit_Loss () If Range("C1").Value > 0 Then MsgBox "You have made a profit" ElseIf Range("C1").Value = 0 Then MsgBox "You have broken even" Else MsgBox "you have made a loss" End If End Sub

36. Exercises Pebbles and Muffins

37. Next week Assessed Exercise Variables and if statements Using Ranges in VBA