VBA Projects

Full Access with Source Code
  • Designed and Developed by PNRao

  • Full Access with VBA Source Code

  • Well Commented Codes Lines

  • Creative and Professional Design

Manage Your Projects

120+ Project Management Templates

Seamlessly manage your projects with our powerful & multi-purpose templates for project management.

120+ PM Templates Includes:
  • 50+ Excel Templates

  • 50+ PowerPoint Templates

  • 25+ Word Templates

Share Post

When dealing with file operations, it’s crucial to know where you stand, quite literally. The position within a file, especially during read or write operations, can dictate the flow of data and the outcome of these operations. Enter the Loc function, a tool that provides insights into your current position within an open file. In this blog post, we’ll explore the intricacies of the VBA Loc function, its purpose, syntax, and more.

VBA Loc Function – Purpose, Syntax and Arguments

Purpose of the Loc Function

The Loc function in VBA is designed to return the current read/write position within an open file. This position is essentially a pointer that indicates where the next read or write operation will start.

Syntax and Arguments

The syntax for the Loc function is simple:

position = Loc(filenumber)


  • filenumber: This is a required argument. It’s the file number used when the file was opened with the `Open` statement.
  • position: This is the returned value, a `Long` data type, indicating the current byte position in the file. The first byte in the file is at position 1.

3. Remarks and Notes

  • Position Indicator: The value returned by the Loc function is essentially a position indicator. It tells you where you are in the file and where the next read or write operation will commence.
  • Binary vs. Text Files: The Loc function is especially useful when working with binary files where data is stored in a structured format and you need to access specific positions. However, it can also be used with text files.
  • End of File: When used with the `LOF` function (which returns the length of a file), the Loc function can help determine if you’ve reached the end of a file, as demonstrated in the provided example.

The VBA Loc function is a powerful tool when working with files, offering clarity on your current position within a file. Whether you’re reading data, writing data, or performing other file manipulations, understanding your position is crucial. By mastering the Loc function, you equip yourself with a valuable tool in your VBA toolkit, ensuring efficient and accurate file operations.

Walking Through the Examples

The Loc function in VBA is a versatile tool, especially when dealing with file operations. Let’s explore its capabilities through five illustrative examples:

Example 1: Reading a File Line by Line

In this example, we’ll read a text file line by line, printing each line and its starting position.

Dim MyLocation As Long
Dim MyLine As String
Open "TESTFILE.txt" For Input As #1
Do Until EOF(1)
    MyLocation = Loc(1)
    Line Input #1, MyLine
    Debug.Print "Position: " & MyLocation & "; Line: " & MyLine
Close #1

We open “TESTFILE.txt” for input. Before reading each line, we capture the current position using Loc. After reading the line, we print both the position and the line content.

Example 2: Appending Data to a Binary File

Here, we’ll append data to a binary file and demonstrate how Loc reflects the position changes.

Dim MyLocation As Long
Open "TESTFILE.dat" For Binary As #1
MyLocation = Loc(1)
Debug.Print "Initial Position: " & MyLocation

' Move to the end of the file
Seek #1, LOF(1) + 1
Put #1, , "Appended Data"
MyLocation = Loc(1)
Debug.Print "After Appending: " & MyLocation

Close #1

We open a binary file and capture the initial position. After appending data, we see how the position has changed, reflecting the new end of the file.

Example 3: Random Access Read

We’ll read specific records from a file with fixed-length records, using Loc to navigate.

Dim MyLocation As Long
Dim RecordLength As Integer
Dim MyData As String

RecordLength = 10 ' Assume each record is 10 bytes
Open "TESTFILE.dat" For Binary As #1

' Read the third record
Seek #1, (2 * RecordLength) + 1
Get #1, , MyData
MyLocation = Loc(1)
Debug.Print "Data: " & MyData & "; Position: " & MyLocation

Close #1

We calculate the position of the third record and use `Seek` to navigate there. After reading the record, we print the data and the position.

Example 4: Checking for End of File

Using Loc and `LOF`, we’ll check if we’ve reached the end of a file.

Dim MyLocation As Long
Open "TESTFILE.txt" For Input As #1
    MyLine = Input(1, #1)
    MyLocation = Loc(1)
    If MyLocation >= LOF(1) Then Exit Do
Debug.Print "Reached end of file at position: " & MyLocation
Close #1

We read the file character by character. If the position from Loc matches or exceeds the file length from `LOF`, we’ve reached the end and exit the loop.

Example 5: Modifying Data at a Specific Position

We’ll navigate to a specific position in a binary file, modify the data, and then check our position.

Dim MyLocation As Long
Open "TESTFILE.dat" For Binary As #1

' Navigate to 5th byte
Seek #1, 5
Put #1, , "NewData"
MyLocation = Loc(1)
Debug.Print "After Modification, Position: " & MyLocation

Close #1

We navigate to the 5th byte of the file and overwrite the data. After the modification, we check and print our position using Loc.

These examples showcase the versatility of the Loc function in VBA. When combined with other file operations, it allows for precise control over read and write operations, making it an essential tool for anyone working with file data in VBA.


The VBA Loc function, while seemingly simple, is a cornerstone for those looking to achieve precision in file operations. Its ability to pinpoint the current position within a file is invaluable, especially when navigating complex data structures or ensuring data integrity during read and write operations.

As we’ve seen through various examples, the Loc function, when paired with other VBA file handling capabilities, offers a robust toolkit for developers. Whether you’re a seasoned VBA programmer or just starting your journey, understanding and effectively utilizing the Loc function can significantly elevate the efficiency and accuracy of your file-based operations. As always, while automation and programmability offer immense power, they also come with the responsibility to ensure data safety and system stability. Happy coding!

Effortlessly Manage Your Projects and Resources
120+ Professional Project Management Templates!

A Powerful & Multi-purpose Templates for project management. Now seamlessly manage your projects, tasks, meetings, presentations, teams, customers, stakeholders and time. This page describes all the amazing new features and options that come with our premium templates.

Project Management Templates

All-in-One Pack
120+ Project Management Templates

Essential Pack
50+ PM Templates

Excel Pack
50+ Excel PM Templates

PowerPoint Pack
50+ Excel PM Templates

MS Word Pack
25+ Word PM Templates

Ultimate Project
Management Template
Ultimate Resource
Management Template
Project Portfolio
Management Templates
Categories: VBA FunctionsTags: , , , Last Updated: October 1, 2023

Leave A Comment