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.

Table of Contents hide

Download Practice Workbook

Introduction to VBA Input Function

2 Examples to Use VBA Input Function in Excel

1. Input Each Character Using VBA Input Function

2. Input Each Line Using VBA Input Function

Conclusion

Download Practice Workbook

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

Use of Input Function in Visual Basic.xlsm

Introduction to VBA Input Function

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.

2 Examples to Use VBA Input Function in Excel

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. Input Each Character Using VBA Input Function

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.

Similar Readings

2. Input Each Line Using 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 on a message box.

MsgBox EachLine

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. You can also visit our ExcelDemy blog to explore more about excel. Stay with us and Keep learning.