Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to solve Error 2023 for Excel? | Excel Worksheet Functions | |||
Runtime error 13-- Setting a variant Range?! | Excel Programming | |||
Error Handling - On Error GoTo doesn't trap error successfully | Excel Programming | |||
Array problem: Key words-Variant Array, single-element, type mismatch error | Excel Programming | |||
Array problem: Key words-Variant Array, single-element, type mismatch error | Excel Programming |