VBA Projects

Full Access with Source Code
  • Designed and Developed by PNRao

  • Full Access with VBA Source Code

  • Well Commented Codes Lines

  • Creative and Professional Design

Manage Your Projects

120+ Project Management Templates

Seamlessly manage your projects with our powerful & multi-purpose templates for project management.

120+ PM Templates Includes:
  • 50+ Excel Templates

  • 50+ PowerPoint Templates

  • 25+ Word Templates

Share Post

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!

Effortlessly Manage Your Projects and Resources
120+ Professional Project Management Templates!

A Powerful & Multi-purpose Templates for project management. Now seamlessly manage your projects, tasks, meetings, presentations, teams, customers, stakeholders and time. This page describes all the amazing new features and options that come with our premium templates.

Project Management Templates

All-in-One Pack
120+ Project Management Templates

Essential Pack
50+ PM Templates

Excel Pack
50+ Excel PM Templates

PowerPoint Pack
50+ Excel PM Templates

MS Word Pack
25+ Word PM Templates

Ultimate Project
Management Template
Ultimate Resource
Management Template
Project Portfolio
Management Templates
Categories: VBA StatementsTags: , Last Updated: September 28, 2023

Leave A Comment