How to Use VBA Input Function in Excel (2 Examples)

This article highlights how to use the VBA Input function in excel. We will use some relevant examples to illustrate the uses of this function. You can read and return characters from files opened in input or binary mode. Let’s follow the article to see a few examples explaining how to apply this function in Excel VBA.

Introduction to VBA Input Function in Excel

The Input function can only be used in Excel VBA as there is no such worksheet function in Excel.

Objective:
We can use the VBA Input function to return characters from any file opened in Input or Binary mode.

Syntax:

Arguments:

Return Value:

• String. It returns all characters including leading spaces, commas, linefeeds, carriage returns, and quotation marks.

Remarks:

• It is usually written with the Print or Put statements to write the data read by this function.
• You must open the files in Input or Binary mode to read data using this function.
• You should use LOF/LOC instead of EOF to read binary files with this function. Otherwise, it will generate an error.
• Some of the errors that may occur while using this function are: 6 – overflow; 52 – invalid file name or file number; 62 – reading exceeds EOF.
• The InputB function returns the number of bytes instead of characters.

Assume you have a text file named Dataset containing the following data.

We will use the VBA Input function to read and return characters from this file. Follow along with the examples below.

1. Inserting Each Character Using VBA Input Function in Excel

• First, press ALT + F11 to open the VB Editor. You can also open it from the Developer tab. Then, insert a new module by selecting Insert >> Module as shown below. This will open a new blank module.

• Next, copy the following code and paste it onto the newly created module.

Sub InputFunction()
Dim EachChar
Dim FilePath As String
FilePath = "D:\ExcelDemy\Dataset.txt"
Open FilePath For Input As #1
Do While Not EOF(1)
    EachChar = Input(1, #1)
    Debug.Print EachChar
Loop
Close #1
End Sub

• After that, press F5 to run the code. Finally, you will see the following result in the Immediate Window. Press CTRL + G if the window is not visible or open it from the View tab.

• You can change the Number argument to get more characters at a time. But this often leads to errors.

2. Inserting Each Line Using Excel VBA Input Function

Now we will see another example to apply the VBA Input function and return each line of characters at a time from the text file.

• First, copy the following code and paste it onto the existing code. You can insert another module and paste it there too. In that case, change the name of the subject to make it different from the earlier one.

Sub InputFunction()
Dim EachChar
Dim FilePath, EachLine As String
FilePath = "D:\ExcelDemy\Dataset.txt"
Open FilePath For Input As #1
Do While Not EOF(1)
    EachChar = Input(1, #1)
    EachLine = EachLine & EachChar
Loop
Debug.Print EachLine
Close #1
End Sub

• Then press F5 to run the code and see the following result.

• You can replace the Print statement with MsgBox to show the result in a message box.

MsgBox EachLine

Read More: How to Use VBA User Defined Function

Download Practice Workbook

You can download the practice workbook from the download button below.

Conclusion

Now you know how to use the VBA Input function in excel. Do you have any further queries or suggestions? Then please let us know in the comment section below. Stay with us and Keep learning.

Related Articles

• Excel VBA Function to Return Multiple Values
• How to Execute VBA Function Procedure in Excel
• How to Return a Value in VBA Function
• How to Make VBA Function with Arguments in Excel
• VBA Sub Vs Function in Excel
• How to Create and Use ColorFunction in Excel
• Difference Between Subroutine and Function in Excel VBA
• How to Create Custom Function in Excel VBA