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

Get FREE Advanced Excel Exercises with Solutions!

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:

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.

Read More: What Are Excel Function Arguments (A Detailed Discussion)


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

txt dataset to read from

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.

Insert new 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

code module with VBA Input function

  • 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.

Application of the Input function

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

Input function reading multiple characters

Read More: How to Use VBA Modules in Excel (8 Simple Ways)


Similar Readings


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.

Read lines with Input function

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

MsgBox

Read More: How to Use Select Case Statement in Excel VBA (2 Examples)


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


Related Articles

Tags:

Kawser Ahmed
Kawser Ahmed

Hello! Welcome to my Excel blog! It took me some time to be a fan of Excel. But now I am a die-hard fan of MS Excel. I learn new ways of doing things with Excel and share them here. Not only a how-to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned! You can check out my courses at Udemy: udemy.com/user/exceldemy/

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo