To find out about the type of a variable, you can use "TypeOf ... Is ..." test. The tested types can only be object types and structure types, but the test can be applied to any variable, whether typed as an integer, a string or an object.
An example of TypeOf in Excel:
Set MyVar = Selection
Debug.Print "Selection is an object: " & TypeOf MyVar Is Object Debug.Print "Selection is a range: " & TypeOf MyVar Is Range Debug.Print "Sheets is of type Sheets: " & TypeOf Sheets Is Sheets MyStr = "Hello"
Debug.Print "Text is an object: " & TypeOf MyStr Is Object If TypeOf MyVar Is Range Then
Set MyCells = MyVar.Cells End If
An example test with a "Select Case True":
Set MyVar = new Collection Select Case True
Case TypeOf MyVar is Range Debug.Print "Range"
Case TypeOf MyVar is Collection Debug.Print "Collection" Case Else
Debug.Print "Other cases" End Select
You can further find out about a type using IsObject and TypeName functions: Debug.Print IsObject(Selection) 'True
3 http://msdn.microsoft.com/en-us/library/office/gg251516.aspx 4 http://msdn.microsoft.com/en-us/library/aa243358.aspx
Data Types
Debug.Print IsObject("Hello") 'False Debug.Print TypeName("Hello") 'String Debug.Print TypeName(4) 'Integer Debug.Print TypeName(3.5) 'Double Debug.Print TypeName(Selection) 'Range
Links:
• If...Then...Else Statement5 at Visual Basic for Applications Reference, msdn.microsoft.com
• TypeName Function6 at Visual Basic for Applications Reference, msdn.microsoft.com • IsObject Function7 at Visual Basic for Applications Reference, msdn.microsoft.com • IsArray Function8 at Visual Basic for Applications Reference, msdn.microsoft.com • IsDate Function9 at Visual Basic for Applications Reference, msdn.microsoft.com • IsNumeric Function10 at Visual Basic for Applications Reference, msdn.microsoft.com • IsNull Function11 at Visual Basic for Applications Reference, msdn.microsoft.com
5 http://msdn.microsoft.com/en-us/library/aa243382.aspx 6 http://msdn.microsoft.com/en-us/library/aa263394.aspx 7 http://msdn.microsoft.com/en-us/library/aa445054.aspx 8 http://msdn.microsoft.com/en-us/library/aa445040.aspx 9 http://msdn.microsoft.com/en-us/library/aa445042.aspx 10 http://msdn.microsoft.com/en-us/library/aa445052.aspx 11 http://msdn.microsoft.com/en-us/library/aa445050.aspx
14 Procedures and Functions
Functions are named blocks program code that perform a specific task and return a result. The task can be as simple as adding two numbers or as complex as launching a spacecraft. A subroutine is like a function, just that it does not return a result.
14.1 Defining procedures
An example function definition:
Public Function Sum(ByRef Number1 As Double, ByRef Number2 As Double) As Double
'Return the result by writing it to a variable having the same name as the
function
Sum = Number1 + Number2 End Function
An example subroutine definition:
Public Sub Tell(ByVal MyString1 as String, ByVal MyString2 as String) MsgBox MyString1 & MyString2
End Sub
Note the following:
• The arguments to the function are declared as ByRef1 which requires the compiler to make sure that only arguments of the specified type are used, in this case Double. • The function returns a value by assigning it to the function name as though the func-
tion were a variable. This contrasts with the use of the keyword return in many other languages.
14.2 Calling procedures
You can use or call the two procedures defined in the previous sections as follows:
'On the next line, argument order matters
Tell "Hello there.", ""
'On the next line, names of arguments are used and argument order does not
matter.
Tell MyString1:="Hello there,", MyString2:=" again."
'Tell ("Hello there.","") -- syntax error
MySum = Sum(123.456, 234)
Procedures and Functions
MySum2 = Sum(Number2:=8, Number1:=6)
'MySum3 = Sum Number2:=8, Number1:=6 -- syntax error
Note the following:
• The arguments (argument list) passed to a function must be enclosed in round brackets, whereas those supplied to a subroutine need not.
14.3 Procedure parts
Each function and subroutine has the following parts, some of the optional:
Visibility
Public, Friend or Private
Procedure Type
Sub, Function, Property Let, Property Get, Property Set
Name
A freely chosen name that starts with a letter and contains only letters, numbers and underscores.
Argument List
A list of the items of data that the procedure reads or writes into.
Return Type
For a Function or Property Get, the data type returned, such as Double or String.
Body
All the statements that do the work.
Only the Name and the Procedure Type are mandatory. Of course, a procedure without a body doesn't do anything.
14.4 Visibility
This seems like a very unimportant part of a procedure declaration to most people but in fact it is a very helpful feature. With it you can show that some procedures are just for use inside a module (Private), some only for use in this component (Friend) or available for the whole world (Public). You should mark procedures Private unless they will be called from outside the module. this will encourage you, and anyone who edits your program, to place related procedures in the same module which obviously makes maintenance easier.
Marking a procedure Private also means that you can have another procedure with exactly the same name in another module.
Early Termination
14.5 Early Termination
Use Exit Function or Exit Sub to terminate a procedure in the middle, like this:
Sub LengthyComputation(fat, n) If n = 0 Or n = 1 Then fat = 1 ' Now terminate Exit Sub End If
' Now compute fat ...
End Sub
14.6 Procedure type
All procedures are either functions that return a result as the value of the function, or subroutines that are called for their side effects. To return a value, you can use both, but with subroutine, you need to do it via an argument:
Private Function FunctionHalf(ByRef y as Double) as Double FunctionHalf = y / 2
End Function
Private Sub SubroutineHalf(ByRef y As Double, ByRef Result As Double) Result = y / 2
End Sub
The two procedures do essentially the same thing, that is, divide a number by two. The
Function version does it by assigning the new value to the name of the function while the Sub version assigns it to the name of one of the arguments. This affects how you use them.
The function version can be used in an expression as follows: Debug.Print FunctionHalf(10) 'Prints 5
To use the subroutine to return value, you need to store the value in a variable, like this:
Dim nHalf as Double SubroutineHalf 10, nHalf Debug.Print nHalf
Generally, you use a Function when the result is a single thing (number, string, object) and a Sub when you either want to return several distinct things or nothing at all.
Properties are also a form of procedure. Property Get is a function; Property Let and Property Set are subroutines. For more discussion of Properties, see the ../Object Oriented
Programming/2 chapter.
Procedures and Functions
14.7 Optional arguments
You can specify optional arguments and default values:
Function MySum(i1, i2, Optional i3) If IsMissing(i3) Then MySum = i1 + i2 Else MySum = i1 + i2 + i3 End If End Function
Function MyConcatenate(String1, String2, Optional Separator = " ")
'Default value for Separator specified
MyConcatenate = String1 & Separator & String2 End Function
Sub Test()
Debug.Print MySum(1, 2) * MySum(1, 2, 3) Debug.Print MyConcatenate("Hello", "World") Debug.Print MyConcatenate("Hello", "World", ", ") End Sub
Once an argument is declared optional, all arguments at the right of it have to be declared optional as well.
Links:
• Using Optional Arguments3, Office 2000, msdn.microsoft.com
15 Error Handling
Error handling in Visual Basic, an outline: • On Error Goto <Label>
• On Error Goto 0 • On Error Resume Next • If Err.Number = 0 • If Err.Number <> 0 • Resume • Resume <Label> • Resume Next • Err.Description • Err.Raise <Number>
Suppressing the error and detecting it using the non-zero error numberː
Set MyCollection = New Collection MyCollection.Add "Item", "Item" On Error Resume Next
MyCollection.Add "Item", "Item" 'This result in an error MyErrNumber = Err.Number
On Error Goto 0 'Restore the absence of error handling If MyErrNumber <> 0 Then
'Error occurred
MsgBox "Item already present in the collection." End If
Creating an error handlerː
Sub Test()
On Error Goto ErrorHandler ...
Exit Sub
ErrorHandler: ...
End Sub
Creating an error handler that differentiates per error numberː
Sub Test()
On Error Goto ErrorHandler ...
Exit Sub
ErrorHandler:
Select Case Err.Number Case 0
'No error
Case 5
Error Handling
Case Else
'...
End Select End Sub
See also ../Effective Programming#Errors_and_Exceptions1 and ../Cod-
ing_Standards#Error_Handling2.
1 Chapter 25.10 on page 134 2 Chapter 31.37 on page 213
16 Files
Another essential part of Visual Basic is file Input/Output, that is, working with files. While programming, you may want at some point to save data so they may be accessible for further use. This is where file I/O comes in. VB allows us to perform most operations available in Windows Explorer and DOS command line. Before you can do this, you need to understand how file I/O works.
16.1 Layout of Data
VB generally arranges data into records and fields for the programmer's convenience. The best way to explain this is by illustrating it:
Bob,Larry,George Kevin,Ken,Alec
This is obviously data containing names. Each line of data is called a record. So the first record contains Bob, Larry, and George. A field is each item in a record. So the first field in the first record would be Bob, the second field would be Larry, and so on. There may be as many fields and records as you see fit.
Records and fields are created really just for the programmer's convenience. For greater flexibility, you can also view the whole thing as a string. And you can split the string into records and fields yourself with the built-in Split() function.
16.2 Input
When a program receives data from an outside source, it is considered input. In Visual Basic, this source is usually a standard text file, with a .txt file extension(as which could be read with Notepad). First, you need to open the file from which you want to read the data. This is done using the following code:
Open <filename> For <mode> As <channelnumber>
Files
Open "c:\filename.txt" For Input As #1
The file path can be anything you want, if it doesn't exist, a new file (and directory(s)) will be created. The extension of the file doesn't matter much. It will not affect the content of the file nor the process of writing/reading the file. So you can also do this:
Open "c:\filename.myfile" For Input As #1
Or this:
Open "c:\filename" For Input As #1
The open and file path are self explanatory. However, "for input" states it is being used to receive data from an outside file, VB is "inputting" data from a file into variables . When outputting(writing to a file), this is changed to "for output". When we want to open a file in binary, it's "for binary". "As #1" is which channel is being used. When you are seeking specific information from the file you would use "for random". A channel could be seen as Visual Basic setting up a pathway to the file. Only one file can be used on a channel at a time. So if you want to open multiple files without closing any, you can use different channels. To input a line of data, it is fairly simply:
Input <channel>, <variables>
For example: Input #1, x, y, z
This will read data from whatever channel 1 is connected to. It will store the first field of the current record into X, and the second field into Y, and the third into Z.
Finally, we should ALWAYS close the file after the work is done. The syntax is simple: Close <channel>
As an example: Close #1
More on using input will be covered in the "Use of Data" section.
Note: If the code will be part of a larger program, the channel number should be obtained in a dynamic way instead of a hard-coded number. This is done using the function FreeFile()
Output
Dim nLogFile As Long nLogFile = FreeFile()
Open "c:\filename" For Input As #nLogFile ...
Close #nLogFile
16.3 Output
Output is very similar to input. The only difference is, well, it sends information OUT of the program and into a file. Once again, we shall only use text files (.txt). First you must open the file:
Open "C:\filepath.txt" For Output As #1
You may have noticed, the only thing that differs from opening a file for input, is the "For Output". Once this is done, everything else is simple.
Write #1, x, 5
Assuming X = 3, if you open the text document it will look like: 3, 5
You can write any value or variable to a file, however it is important to note that, when writing a string, it should be placed in quotes. If it is a variable containing a string, this is done automatically. Another important note is that two consecutive write statements will write to separate lines. For example:
Write #1, 5, 4 Write #1, 7, 4
Write #1, "this is a string"
Will appear in the file as 5, 4
7, 4
"this is a string"
Keep in mind, when outputting to a file, it will ERASE all prior information in the text. To add on to existed information, the "Append" command must be used.
If you are writing data to a file for use by some other program, you may find that you don't want the quotes that VB puts around your strings. You can avoid writing the quotes by using the Print command instead of the Write command. For example:
Files
Print #1, "this is a string"
Will appear in the file as this is a string
If, during runtime, the file at hand was modified, and a new line was added which contained necessary data, and the open statement ended as "#1", whatever was in line 1 of the file would be written over(only if the file was opened as Output). To solve this, a variable is dimmed as FreeFile (Dim FF as FreeFile). This will open the first free slot or line in the file at hand. The new Open statement would become:
Open "C:\filepath.txt" For Output As FF
(will erase file)
Open "C:\filepath.txt" For Append As FF
(will not erase file)
Using the second statement, the first free line in the file will be written to.