variant/error Error 2023
 

I am using the following code

' Create the argument
arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
Range(ref).Range("A1").Address(, , xlR1C1)
' Execute an XLM macro
GetValue = ExecuteExcel4Macro(arg)

to fetch values from cells in a closed excel workbook.

IF I attempt to fetch the value from an _empty cell_, ExecuteExcel4Macro
returns a variant/error object containing
Error 2023

How can I test the returned object, which should NORMALLY be a string, so
that I can ignore the error?

I want to do something like this:
if GetValue is typoe variant/error then
GetValue = ""
end if

but I can't figure out the correct syntax to do this.
When I try to do something like
On Error goto badvalue
goto good value
badvalue: on error resume next
GetValue = ""
Goodvalue: end subroutine

It either goes into an endless loop or the user sees an error message that
means nothing to them, "type mismatch"

How can I test the Type of GetValue and act upon it?

--
bz

please pardon my infinite ignorance, the set-of-things-I-do-not-know is an
infinite set.

remove ch100-5 to avoid spam trap

variant/error Error 2023
 

"Jim Cone" wrote in news:ugdQ14iJHHA.2312
@TK2MSFTNGP02.phx.gbl:


' Create the argument
arg = "'" & Path & "[" & file & "]" & Sheet & "'!" & _
Range(ref).Range("A1").Address(, , xlR1C1)

' Execute an XLM macro
On Error Resume Next
GetValue = ExecuteExcel4Macro(arg)
If Err.Number < 0 Then Exit Sub


Thanks for the info.
Unfortunitly, it didn't work, or I didn't know how to 'Execute an XLM macro.'

The code I am using is a 'Module' that is part of the VBA project that is
part of the excel workbook.

Err.Number is 0 even though GetValue has taken on the 'Varient/Error' 'Error
2023'.

Err is also zero, although it had a value AFTER excel presented me with the
"Run Time Error '13': type mismatch" that occurs when the calling routine
tries to use what it expects to be a string but is an Error messag.

Maybe I must execute a ExecuteExcel4Macro which will test for an empty cell
before I try to fetch a value from that cell. Not quite sure how to do that.

I keep thinking there HAS to be a way to test the value of GetValue for an
error varient but everyting I have tried has failed.

Thanks again for trying.

-bz-

------------
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"bz"
wrote in message
I am using the following code

' Create the argument
arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
Range(ref).Range("A1").Address(, , xlR1C1)
' Execute an XLM macro
GetValue = ExecuteExcel4Macro(arg)

to fetch values from cells in a closed excel workbook.

IF I attempt to fetch the value from an _empty cell_, ExecuteExcel4Macro
returns a variant/error object containing
Error 2023

How can I test the returned object, which should NORMALLY be a string, so
that I can ignore the error?

I want to do something like this:
if GetValue is typoe variant/error then
GetValue = ""
end if

but I can't figure out the correct syntax to do this.
When I try to do something like
On Error goto badvalue
goto good value
badvalue: on error resume next
GetValue = ""
Goodvalue: end subroutine

It either goes into an endless loop or the user sees an error message that
means nothing to them, "type mismatch"
How can I test the Type of GetValue and act upon it?





--
bz

please pardon my infinite ignorance, the set-of-things-I-do-not-know is an
infinite set.

remove ch100-5 to avoid spam trap

variant/error Error 2023
 

You probably ought to review the code source again...
http://www.j-walk.com/ss/excel/tips/tip82.htm
--
Jim Cone
San Francisco, USA
http://www.officeletter.com/blink/specialsort.html

variant/error Error 2023
 

You can test for a 2023 error with iserror

Sub TestGetValue()
Dim v as Variant
p = "c:\XLFiles\Budget"
f = "99Budget.xls"
s = "Sheet1"
a = "A1"
v = GetValue(p, f, s, a)
if iserror(v) then
msgbox "Returned an error"
else
msgbox v
end if
End Sub
-- Regards,Tom Ogilvy"bz" wrote in message
98.139...
"Jim Cone" wrote in news:ugdQ14iJHHA.2312
@TK2MSFTNGP02.phx.gbl:


' Create the argument
arg = "'" & Path & "[" & file & "]" & Sheet & "'!" & _
Range(ref).Range("A1").Address(, , xlR1C1)

' Execute an XLM macro
On Error Resume Next
GetValue = ExecuteExcel4Macro(arg)
If Err.Number < 0 Then Exit Sub


Thanks for the info.
Unfortunitly, it didn't work, or I didn't know how to 'Execute an XLM
macro.'

The code I am using is a 'Module' that is part of the VBA project that is
part of the excel workbook.

Err.Number is 0 even though GetValue has taken on the 'Varient/Error'
'Error
2023'.

Err is also zero, although it had a value AFTER excel presented me with
the
"Run Time Error '13': type mismatch" that occurs when the calling routine
tries to use what it expects to be a string but is an Error messag.

Maybe I must execute a ExecuteExcel4Macro which will test for an empty
cell
before I try to fetch a value from that cell. Not quite sure how to do
that.

I keep thinking there HAS to be a way to test the value of GetValue for an
error varient but everyting I have tried has failed.

Thanks again for trying.

-bz-

------------
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"bz"
wrote in message
I am using the following code

' Create the argument
arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
Range(ref).Range("A1").Address(, , xlR1C1)
' Execute an XLM macro
GetValue = ExecuteExcel4Macro(arg)

to fetch values from cells in a closed excel workbook.

IF I attempt to fetch the value from an _empty cell_, ExecuteExcel4Macro
returns a variant/error object containing
Error 2023

How can I test the returned object, which should NORMALLY be a string, so
that I can ignore the error?

I want to do something like this:
if GetValue is typoe variant/error then
GetValue = ""
end if

but I can't figure out the correct syntax to do this.
When I try to do something like
On Error goto badvalue
goto good value
badvalue: on error resume next
GetValue = ""
Goodvalue: end subroutine

It either goes into an endless loop or the user sees an error message
that
means nothing to them, "type mismatch"
How can I test the Type of GetValue and act upon it?





--
bz

please pardon my infinite ignorance, the set-of-things-I-do-not-know is an
infinite set.

remove ch100-5 to avoid spam trap

variant/error Error 2023
 

Try something like the following:

Dim GetVal As Variant
Dim Arg As String
Arg = "whatever"
''''''''''''''''''''
' call XL4 macro
'''''''''''''''''''''
GetVal = Application.ExecuteExcel4Macro(Arg)
If IsError(GetVal) Then
Select Case True
Case GetVal = CVErr(xlErrNA)
Debug.Print "Error #N/A"
Case GetVal = CVErr(xlErrDiv0)
Debug.Print "Error #DIV/0"
Case GetVal = CVErr(xlErrName)
Debug.Print "Error #NAME?"
Case GetVal = CVErr(xlErrNull)
Debug.Print "Error #NULL"
Case GetVal = CVErr(xlErrNum)
Debug.Print "Error #NUM"
Case GetVal = CVErr(xlErrRef)
Debug.Print "Error #REF"
Case GetVal = CVErr(xlErrNum)
Debug.Print "Err #NUM"
Case Else
Debug.Print "Unknown error value: " & CStr(GetVal)
End Select
End If


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)


"bz" wrote in message
98.139...
I am using the following code

' Create the argument
arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
Range(ref).Range("A1").Address(, , xlR1C1)
' Execute an XLM macro
GetValue = ExecuteExcel4Macro(arg)

to fetch values from cells in a closed excel workbook.

IF I attempt to fetch the value from an _empty cell_, ExecuteExcel4Macro
returns a variant/error object containing
Error 2023

How can I test the returned object, which should NORMALLY be a string, so
that I can ignore the error?

I want to do something like this:
if GetValue is typoe variant/error then
GetValue = ""
end if

but I can't figure out the correct syntax to do this.
When I try to do something like
On Error goto badvalue
goto good value
badvalue: on error resume next
GetValue = ""
Goodvalue: end subroutine

It either goes into an endless loop or the user sees an error message that
means nothing to them, "type mismatch"

How can I test the Type of GetValue and act upon it?

--
bz

please pardon my infinite ignorance, the set-of-things-I-do-not-know is an
infinite set.

remove ch100-5 to avoid spam trap