Debugging VBA

Question

Debugging VBA

Victor Leung 20

I am a beginner in VBA programming, I am writing a function to retrieve the cell value from another file. However, I made a lot of attempts but still cannot get it work. May I know where can I get assistance?

Barry Schwarz 3,836 Reputation points

2025-08-02T18:46:39.1833333+00:00

You can get assistance here. You need to provide details.

Are you writing a VBA function macro? If so, copy and paste it into a message here along with the results of executing it. Tell us how these results differ from what you want.
Victor Leung 20 Reputation points

2025-08-03T12:10:14.7266667+00:00
I am writing the below function to read the cell value from another workbooks, the filenames of workbooks are passed to the function as a variable, I am expecting it can return the cell value but it keeps returning #VALUE!

Function ReadClosedWorkbook(BoQName As String) As Variant Dim wb As Workbook Dim ws As Worksheet Dim filePath As String Dim result As Variant filePath = "C:\Users\VLeung\Desktop\VBA\" & BoQName & ".xlsx" Set wb = Workbooks.Open(fileName:=filePath, ReadOnly:=True) Set ws = wb.Sheets("Sheet1") result = ws.Range("A1").Value wb.Close SaveChanges:=False ReadClosedWorkbook = result End Function

I tired putting the following codes in a Sub, it can retrieve the value from cell A1 of sheet 1 of "BoQName".xlsx

filePath = "C:\Users\VLeung\Desktop\VBA" & BoQName & ".xlsx" Set wb = Workbooks.Open(fileName:=filePath, ReadOnly:=True) Set ws = wb.Sheets("Sheet1") result = ws.Range("A1").Value

However, when I put them into a Function, it sounds like not working.
Barry Schwarz 3,836 Reputation points

2025-08-03T22:02:17.0533333+00:00
The file path is not the same in the two modules you show. The function has an extra "" after the VBA. It's possible that this is just a type since it appears you did not cut and paste the sub code but it raises the question: did you include a "" at the beginning of the argument when you call the function?

Assuming this is not the issue, I recommend adding some debugging statements after each of you variables is assigned to:

debug.print filepath debug.print wb.name debug.print ws.name debug.print result debug.print readclosedworkbook

You will need to open the VBA editor (Alt-F11) to see the debug output..
Victor Leung 20 Reputation points

2025-08-04T17:06:41.0666667+00:00

I added an ErrorHandler and few Debug.Print statements. The first Debug.Print shows the variable "filePath" stores the correct path and file name. However, it doesn't pause at the first breakpoint but jumps directly to ErrorHandler.

I am wondering if the following statement is causing errors but it sounds like the syntax is correct.

Set wb = Workbooks.Open(fileName:=filePath, ReadOnly:=True)

I have also checked that Bill 1.xlsx is located on C:\Users\VLeung\Desktop\VBA. Would you see some more possibilities causing the error?

Accepted answer

0 additional answers

Your answer

Barry Schwarz 3,836 Reputation points

2025-08-02T18:46:39.1833333+00:00

You can get assistance here. You need to provide details.

Are you writing a VBA function macro? If so, copy and paste it into a message here along with the results of executing it. Tell us how these results differ from what you want.
Victor Leung 20 Reputation points

2025-08-03T12:10:14.7266667+00:00

I am writing the below function to read the cell value from another workbooks, the filenames of workbooks are passed to the function as a variable, I am expecting it can return the cell value but it keeps returning #VALUE!

Function ReadClosedWorkbook(BoQName As String) As Variant Dim wb As Workbook Dim ws As Worksheet Dim filePath As String Dim result As Variant filePath = "C:\Users\VLeung\Desktop\VBA\" & BoQName & ".xlsx" Set wb = Workbooks.Open(fileName:=filePath, ReadOnly:=True) Set ws = wb.Sheets("Sheet1") result = ws.Range("A1").Value wb.Close SaveChanges:=False ReadClosedWorkbook = result End Function

I tired putting the following codes in a Sub, it can retrieve the value from cell A1 of sheet 1 of "BoQName".xlsx

filePath = "C:\Users\VLeung\Desktop\VBA" & BoQName & ".xlsx" Set wb = Workbooks.Open(fileName:=filePath, ReadOnly:=True) Set ws = wb.Sheets("Sheet1") result = ws.Range("A1").Value

However, when I put them into a Function, it sounds like not working.
Barry Schwarz 3,836 Reputation points

2025-08-03T22:02:17.0533333+00:00

The file path is not the same in the two modules you show. The function has an extra "" after the VBA. It's possible that this is just a type since it appears you did not cut and paste the sub code but it raises the question: did you include a "" at the beginning of the argument when you call the function?

Assuming this is not the issue, I recommend adding some debugging statements after each of you variables is assigned to:

debug.print filepath debug.print wb.name debug.print ws.name debug.print result debug.print readclosedworkbook

You will need to open the VBA editor (Alt-F11) to see the debug output..
Victor Leung 20 Reputation points

2025-08-04T17:06:41.0666667+00:00

I added an ErrorHandler and few Debug.Print statements. The first Debug.Print shows the variable "filePath" stores the correct path and file name. However, it doesn't pause at the first breakpoint but jumps directly to ErrorHandler.

I am wondering if the following statement is causing errors but it sounds like the syntax is correct.

Set wb = Workbooks.Open(fileName:=filePath, ReadOnly:=True)

I have also checked that Bill 1.xlsx is located on C:\Users\VLeung\Desktop\VBA. Would you see some more possibilities causing the error?

Answer 1

Google AI produced this little gem:

Function Macro Limitations:

Direct Interaction: Function macros (UDFs - User Defined Functions) called directly from a worksheet cell are designed to return a value to that cell and generally have restrictions on interacting with the Excel application, such as opening new workbooks or manipulating other objects.

Solution: If the goal is to open a workbook and perform actions, consider using a Sub procedure instead of a Function. Sub procedures are designed for actions and can freely interact with the Excel application object model. If the function's result is needed in a cell, the Sub can update the cell after opening the workbook.

I was almost able to work around the problem by putting the Open call into a private Sub that the function could call (see below). Unfortunately, this only worked when I manually entered the function using F8 in the debugger. When I called the function from a cell in a worksheet, the Open still failed.

Function xyz() As Long
Dim wb As Workbook
Dim x As Long
Dim str As String
x = Workbooks.Count
MyOpen ("D:\Finance\auto.xls")
'Set wb = Workbooks(x + 1)
If wb Is Nothing Then
    Debug.Print "again"
    xyz = -1
Else
    Debug.Print wb.Name
    Debug.Print wb.Sheets("summary").Range("C275").Value
    xyz = 27
End If
End Function
Private Sub MyOpen(fname As String)
Dim wb As Workbook
Debug.Print fname
Set wb = Workbooks.Open(Filename:=fname, ReadOnly:=True)
If wb Is Nothing Then Debug.Print "oops!"
End Sub

However I was able to get it to work by creating a Sub that mimicked the action of a function (see below). Basically, you select the cell to contain the value and you then invoke the Sub (ALT-F8 or a shortcut key). The Sub manually stores the result in the selected cell.

Sub abc()
Dim Here As Range
Dim wb As Workbook
Set Here = Selection
Debug.Print Here.Address
Set wb = Workbooks.Open(Filename:="D:\Finance\auto.xls", ReadOnly:=True)
If wb Is Nothing Then
    Debug.Print "oops"
    Exit Sub
End If
Here.Value = wb.Sheets("summary").Range("C275").Value
Debug.Print Here.Value
End Sub

Finally, if other considerations dictate the use of a function, you could create a Sub to be run once at the beginning whose only job is to open the workbook. The function could then find the workbook by searching the Workbooks collection and extracting what it needs since the workbook is already open.

Victor Leung 20 Reputation points

2025-08-05T13:32:00.66+00:00

Thanks a lot.

Share via

Debugging VBA

0 additional answers

Your answer