View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
ker_01 ker_01 is offline
external usenet poster
 
Posts: 395
Default Passing single-cell vs multi-cell ranges to a variant array?

I have a function that pulls in a range [e.g. Function XYZ (TempRange as
range)]

I then assign that range to a class module's variant array:
abc.SourceDataRange = TempRange.Value

Then I perform various calculations on SourceDataRange within that class
module. I'm testing a few unlikely (but still potential) scenarios, and I
found a problem that I don't know how to fix.

Under almost all cases, more than one cell will be selected. My later code
loops through the values using references like SourceDataRange(i,1).

However, when a single cell is passed through the function,
abc.SourceDataRange = TempRange.Value
returns that single cell's value, instead of an array- so my class module
code can't process it when it hits the first line coded with
SourceDataRange(i,1), because SourceDataRange is a single value instead of an
array.

I can determine when the reference is a single cell in the function, using
[TempRange.Cells.Count], so I tried the following to try to force the single
value into an array format for my class module:
If TempRange.Cells.Count 1 Then
abc.SourceDataRange = TempRange.Value
Else
abc.SourceDataRange = Array(TempRange.Value)
End If

Which pushes the value over into SourceDataRange(0)... so it is in an array,
but still not to the point where I can reference it as as
SourceDataRange(1,1).

Any ideas on syntax to pass the single value over so that it will work the
same as if a 2+ cell range had been selected?

Thank you!
Keith