Allowing Column Ranges in UDF

[Nirmal Singh]

I have a User Defined Function which takes a Range as an argument.

It processes the range as follows

For RowNumber = 1 To DateRange.Rows.Count
DateFrom = DateRange.Cells(RowNumber, 1).Value
DateTo = DateRange.Cells(RowNumber, 2).Value
......(Further Processing)
Next RowNumber

This works fine for a range such as "C2350".

How can I cater for the user entering a column range such as "C"? In this
case I would want to loop down column C while it had valid data.

Nirmal

 

[Dave Peterson]

I'm not sure what valid data is, but maybe you could just use the usedrange:

function myfunc(rng as range) as Variant

dim myRng as range
with rng
set myrng = nothing
on error resume next
'used range and just the first area???
set myrng = intersect(.parent.usedrange,.areas(1))
on error goto 0
end with

if myrng is nothing then
myfunc = "invalid range"
exit function
end if

'just two columns?
set myrng = myrng.columns(1).resize(,2)

'and now work against myrng

....
End function