60
Data type
Storage size
Range
Currency
8 bytes
-922,337,203,685,477.5808 to 922,337,203,685,477.5807
(scaled integer)
Decimal
14 bytes
+/-79,228,162,514,264,337,593,543,950,335 with no decimal
point; +/-7.9228162514264337593543950335 with 28 places
to the right of the decimal; the smallest non-0 number is
+/-0.0000000000000000000000000001
Date
8 bytes
January 1, 100 to December 31, 9999
Object
4 bytes
Any Object reference
String
10 bytes + string
0 to approximately 2 billion characters
(variable-length)
length
String
Length of string
1 to approximately 65,400 characters
(fixed-length)
Variant
16 bytes
Any numeric value up to the range of a Double
(with numbers)
Variant
22 bytes + string
Same range as for variable-length String
(with characters)
length
User-defined
Number required The range of each element is the same as the range of its
(using Type)
by elements
data type
If you do not declare a variable’s type, it defaults to the Variant type. Variants take up more memory
than any other type because each Variant has to carry information with it that tells VBA what type of
data it is currently storing, as well as store the data itself.
Variantsuse more computer overhead when they are processed. VBAhas to figure out what types it is
dealing with and whether it needs to convert between types in order to process the number. If maximum
processing speed is required for your application, you should declare your variable types, taking advan-
tage of those types that use less memory when you can. For example, if you know your numbers will be
whole numbers in the range of -32000 to +32000, you would use an Integer type.
Declaring Variable Type
You can declare a variable’s type on a Dim statement, or related declaration statements such as Public.
The following declares Sales to be a double precision floating-point number:
Dim Sales As Double
You can declare more than one variable on a Dim:
Dim SalesData As Double, Index As Integer, StartDate As Date
The following can be a trap:
Dim Col, Row, Sheet As Integer
43
Chapter 1: Primer in Excel VBA
VB.NET PDF - Convert PDF with VB.NET WPF PDF Viewer Highlight Text. Add Text. Add Text Box. Drawing Markups. PDF Print. Work PDF to image file formats with high quality, support converting PDF to PNG, JPG, BMP and
changing font size in pdf form; add image to pdf form
35
Many users assume that this declares each variable to be Integer. This is not true. Col and Roware
Variantbecause they have not been given a type. To declare all three as Integer, the line should be
as follows:
Dim Col As Integer, Row As Integer, Sheet As Integer
Declaring Function and Parameter Types
If you have input parameters for sub procedures or function procedures, you can define each parameter
type in the first line of the procedure as follows:
Function IsHoliday(WhichDay As Date)
Sub Marine(CrewSize As Integer, FuelCapacity As Double)
You can also declare the return value type for a function. The following example is for a function that
returns a value of True or False:
Function IsHoliday(WhichDay As Date) As Boolean
Constants
You have seen that many intrinsic constants are built into VBA, such as vbYes and vbNo, discussed pre-
viously. You can also define your own constants. Constants are handy for holding numbers or pieces of
text that do not change while your code is running, but that you want to use repeatedly in calculations
and messages. Constants are declared using the Const keyword, as follows:
Const Pi = 3.14159265358979
You can include the constant’s type in the declaration:
Const Version As String = “Release 3.9a”
Constants follow the same rules regarding scope as variables. If you declare a constant within a proce-
dure, it will be local to that procedure. If you declare it in the declarations section of a module, it will be
available to all procedures in the module. If you want to make it available to all modules, you can
declare it to be Public as follows:
Public Const Error666 As String = “You can’t do that”
Variable Naming Conventions
You can call your variables and user-defined functions anything you want, except where there is a clash
with VBAkeywords and function names. However, many programmers adopt a system whereby the
variable or object type is included, in abbreviated form, in the variable name, usually as a prefix, so
instead of declaring:
Dim SalesData As Double
you can use:
Dim dSalesData As Double
44
Chapter 1: Primer in Excel VBA
43
Wherever dSalesData appears in your code, you will be reminded that the variable is of type Double.
Alternatively, you could use this line of code:
Dim dblSalesData As Double
For the sake of simplicity, this approach has not been used so far in this chapter, but from here onward,
the examples will use a system to create variable names. This is the convention used in this book:
❑
One-letter prefixes for the common data types:
Dim iColumn As Integer
Dim lRow As Long
Dim dProduct As Double
Dim sName As String
Dim vValue As Variant
Dim bChoice As Boolean
❑
Two- or three-letter prefixes for object types:
Dim objExcel As Object
Dim rngData As Range
Dim wkbSales As Workbook
In addition to these characters, a lowercase a will be inserted in front of array variables, which are dis-
cussed later in this chapter. If the variable is a module-level variable, it will also have a lowercase m
placed in front of it. If it is a public variable, it will have a lowercase g (for global) placed in front of it.
For example, malEffect would be a module-level array variable containing long integer values.
Object Variables
The variables you have seen so far have held data such as numbers and text. You can also create object
variables to refer to objects such as worksheets and ranges. The Set statement is used to assign an object
reference to an object variable. Object variables should also be declared and assigned a type as with nor-
mal variables. If you don’t know the type, you can use the generic term Object as the type:
Dim objWorkbook As Object
Set objWorkbook = ThisWorkbook
MsgBox objWorkbook.Name
It is more efficient to use the specific object type if you can. The following code creates an object variable
rng,referring to cell B10 in Sheet1, in the same workbook as the code. It then assigns values to the
object and the cell above:
Sub ObjectVariable()
Dim rng As Range
Set rng = ThisWorkbook.Worksheets(“Sheet1”).Range(“C10”)
rng.Value = InputBox(“Enter Sales for January”)
rng.Offset(-1, 0).Value = “January Sales”
End Sub
If you are going to refer to the same object more than once, it is more efficient to create an object variable
than to keep repeating a lengthy specification of the object. It also makes code easier to read and write.
45
Chapter 1: Primer in Excel VBA
43
Object variables can also be very useful for capturing the return values of some methods, particularly
when you are creating new instances of an object. For example, with either the Workbooks object or the
Worksheetsobject, the Addmethod returns a reference to the new object. This reference can be assigned
to an object variable so that you can easily refer to the new object in later code:
Sub NewWorkbook()
Dim wkb As Workbook, wks As Worksheet
Set wkb = Workbooks.Add
Set wks = wkb.Worksheets.Add(After:=wkb.Sheets(wkb.Sheets.Count))
wks.Name = “January”
wks.Range(“A1”).Value = “Sales Data”
wkb.SaveAs Filename:=”JanSales.xlsx”
End Sub
This example creates a new empty workbook and assigns a reference to it to the object variable wkb.
Anew worksheet is added to the workbook, after any existing sheets, and a reference to the new work-
sheet is assigned to the object variable wks. The name on the tab at the bottom of the worksheet is then
changed to January, and the heading Sales Data is placed in cell A1. Finally, the new workbook is saved
as JanSales.xlsx.
Note that the parameter after the Worksheets.Add is in parentheses. Because you are assigning the
return value of the Addmethod to the object variable, any parameters must be in parentheses. If the
return value of the Addmethod were ignored, the statement would be without parentheses, as follows:
wkb.Worksheets.Add After:=wkb.Sheets(wkb.Sheets.Count)
With...End With
Object variables provide a useful way to refer to objects in shorthand, and are also more efficiently pro-
cessed by VBA than fully qualified object strings. Another way to reduce the amount of code you write,
and also increase processing efficiency, is to use a With...EndWith structure. The final example in the
previous section could be rewritten as follows:
With wkb
.Worksheets.Add After:=.Sheets(.Sheets.Count)
End With
VBA knows that anything starting with a period is a property or a method of the object following the
With. You can rewrite the entire NewWorkbookprocedure to eliminate the wkbobject variable, as follows:
Sub NewWorkbook()
Dim wks As Worksheet
With Workbooks.Add
Set wks = .Worksheets.Add(After:=.Sheets(.Sheets.Count))
wks.Name = “January”
wks.Range(“A1”).Value = “Sales Data”
.SaveAs Filename:=”JanSales.xlsx”
End With
End Sub
You can take this a step further and eliminate the wks object variable:
46
Chapter 1: Primer in Excel VBA
43
Sub NewWorkbook()
With Workbooks.Add
With .Worksheets.Add(After:=.Sheets(.Sheets.Count))
.Name = “January”
.Range(“A1”).Value = “Sales Data”
End With
.SaveAs Filename:=”JanSales.xlsx”
End With
End Sub
If you find this confusing, you can compromise with a combination of object variables and With...End
With:
Sub NewWorkbook()
Dim wkb As Workbook, wks As Worksheet
Set wkb = Workbooks.Add
With wkb
Set wks = .Worksheets.Add(After:=.Sheets(.Sheets.Count))
With wks
.Name = “January”
.Range(“A1”).Value = “Sales Data”
End With
.SaveAs Filename:=”JanSales.xlsx”
End With
End Sub
With...End Withis useful when references to an object are repeated in a small section of code.
Making Decisions
VBA provides two main structures for making decisions and carrying out alternative processing, repre-
sented by the If and SelectCase statements. If is the more flexible one, but SelectCase is better
when you are testing a single variable.
If Statements
Ifcomes in three forms: the IIffunction, the one-line Ifstatement, and the block Ifstructure. The fol-
lowing dTax function uses the IIf (Immediate If) function:
Function dTax(dProfitBeforeTax As Double) As Double
dTax = IIf(dProfitBeforeTax > 0, 0.3 * dProfitBeforeTax, 0)
End Function
IIfis similar to the Excel worksheet IFfunction. It has three input arguments: the first is a logical test,
the second is an expression that is evaluated if the test is true, and the third is an expression that is eval-
uated if the test is false.
In this example, the IIf function tests that the dProfitBeforeTax value is greater than 0. If the test is
true, IIf calculates 30% of dProfitBeforeTax. If the test is false, IIf calculates 0. The calculated IIf
value is then assigned to the return value of the Tax function. The Tax function can be rewritten using
the single-line If statement as follows:
47
Chapter 1: Primer in Excel VBA
Documents you may be interested
Documents you may be interested
