Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determine if variable contains a valid range reference?
I'm trying to create a function (UDF) that will allow input two ranges; one
is required, the other is optional. See sample code below. If the required range isn't included, the function returns an error, which is fine. However, when only the optional range is omitted, my code blows up (without an error) when I try to process the returned value. I've done some searching, but haven't found a way to easily/cleanly determine if the variable returned by the function is a valid range (so that I can conditionally process those values). Can anyone point me in the right direction? Many thanks! Keith much-simplified sample below (with two problems); one is that when there is no MyOptionalRange entered, the code never reaches the msgbox (I'd at least have expected an error). The other problem is that when MyOptionalRange /is/ included and the values are being entered via the formula entry pop-up (and not entered by hand directly in the cell), the code seems to loop more than once (msgbox keeps popping up) and I'm not sure why or how to ensure that it only processes once, when the formula box is closed. Function ThisIsATest(MyRequiredRange As Range, MyOptionalRange As Range) Dim MyRequiredVariantArray As Variant MyRequiredVariantArray = MyRequiredRange.Value Debug.Print MyRequiredVariantArray(1, 1) Dim MyOptionalVariantArray As Variant MyOptionalVariantArray = MyOptionalRange.Value Debug.Print MyOptionalVariantArray(1, 1) MsgBox "All Done" End Function |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determine if variable contains a valid range reference?
If the range is optional then you need to include Optional in the Function so
that it does not return an error. You then test for its value in the code. Function ThisIsATest(MyRequiredRange As Range, _ Optional MyOptionalRange As Range) If MyOptionalRange Is Nothing Then 'code for optional range not included MsgBox "MyOptionalRange is Nothing" Else 'code if included MsgBox "MyOptionalRange is: " _ & MyOptionalRange.Address End If 'Alternatively If Not MyOptionalRange Is Nothing Then 'code here becaue it is included MsgBox "MyOptionalRange is: " _ & MyOptionalRange.Address Else 'code for optional range not included MsgBox "MyOptionalRange is Nothing" End If -- Regards, OssieMac |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
The name ... either conflicts with a valid range reference or isinvalid for Excel. | Excel Programming | |||
Conflict with Valid Range Reference Error in Microsoft Excel 2007 | Excel Programming | |||
Conflict with valid range reference error | Excel Discussion (Misc queries) | |||
Input cell reference is not valid (One Variable Data Table) | Excel Worksheet Functions | |||
Determine Valid Sample Size from a Population - Any UDFs or Formulas? | Excel Programming |