Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
bz bz is offline
external usenet poster
 
Posts: 16
Default 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
  #2   Report Post  
Posted to microsoft.public.excel.programming
bz bz is offline
external usenet poster
 
Posts: 16
Default 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
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,290
Default 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

  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 27,285
Default 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



  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 7,247
Default 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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to solve Error 2023 for Excel? Eric Excel Worksheet Functions 0 July 1st 07 02:24 PM
Runtime error 13-- Setting a variant Range?! [email protected] Excel Programming 3 September 11th 06 07:03 PM
Error Handling - On Error GoTo doesn't trap error successfully David Excel Programming 9 February 16th 06 06:59 PM
Array problem: Key words-Variant Array, single-element, type mismatch error davidm Excel Programming 6 November 9th 05 06:54 AM
Array problem: Key words-Variant Array, single-element, type mismatch error davidm Excel Programming 1 November 8th 05 05:21 AM


All times are GMT +1. The time now is 11:03 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"