Re: 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" <bz+mspep@xxxxxxxxxxxxxxxxxxxx> wrote in message
news:Xns98A2D6611ECF7WQAHBGMXSZHVspammote@xxxxxxxxxxxxxxxxx
"Jim Cone" <jim.coneXXX@xxxxxxxxxx> 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" <bz+mspep@xxxxxxxxxxxxxxxxxxxx>
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.

bz+mspep@xxxxxxxxxxxxxxxxxxxx remove ch100-5 to avoid spam trap


.

Relevant Pages

  • Re: variant/error Error 2023
    ... ' Execute an XLM macro ... GetValue = ExecuteExcel4Macro ... Unfortunitly, it didn't work, or I didn't know how to 'Execute an XLM macro.' ... IF I attempt to fetch the value from an _empty cell_, ...
    (microsoft.public.excel.programming)
  • Re: variant/error Error 2023
    ... ' Execute an XLM macro ... GetValue = ExecuteExcel4Macro ... means nothing to them, "type mismatch" ...
    (microsoft.public.excel.programming)
  • variant/error Error 2023
    ... ' Execute an XLM macro ... GetValue = ExecuteExcel4Macro ... to fetch values from cells in a closed excel workbook. ... IF I attempt to fetch the value from an _empty cell_, ...
    (microsoft.public.excel.programming)
  • Re: variant/error Error 2023
    ... ' call XL4 macro ... GetValue = ExecuteExcel4Macro ... returns a variant/error object containing ... How can I test the returned object, which should NORMALLY be a string, so ...
    (microsoft.public.excel.programming)