variant/error Error 2023

B

bz

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.

(e-mail address removed) remove ch100-5 to avoid spam trap
 
J

Jim Cone

' 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
------------
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"bz" <[email protected]>
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.
(e-mail address removed) remove ch100-5 to avoid spam trap
 
B

bz

' 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
Click to expand...


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" <[email protected]>
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?
Click to expand...





--
bz

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

(e-mail address removed) remove ch100-5 to avoid spam trap
 
T

Tom Ogilvy

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 said:
' 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
Click to expand...


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" <[email protected]>
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?
Click to expand...





--
bz

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

(e-mail address removed) remove ch100-5 to avoid spam trap
Click to expand...
 
C

Chip Pearson

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

ExecuteExcel4Macro 4
ExecuteExcel4Macro Error 2
GetValue from closed workbook 1
Closed workbook 1
Error retrieving data from closed workbook 5
Weird... type mismatch only half of the time 1
Retrieving cell formatting from closed workbook 0
Retrieving cell formatting from closed workbook 1

Top