ExecuteExcel4Macro Problem

[deepunderground]

I have the following user defined function:

Function mytest2(ByVal Exposure As String, ByVal i As Integer)

msgbox "Step1"

mytest2 = Application.ExecuteExcel4Macro("Hlookup(""" & Exposure & """,
'C:\Reference Tables\[ASCE 7-02.xls]TABLE 1609.6.2.1(4)'!R3C2:R13C4, """ & 3
+ i & """, False)")

msgbox "Step2"

End Function


If I were to assign variables to Exposre and i, and then step through the
lines of code I get the value 1.6 for mytest2 - which is correct. The problem
i'm having is when I inset the function into the excel spreadsheet I get the
#Value error. Does anyone know why this is occuring? Is there some kind of
problem using ExecuteExcel4Macro in a user defined function. Also, If I were
to step through the lines of code I get both message boxes "Step1" and
"Step2". When I insert the user defined function into the spreadsheet, I get
the message box "Step1" but it never reaches "Step2".

 

[Jim Cone]

In XL4 the HLookup function has 3 arguments not four.
They are: lookup_value, table_array, row_index_num.
Also, if row_index_num is less than 1 , HLookup returns #Value.

Jim Cone
San Francisco, USA


message I have the following user defined function:

Function mytest2(ByVal Exposure As String, ByVal i As Integer)
msgbox "Step1"
mytest2 = Application.ExecuteExcel4Macro("Hlookup(""" & Exposure & """,
'C:\Reference Tables\[ASCE 7-02.xls]TABLE 1609.6.2.1(4)'!R3C2:R13C4, """ & 3
+ i & """, False)")
msgbox "Step2"
End Function

If I were to assign variables to Exposre and i, and then step through the
lines of code I get the value 1.6 for mytest2 - which is correct. The problem
i'm having is when I inset the function into the excel spreadsheet I get the
#Value error. Does anyone know why this is occuring? Is there some kind of
problem using ExecuteExcel4Macro in a user defined function. Also, If I were
to step through the lines of code I get both message boxes "Step1" and
"Step2". When I insert the user defined function into the spreadsheet, I get
the message box "Step1" but it never reaches "Step2".

 

[Niek Otten]

It could well be that ExecuteExcel4Macro is forbidden in UDFs, like many
other functions are.
If there is any chance that a function tries to change something in a
worksheet, it will cause Excel to return #VALUE.
Even a simple FIND will do this, because there is an option to replace.

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel