ExecuteExcel4Macro Function not working when referenced from a Cell in Excel

[GHolden]

Hi,

I have a function that returns data from cells in Excel workbooks using ExecuteExcel4Macro

Function GetCellData(cellRef As String)
Dim wbPath As String
Dim wbName As String
Dim wsName As String
Dim result As String

wbPath = "C:\TEMP\"
wbName = "[Values.xlsx]"
wsName = "Sheet1"

result = "'" & wbPath & wbName & wsName & "'!" & Range(cellRef).Address(True, True, xlR1C1)

GetCellData = ExecuteExcel4Macro(result)

End Function

This works fine if I call it from within vba code e.g.

Sub TEST()
MsgBox GetCellData("A1")
End Sub

However, if I use =GetCellData("A1") in a cell in the worksheet I just get #VALUE!

Other user defined functions calls in cells work fine.

Any ideas gratefully received!



There are two ways to write error-free programs; only the third one works.

 

[SkipVought]

hi,

Your function needs to be stored in a MODULE, and not in a Worksheet to ThisWorkbook object.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[GHolden]

Hi,

Thanks for the response, the function is in a module. There are other functions in the same module that work fine.

Regards,

There are two ways to write error-free programs; only the third one works.

 

[SkipVought]

Just curious, you are returning a cell value and not running a macro in this function.

There are more reliable ways for returning data from an external database, like using ADO, for instance.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[GHolden]

Hi, yes I am returning a cell value. I have used ADO for other projects, client has lots of spreadsheets with linked cells, if they move the sheets all the links break (the folder structure remains the same so the relative positions are always the same). The intention was to effectively make the paths dynamic using this method and passing the current directory + the relative directory to the function... if that makes sense (at the moment they do a massive find / replace to update the links).

I can't understand why the function works if I call it from VBA but not if I reference it from a cell.

There are two ways to write error-free programs; only the third one works.