Kawser Ahmed
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:
| 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.
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.
Read More: How to Use VBA Modules in Excel (8 Simple Ways)
Similar Readings
- 22 Macro Examples in Excel VBA
- How Different Is VBA from Other Programming Languages
- Learn Excel VBA Programming & Macros (Free Tutorial – Step by Step)
- 20 Practical Coding Tips to Master Excel VBA
- How to Write VBA Code in Excel (With Easy Steps)
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 on a message box.
MsgBox EachLine
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
- Types of VBA Macros in Excel (A Quick Guide)
- What You Can Do with VBA (6 Practical Uses)
- Introduction to VBA Features and Applications
- How to Put Comma After 2 Digits in Excel (9 Quick Methods)
- 6 Best Excel VBA Programming Books (For Beginners & Advanced Users)
- How to Open Workbook from Path Using Excel VBA (4 Examples)
