One of the most commonly used statements in VBA is the Get statement. Its main purpose is to retrieve data from a file, record, or object.
The syntax of the VBA Get statement is:
Get [#]filenumber, [recnumber], [varname]
The ‘filenumber’ specifies the number of the open file from which the data needs to be retrieved. The ‘#’ symbol is used to indicate the mode in which the file is opened. If it is omitted, the file is opened in random mode. The ‘recnumber’ represents the number of the record in which the data is located. If the file is sequential or binary, this parameter can be omitted. The ‘varname’ is the variable in which the retrieved data will be stored.
If an error occurs while using the Get statement, it triggers an error code and sets the Error object properties. Therefore, it is essential to handle the errors using the ‘On Error’ statement in VBA.
To understand the usage of Get statement better, let’s look at some examples.
Examples of VBA Get Statement
Example 1: Retrieving Data from a TXT File
Suppose we have a text file named “data.txt” containing the following information:
Name: John Smith Age: 25 Occupation: Software Engineer
To retrieve the data from this file and display it in a message box using the Get statement, we can use the following code:
Dim filenumber as Integer filenumber = Freefile Open "C:\Users\Username\data.txt" For Input As filenumber Dim name, age, occupation as String Get filenumber, , name Get filenumber, , age Get filenumber, , occupation Msgbox "Name: " & name & vbNewLine & "Age: " & age & vbNewLine & "Occupation: " & occupation Close filenumber
In this example, we first open the text file for input and assign it a filenumber. Then, using the Get statement, we retrieve the data from each line and store it in the appropriate variable. Finally, we display the retrieved data in a message box.
Example 2: Retrieving Data from a Record in an Access Database
The Get statement can also be used to retrieve data from a specific record in an Access database. For this example, let’s assume we have a table named “employees” in our database, with columns for employee name, age, and department.
Dim filenumber as Integer filenumber = Freefile Open "C:\Users\Username\database.accdb" For Random Access Read Write Shared As filenumber Dim name, age, department as String Get filenumber, 3, name Get filenumber, 3, age Get filenumber, 3, department Msgbox "Employee Details:" & vbNewLine & "Name: " & name & vbNewLine & "Age: " & age & vbNewLine & "Department: " & department Close filenumber
In this example, we open the Access database for random access and read/write operations. Then, using the Get statement, we retrieve the data from the third record (employee) in the table and display it in a message box.
Example 3: Retrieving Data from an Object Property
The Get statement can also be used to retrieve data from an object’s property, such as a cell value in an Excel worksheet. Let’s look at the following example:
Dim ws as Worksheet
Set ws = ThisWorkbook.Worksheets("Sheet1")
Dim cellValue as Integer
Get ws, "B2", cellValue
Msgbox "Cell B2 value: " & cellValue
In this example, we assign the worksheet “Sheet1” to the object ‘ws,’ and then use the Get statement to retrieve the value of cell B2 and store it in the variable ‘cellValue.’ We then display it in a message box.
Example 4: Retrieving Multiple Data Types
The Get statement allows us to retrieve data of different types from a single file or object. For example:
Get filenumber, , myString Get filenumber, , myInteger Get filenumber, , myDate
In this example, we can retrieve a string, integer, and date value using the same filenumber, without having to open and close the file multiple times.
Example 5: Retrieving a Range of Data in One Statement
The Get statement can also be used to retrieve a range of data in one statement. For example:
Get filenumber, , (myString1, myString2, myString3)
This statement assigns the values of myString1, myString2, and myString3 from the current record to the corresponding strings in the parentheses.
Important Notes and Remarks
- The Get statement is used in conjunction with other statements, such as ‘Open’ and ‘Close,’ to retrieve data successfully.
- It is essential to specify the correct filenumber, record number, and variable name when using the Get statement.
- The recorded data must be of the same type as the variable to which it is assigned, or the conversion will fail.
- The Get statement can only retrieve data when the file, record, or object is opened in the proper mode.
In conclusion, the Get statement is a powerful tool in VBA that allows us to retrieve data from files, records, and objects easily. With the correct syntax and usage, it can help us automate tasks and improve the efficiency of our VBA code.
I hope this post was helpful in understanding the purpose, syntax, and usage of the Get statement in VBA.
Please feel free to share your feedback and views on this post. Do you have any other useful examples of using the Get statement? Let me know in the comments below. Happy coding!
