VBA Input # Statement

VBA Input # statement allows the user to input data from a file into their program. The primary purpose of this statement is to read data from an external file and store it in variables for further use in the program.

Syntax of VBA ‘Input #’ Statement

What is the Input # Statement?

The Input # statement is used to read data from an open file in a sequential manner. Sequential files are typically text files where data is read from the start to the end, line by line. With Input # , you can easily fetch data from these files and store them into variables in your VBA code.

Syntax

The syntax of the ‘Input #’ statement is as follows:

 Input #filenumber, varlist

Where:

• #filenumber is the number assigned to the open file using the Open statement.
• varlist are the variables where you want to store the data read from the file.

How it Works:

When using the Input # statement, VBA reads the data from the file based on the variable type provided. For example, if the next variable is a String, VBA will read until it encounters a comma, a newline character, or the end of the file.

Points to Consider:

• Delimiter: By default, a comma is used as a delimiter when reading data. Ensure that the file you’re reading from is correctly formatted to avoid any misinterpretations.
• Error Handling: Always implement error handling. If VBA attempts to read beyond the end of the file or if the data type doesn’t match the variable, you’ll encounter an error.
• Close Files: Always remember to close the file after reading/writing using the Close statement.

Examples of VBA Input # Statement

Now let’s take a look at the top 5 examples of the ‘Input #’ statement in action.

Example 1: Reading data from a text file

In this example, we have a text file named “Data.txt” containing the following data:

John Doe
25
john.doe@email.com

We want to read this data and store it in variables Name, Age, and Email.

Sub ReadData()
    Dim FileNum as Integer
    Dim Name as String
    Dim Age as Integer
    Dim Email as String
    
    'Get file number
    FileNum = FreeFile()
    
    'Open file for reading
    Open "C:\\Data.txt" For Input As FileNum
    
    'Read data from file into variables
    Input #FileNum, Name
    Input #FileNum, Age
    Input #FileNum, Email
    
    'Close file
    Close FileNum
    
    'Display data
    MsgBox "Name: " & Name & vbNewLine & "Age: " & Age & vbNewLine & "Email: " & Email
End Sub

In this example, the ‘Input #’ statement is used to read data from the file into the variables Name, Age, and Email. The data is then displayed using a message box.

Example 2: Reading data from a CSV file

Similar to the previous example, we can use the ‘Input #’ statement to read data from a CSV (Comma Separated Values) file. Let’s say we have a CSV file named “Employees.csv” that contains the following data:

John Doe, 25, john.doe@email.com
Jane Smith, 30, jane.smith@email.com
Michael Johnson, 27, michael.johnson@email.com

We want to read this data and store it in a worksheet starting from cell A2.

Sub ReadData()
    Dim FileNum as Integer
    Dim Name as String
    Dim Age as Integer
    Dim Email as String
    Dim Row as Long
    
    'Get file number
    FileNum = FreeFile()
    
    'Open file for reading
    Open "C:\\Employees.csv" For Input As FileNum
    
    'Read data from file into variables
    Row = 2 'Start from row 2 in worksheet
    Do Until EOF(FileNum)
        Input #FileNum, Name, Age, Email
        
        'Write data to worksheet
        Cells(Row, 1).Value = Name
        Cells(Row, 2).Value = Age
        Cells(Row, 3).Value = Email
        
        Row = Row + 1 'Move to next row
    Loop
    
    'Close file
    Close FileNum
End Sub

Here, the ‘Input #’ statement is used inside a Do Until loop to read data from the file until the end of the file is reached. The data is then written to the worksheet starting from cell A2.

Example 3: Using Varlist to specify variables

As mentioned earlier, the ‘Input #’ statement allows us to specify the variables to store the data being read by using the [varlist] parameter. Let’s take a look at an example:

Sub ReadData()
    Dim FileNum as Integer
    Dim Name as String
    Dim Age as Integer
    Dim Email as String
    
    'Get file number
    FileNum = FreeFile()
    
    'Open file for reading
    Open "C:\\Data.txt" For Input As FileNum
    
    'Read data from file into variables
    Input #FileNum, Name, Age
    
    'Close file
    Close FileNum
    
    'Display data
    MsgBox "Name: " & Name & vbNewLine & "Age: " & Age
End Sub

In this example, the variable Email is not specified in the [varlist], so it will not be assigned any value from the file.

Example 4: Ignoring non-numeric data

The ‘Input #’ statement is primarily used to read numeric data from a file. If the data being read contains non-numeric characters, it will throw an error. To ignore non-numeric data, we can use the Input # statement within a On Error Resume Next block, as shown in the following example:

Sub ReadData()
    Dim FileNum as Integer
    Dim Age as Integer
    
    'Get file number
    FileNum = FreeFile()
    
    'Open file for reading
    Open "C:\\Data.txt" For Input As FileNum
    
    'Read age from file
    On Error Resume Next 'Ignore error
    Input #FileNum, Age
    On Error GoTo 0 'Resume normal error handling
    
    'Close file
    Close FileNum
    
    'Display age
    MsgBox "Age: " & Age
End Sub

In this example, if the data being read from the file is not a number, it will be ignored and the variable Age will not be assigned any value.

Example 5: Reading data into an array

Instead of storing the data from the file into separate variables, we can also use the ‘Input #’ statement to read the data directly into an array. Let’s take a look at an example:

Sub ReadData()
    Dim FileNum as Integer
    Dim Data(1 To 3) as String 'Array of size 3 for 3 lines in file
    
    'Get file number
    FileNum = FreeFile()
    
    'Open file for reading
    Open "C:\\Data.txt" For Input As FileNum
    
    'Read data from file into array
    Input #FileNum, Data
    
    'Close file
    Close FileNum
    
    'Display data
    MsgBox "Name: " & Data(1) & vbNewLine & "Age: " & Data(2) & vbNewLine & "Email: " & Data(3)
End Sub

In this example, the ‘Input #’ statement is used to read the first three lines of the file into the array Data. We can then access each element of the array to display the data.

Important Notes & Remarks

While the ‘Input #’ statement can be useful in reading data from files, there are a few things that should be kept in mind:

• The ‘Input #’ statement can only be used to read data from external files. It cannot be used to read data from the user.
• The ‘Input #’ statement can only read data that is formatted in a specific way. For example, it cannot read data that is in a different language or data that is not in a structured format.
• The data being read from the file must match the data type of the variable or array specified in the [varlist]. Otherwise, an error will occur.
• If the file being read does not exist or cannot be opened, an error will occur. It is important to handle error checking when using the ‘Input #’ statement.
• The ‘Input #’ statement will continue to read data from the file until it reaches the end of the file. It is important to know the structure of the file being read to ensure all the necessary data is being captured.

Conclusion

While there are more modern methods and technologies for reading files, the Input # statement in VBA remains a useful tool, especially when working with legacy systems or when needing to quickly parse simple text files. As always, understanding the nuances of the tools you’re using is key to ensuring accuracy and efficiency in your work.

Thank you for reading this post on the VBA ‘Input #’ statement. I hope it has provided you with a better understanding of its purpose, syntax, and examples. If you have any feedback or additional views on this topic, please feel free to share them in the comments section below. I would love to hear from you.