All programming languages allow the use of variables, named areas of memory in which you can store data required by your program for its execution. To create a variable, you declare it, in other words, you notify Visual Basic of your intention to use a variable of a given name to store a given type of data. The keyword “Dim” (short for “Dimension”) is used to declare variables; thus, to declare that you will be using a variable called “strDepartment” to hold string (text) data, you would write “Dim strDept as String”.
When declaring a variable, it is not obligatory that you also declare the variable type; but it is useful to do so, since this prevents you from accidentally placing the wrong type of data into the variable. The “String” data type is used to hold text. For whole numbers, the “Integer” (small whole numbers) and “Long” (large whole numbers) are used. For real numbers (with decimals) “Single” (large real numbers) and “Double” (very large real numbers) are used. There is also the “Currency” data type which is used for large real numbers with exactly four decimal places. Then we have “Boolean”, a data type which always returns true or false and “Date”.
These then are the primitive (proper) data types, Excel VBA also allows you to place Excel objects into variables. Thus, if you want to perform a number of operations on a given worksheet, you could put a reference to that worksheet into a variable and manipulate the worksheet to your heart’s content simply by using the name of the variable. To declare the worksheet variable, you would use a statement like “Dim wks As Worksheet”. Then, to place a reference to a particular worksheet into your variable, you would use a statement like: “set wks = ActiveWorkbook.Worksheets(“Data”)”.
The location, within the module, where you declare a variable determines its scope: if you declare a variable inside a sub routine, then it will be local to that sub routine and the data it contains can only be used inside that sub. If you declare it at the top of the module, above all of the subs, then it can be accessed by all the subs within the module.
Your Excel VBA application may sometimes contain several modules and you want all modules to share certain variables, you can also make variables global. Global variables need to be declared at the top of any module but they use the keyword “Public” instead of “Dim”. Thus to declare a global date variable called “dtStartDate”, you would enter “Public dtStartDate As Date” at the top of one of your code modules.
Need to learn MS Excel 2007 VBA? We offer Microsoft Excel VBA tuition in London and all over the UK.
Get important advice in the sphere of Forex Trading – read the publication. The times have come when concise info is really only one click of your mouse, use this chance.
