Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Test for Optional and Asign one Range to another
Below is a snipit of a function I am having trouble with... I am
tyring to write a function that gets passed 1 or 2 ranges. I cant seem to get the optional test to work right. Also how do I asign one range to another. Basicly if only one range is passed to the fuction I want to set the optional range equal to the one required range, Any help would be appreciated it. Thanks. Function temp(rng As Range, rngoffset As Range, Optional rngoffset As Variant) If IsEmpty(rngoffset) Is True Then Debug.Print "rngoffset empty" Set rngoffset = rng End If End Function |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Test for Optional and Asign one Range to another
rngOffset is declared twice as the function argument.
-- If this post helps click Yes --------------- Jacob Skaria "Michael" wrote: Below is a snipit of a function I am having trouble with... I am tyring to write a function that gets passed 1 or 2 ranges. I cant seem to get the optional test to work right. Also how do I asign one range to another. Basicly if only one range is passed to the fuction I want to set the optional range equal to the one required range, Any help would be appreciated it. Thanks. Function temp(rng As Range, rngoffset As Range, Optional rngoffset As Variant) If IsEmpty(rngoffset) Is True Then Debug.Print "rngoffset empty" Set rngoffset = rng End If End Function |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Test for Optional and Asign one Range to another
Something like the following should get you started:
Function Temp(R1 As Range, Optional ByVal R2 As Range = Nothing) As Variant If Not R1 Is Nothing Then Temp = CVErr(xlErrRef) ' required range was nothing Exit Function End If If R2 Is Nothing Then ' R2 as omitted. Set R2 = R1 Else ' R2 was passed End If Temp = some_result End Function Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Mon, 30 Mar 2009 05:11:54 -0700 (PDT), Michael wrote: Below is a snipit of a function I am having trouble with... I am tyring to write a function that gets passed 1 or 2 ranges. I cant seem to get the optional test to work right. Also how do I asign one range to another. Basicly if only one range is passed to the fuction I want to set the optional range equal to the one required range, Any help would be appreciated it. Thanks. Function temp(rng As Range, rngoffset As Range, Optional rngoffset As Variant) If IsEmpty(rngoffset) Is True Then Debug.Print "rngoffset empty" Set rngoffset = rng End If End Function |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Test for Optional and Asign one Range to another
Declare your Function (or Sub) this way...
Function temp(rng As Range, Optional rngoffset As Variant) and use this to test if the Optional argument was passed into the function (or Sub) or not.... If IsMissing(rngoffset) Then MsgBox "The Optional argument is missing" Else MsgBox "The Optional argument was passed" End If Note the use of IsMissing as the testing mechanism. -- Rick (MVP - Excel) "Michael" wrote in message ... Below is a snipit of a function I am having trouble with... I am tyring to write a function that gets passed 1 or 2 ranges. I cant seem to get the optional test to work right. Also how do I asign one range to another. Basicly if only one range is passed to the fuction I want to set the optional range equal to the one required range, Any help would be appreciated it. Thanks. Function temp(rng As Range, rngoffset As Range, Optional rngoffset As Variant) If IsEmpty(rngoffset) Is True Then Debug.Print "rngoffset empty" Set rngoffset = rng End If End Function |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
asign a number to a text string | Excel Discussion (Misc queries) | |||
asign the next 10th as the due date. | Excel Worksheet Functions | |||
HOW TO ASIGN SAME NAME TO TWO DIFFERENT RANGES IN SEPARAT SHEETS | Excel Discussion (Misc queries) | |||
Asign Array Variant to Column | Excel Programming |