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

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:

Input ( Number, [ # ] filenumber )

Arguments:

ARGUMENT	REQUIREMENT	EXPLANATION
Number	Required	It is the number of characters that you want to return. This argument must be a valid numeric expression.
[ # ] filenumber	Required	It is the number you must designate to the file that you want to return the characters from. This argument must be a valid file number.

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.

Example 1 – Inserting Each Character Using VBA Input Function in Excel

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

Enter the following code and paste it in 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

Press F5 to run the code.

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.

Example 2 – Inserting Each Line Using Excel VBA Input Function

Enter the following code and paste it in 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.

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