CST-273-01 Microsoft VBA R. Juhl

1. CST-273-01Microsoft VBAR. Juhl Storing and Modifying Information

2. Storing and Modifying Information • In this Section you will cover • Using Variables in programs • Using Constants in programs • Working with various data types • Changing data by using operators • Designing a report for Excel

3. Storing and Modifying Information • Variables, Data Types, and Constants • Using the context of the Excel Application • What types of values can be entered into an excel spreadsheet cell and how might you use them? • Numbers • Format • Decimal place • Integer • Float • Currency • Percentage

4. Storing and Modifying Information • Text • Displayed as entered • Automatically converted to a date or time • The content of a cell can be changed or deleted at any time • Cells are temporary storage containers for numbers and text that can be displayed and used in various formats • Similar to a variable in programming • Storage of temporary data

5. Storing and Modifying Information Range(“C6”).Value = “Average:” • The code above stores the text “Average” to spreadsheet cell C6. • You can just as easily declare ‘ Declare a variable and its data type Dim StringValue-1 as String ‘ assign a value to the string variable stringValue-1 = “Average:” ‘ use the variable in assigning a value to a cell Range(“C6”).Value = stringValue-1

6. Storing and Modifying Information • Understanding variables and constants • Variable: • Variables have a scope and a data type • Variables values can be modified any time the program can access them • Constants • Retain their value all the time • Always use comments to describe what the variable or Constant does

7. Storing and Modifying Information • Declaring Variables • Option Explicit • If used, the OptionExplicit statement must appear in a module before any procedures. • When Option Explicit appears in a module, you must explicitly declare all variables using the Dim, Private, Public, ReDim, or Static statements. If you attempt to use an undeclared variable name, an error occurs at compile time. • Variant • The Variant data type is the data type for all variables that are not explicitly declared as some other type (using statements such as Dim, Private, Public, or Static). The Variant data type has no type-declaration character.

8. Storing and Modifying Information • Scope • Private • Privatevariables are available only to the module in which they are declared. • Use the Private statement to declare the data type of a variable. For example, the following statement declares a variable as an Integer: • Private NumberOfEmployees As Integer • Public • Variables declared using the Public statement are available to all procedures in all modules in all applications

9. Storing and Modifying Information • Example of Variables using the Option Explicit statement Option Explicit ‘This variable is visible to other modules Public MyPublicVariable As String ‘This variable is visible only to this module Private MyPrivateVariable As String ‘Using Dim is the same as making the variable Private Dim MyDimVariable As String

10. Storing and Modifying Information ‘ A constant that is only used for conditional compilation ‘You can’t define this as Public or Private, VBA hides the ‘value from other modules #Const Debug_ = “false” ‘This constant is visible to other modules Public Const MyPublicConstant = “Hello” ‘This constant is visible only to this module Private Const MyPrivateConstant = “Hello”

11. Storing and Modifying Information • Using the Dim keyword makes a variable or constant Private Public Sub DataDeclarations() ‘Only this sub can see this variable and constant Dim MyDimSubVariable As String Dim MySubConstant = “Hello” End Sub

12. Storing and Modifying Information • You can assign a data type to a constant and you can assign a value • Using constants can make your application faster. • Whenever you can, declare values as constants, rather than variables.   Since their values never change, they are evaluated only once when your code is compiled, rather than each time they are used at run time. • Using constants makes your code easier to maintain • If you want to change a paramater value that’s used in several or even hundreds of places you only need to change the constatant value once, where its declared.

13. Storing and Modifying Information • Using Constants Make your code easier to read / understand • Defining Data Types • Important in understanding a value and working with it • VBA supports a numbe of datatypes including • Byte 1 byte • Boolean 2 bytes • Integer 2 bytes • Long 4 bytes • Currency 8 bytes • Decimal • Single (floating point) 4 bytes • Double (floating point) 4 bytes • Date 8 bytes • String (variable length) 10 bytes + string length • Object (any object reference) 4 bytes • Variant (with numbers) 16 bytes

14. Storing and Modifying Information • String Dim myString as String ‘Declare the variable myString = “Average” ‘Assign a value Range.(“C6”).Value = myString ‘ codeing a cell on a workshee • Integer Dim myVar As Integer

15. Storing and Modifying Information • Variable Naming Conventions • Must begin with an alpabetic character • Cannot exceed 255 characters • Cannot contain spaces • Avoid the use of punctuation marks and unusual characters • Many are not allowed • The underscore is allowed and works well for separating multiple words within a single variable name i.e. • Dim first_name As String = “Ima”

16. Storing and Modifying Information • Variable Naming Conventions • Don’t repeat / reuse variable names within the same scope • Variable names should be descriptive of the value it will hold • Dim numberOfCats As Integer • Dim x As Integer • The programming preference for most languages is to use lower case for the 1st letter and uppcase for the 1st letter of any following words • Try and keep variable names under 12 characters

17. Storing and Modifying Information • Although it is not required, it is a good idea to place all variable declarations of a procedure / module at the start of your code. • With variable declarations at the beginning you will be able to find / debug your code more quickly • Option Explicit (revisited) • Use in the general section of a “module” window to force explicit variable declarations. • Otherwise, variables can be dimensioned (the act of declaring variables and allocating storage space) implicitly • Without using the DIM Statment

18. Storing and Modifying Information • Option Explicit (revisited) • Without Option Explicit declared • You can begin using a new variable without ever declaring it with the Dim statement. • This is not good programming practice • Makes your code harder to read / interpret • Makes your code more difficult to debug • You can automatically include Option Explicit by • Selecting: • Tools  Options • Checking the “Require Variable Declaration” option

19. Storing and Modifying Information

20. Storing and Modifying Information • Object and Standard Modules • Module refers to a related set of declarations and procedures • Each Module will have a separate window in the VBA IDE • Depending on the origination will have different behavior with regard to variable declaration • An object module is associated with an object • The Worksheet object • The module automatically contains all the event procedures associated with wooksheet … worksheet1

21. Object Module

22. Standard Module (add via insert menu)

23. Storing and Modifying Information • Standard modules are contained within a separate folder in the project explorer • They can be renamed in the properties window • Standard Modules contain variable declarations and programmer-defined procedure • Variable Scope • The time when a variable is visible or available to the program • In its scope – can be accessed / manipulated • Out of scope – unavailable / invisible to the program

24. Storing and Modifying Information • Variable Scope • A variable declared within the code block of a procedure such as the click() event procedure is a procedural level variable • Only available while program execution occurs within the procedure that the variable was declared in • The variable myVar4 in the previous slide is only visible to the program while executing the event procedure of the worksheet • Activate() • Resources are allocated then released only for the time the Activate () event executes

25. Storing and Modifying Information • Each time the Activate() procedure is run the variable is created and destroyed • The variable does not retain its value between calls • If needed the keyword Static can be used to tell VBA to remember the value of the variable between calls to a procedure Private Sub Worksheet_Activate() Static myVar4 As Integer myVar4 = myVar4 + 1 End Sub

26. Storing and Modifying Information Private Sub Worksheet_Activate() Static myVar4 = myVar4 + 1 End Sub • In this procedure the value of myVar4 is incremented by one with each call to the procedure • Replacing the declaration with Dim myVar4 = myVar4 + 1 means the value of myVar4 never grows larger than one • Note integer values are initialized to a value of zero or 0 at declaration • The area outside of any predefined procedure is known as the general declarations section of the module (object or standard) • This area is only used for declarations

27. Storing and Modifying Information

28. Storing and Modifying Information • Declaring a variable outside of a procedure with a Dim statement makes it a module level variable • The scope depends on the keyword used in the declaration • Three variables are declared with • Dim, Private, and Public keywords in the general area Dim myVar As Integer Private myVar2 As Integer Public myVar3 As Integer • Private and Publickeywords are only allowed from variable declaration in general declarations

29. Storing and Modifying Information Dim myVar As Integer Private myVar2 As Integer Public myVar3 As Integer • Each of these general declarations are visible to any procedure within this module • In addition myVar3 is visible to any procedure in any module of this project. • Variables declared in the general section with the word Public are commonly referred to as global

30. Storing and Modifying Information • When declaring a variable with the keyword Public in the general section of a module • If must be reference in other modules of the project by • Indentifying the name of the object module • To reference Public myVar3 of the sheet1 module from another module use • Sheet1.myVar3 = 5

31. Storing and Modifying Information • Numerical data types • An integer declared as integer or long data type • Can hold non-fractional or whole numbers within ranges • Integer – 2 bytes • -32,768 to 32,767 • 1000000000000000 to 0111111111111111 • In binary • Long – 4 bytes • -2,147,483,648 to -2,147,483,647

32. Storing and Modifying Information • If you need to hold a fractional or floating point number use • Single – 4 bytes • Double – 8 bytes • If the number gets to large for the data type you declared for a variable • The program will crash Dim myNum As Integer myNum = 5000 • Will cause an overflow error

33. Storing and Modifying Information • Mixing data types in calculations may cause round off errors. Dim answer As Integer Dim num1 as Single Dim num2 as Integer num1 = 5.3 num2 = 6 answer = num1 * num2 • Produces the value of 32 not 31.8 as you may want • The result is stored in an integer and rounded up • Changing the answer variable data type to single will correct the error

34. Storing and Modifying Information • Math operators • Addition + • Subtraction - • Multiplication * • Division / • Exponential ^

35. Storing and Modifying Information • String Data Types • Used to hold characters as text • Numbers • Letters • Special symbols • Punctuation marks • Use the String keyword to declare a variable of type string Dim myText As String myText = “VBA IDE”

36. Storing and Modifying Information • There are two types of string variables • Variable length – 10 bytes + string length • Fixed length – the length of the string (1 – 65,400) • To declare a fixed length string Dim myString as String * 8 myString = “abcdefgh” • The declared string can hold a maximum of 8 characters • If you initialize the variable with more than 8 characters only the first 8 will be used

37. Storing and Modifying Information • A lot of what programmers do with strings revolves around extracting desirable information out of them. • A search engine may look at all the text on a web page for certain words and store the results in a database • The engine may load the entire textual contents of a web page into a string variable and extract or search for occurrences of keywords • When users search a database with keywords, their stored in string variables and compared to database content

38. Storing and Modifying Information • More on Strings….. • Variant Data Type • General category in the number format of a spreadsheet cell in the Excel application • Variants are declared as variants by using the keyword Variant, or not specifying a data type Dim myVar Dim myVar2 As Variant • Have the same effect • Variants can hold any type of data except a fixed length string • Variant data type relax the restrictions on the value a particulare variable can hold • Gives the programmer more flexibilty

39. Storing and Modifying Information Dim myVar As Variant myVar = “z” . . . myVar = 123.123 . . . myVar = 790765

40. Storing and Modifying Information • Magic Squares Assignment requires the use of the • Storing and Modifying Information Lab • CST-273 09/17/2008 • Objective: Practice entering code using VBA with Excel, declaring and using variables to create a “Magic Squares” spreadsheet where the sum of any row / columns / diagonals adds up to the same number (in this case 15) using numbers 1 through 9 uniquely for each cell. • Open a new Excel file / worksheet • Highlight the cell range of B3:D5 to select a “3 x 3” Matrix • Format the cells to have a dark color background of Navy Blue and a foreground color of Yellow • Format the cells to have a font size of 20 • Format the cells to center their values • Using the SelectionChange() event procedure of the worksheet to code • Double click the worksheet name in the VBA project explorer window • Select worksheet from the project dropdown list • Select SelectionChange() from the procedure drop down list

41. Storing and Modifying Information • Declare 8 variables that represent the 3 rows, 3 columns, and 2 diagonal sums • Assign values to each of the above variables by summing the values contained in the tree rows, three columns, and two diagonals Cells of the 3X3 matrix • Variable1 = Range(“cell#”).value + Range(“cell#”).value + ……… • Were cell# is the actual cell location i.e. B3 • Copy the results of the summed variables around the border of the 3 x 3 matrix by assigning each sum variables “value” to the cells adjacent to the right end of the three rows, bottom of the 3 columns, and upper and lower right adjacent cell of the two diagonals. • The playing board when correctly filled in looks something like this. 15 29415 75315 61815 151515 15 • Extra Credit: Add a Clear button that clears out the playing field

42. Storing and Modifying Information • Simple input and output with VBA • We have used the value property of a spreadsheet cell to get user input • You may want to prompt the user for input & output to the user • InputBox() ‘ gets input from the user • OutputBox() ‘ sends output back to the user • When you want to force a response use the InputBox() function • The inputbox() sends a dialog box that must be addressed

43. Storing and Modifying Information • The InputBox() function returns the data entered by the user as a string • Click • OK returns the string • Cancelreturns a zero-length string “ ” • Syntax InputBox(prompt [, title] [, default] [, xpos] [, ypos] [, helpfile, context] )

44. Storing and Modifying Information Sub test() Dim myString As String myString = InputBox("Please Enter Your Name", "User Input", "Last, First", 5000, 5000) End Sub

45. Storing and Modifying Information

46. Storing and Modifying Information • Manipulating Strings • A string is a sequence of characters • Characters can include control codes • vbCrLf is the control code for a new line • A string can contain punctuation • A string can contain special features • Circumflex • Umlaut

47. Storing and Modifying Information • Adding Strings Together with +or & • To concatenate or join together two or more string elements • You may need to take information from more than one place and combine them MyVar = “A” + “B” MyVar = “A” & “B” • Produce the same results

48. Storing and Modifying Information • The + Operator (Visual Basic) • has the primary purpose of adding two numbers. • It can also concatenate numeric operands with string operands. • The + operator has a complex set of rules that determine whether to • add, concatenate, signal a compiler error, or throw a run-time exception.

49. Storing and Modifying Information • 5 + 8 • Produces 13 • addition • "5" + "8“ • Produces 58 • concatenation • 5 + "8“ • Produces 13 • Performs an auto type cast of the string to a numeric and adds

50. Storing and Modifying Information • The & Operator (Visual Basic) • defined only for String operands, and it always widens its operands to String, regardless of the setting of Option Strict. Dim x As Integer Dim y As String y = 999 x = 1 MsgBox (x + y) MsgBox (x & y) What are the results ????