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".

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

Jim Cone
San Francisco, USA


"deepunderground" wrote in
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".

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

"deepunderground" wrote in
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".