UDF + range object and Offset formula to Value problem

[Post Tenebras Lux]

I haven't encountered this one before:

I have just build a UDF which counts cells according to certain criteria
(for other reasons, I want to keep it as a UDF and not put the whole formula
into a cell - which I know how to do.)

Here is the relevant code:
Function DoTheCount(rng as range) as integer

Dim iCol As Integer
Dim aData As Variant

iCol = CInt(rCol.Text)
'rCol is a range object brought in as a parameter. rCol refers to a cell
that itself
'contains a formula =OFFSET(F$91,$R15,0)
' which yields a value, e.g. 37 (which is the column number I want)

Set ws = Worksheets(sht)
'sht is the worksheet I want, e.g. "sheet1"

With ws.Range("A10")
Set rng = Range(.Offset(0, iCol - 1), .Offset(nRows - 1, iCol - 1))
End With

varrayData = rng

'other stuff happens here

DoTheCount = ianswer

End Function
I have two problems:
1. If I try to use rCol.Value (which refers to a cell with an offset
formula), I get '0'
The only way to get the value I want is to use rCol.Text, which will give
me the correct value '37" which I convert to an integer.

2. rng receives the literal values of the range. The first three cells of
the range contain formula (again of the offset(...) kind). Even though they
show a value in the cell, the rng object shows those first three cells as the
literal formula:

'=OFFSET(Std_0!U$5,MATCH($C4,Std_0!$C$6:$C$2912,0),0)'

note - the ' ' are added here only to show the result, they are not in
the formula.
the rest of the range has only values in, and these show up correctly in
rng.value or when I dump the whole range into a variant array.

How do I convert the formula in the first three rows of the range to values?
Is there any way I can do it using the straight assignment

varrayData = rng

or do I have to iterate thru the rng cells to extract every value?

Thanks.

 

[Dave Peterson]

rng is the parm that you're passing--not rCol.

I haven't encountered this one before:

I have just build a UDF which counts cells according to certain criteria
(for other reasons, I want to keep it as a UDF and not put the whole formula
into a cell - which I know how to do.)

Here is the relevant code:
Function DoTheCount(rng as range) as integer

Dim iCol As Integer
Dim aData As Variant

iCol = CInt(rCol.Text)
'rCol is a range object brought in as a parameter. rCol refers to a cell
that itself
'contains a formula =OFFSET(F$91,$R15,0)
' which yields a value, e.g. 37 (which is the column number I want)

Set ws = Worksheets(sht)
'sht is the worksheet I want, e.g. "sheet1"

With ws.Range("A10")
Set rng = Range(.Offset(0, iCol - 1), .Offset(nRows - 1, iCol - 1))
End With

varrayData = rng

'other stuff happens here

DoTheCount = ianswer

End Function
I have two problems:
1. If I try to use rCol.Value (which refers to a cell with an offset
formula), I get '0'
The only way to get the value I want is to use rCol.Text, which will give
me the correct value '37" which I convert to an integer.

2. rng receives the literal values of the range. The first three cells of
the range contain formula (again of the offset(...) kind). Even though they
show a value in the cell, the rng object shows those first three cells as the
literal formula:

'=OFFSET(Std_0!U$5,MATCH($C4,Std_0!$C$6:$C$2912,0),0)'

note - the ' ' are added here only to show the result, they are not in
the formula.
the rest of the range has only values in, and these show up correctly in
rng.value or when I dump the whole range into a variant array.

How do I convert the formula in the first three rows of the range to values?
Is there any way I can do it using the straight assignment

varrayData = rng

or do I have to iterate thru the rng cells to extract every value?

Thanks.

 

[Post Tenebras Lux]

Sorry, I made a mistake in how I wrote out the problem. rCol is the range
param that is passed into the UDF definition. It should have read: Function
DoTheCount(rCol as range) as integer

rng should have been shown as Dim'ed as range object in the function code.
All the other parts of the code are correct.

I still have the problem. Any thoughts?

Thanks.

 

[Dave Peterson]

So your function looks more like:

Option Explicit

Function DoTheCount(rCol As Range) As Integer

Dim iCol As Integer
Dim aData As Variant

iCol = CInt(rCol.Text)
Set ws = Worksheets(sht)

With ws.Range("A10")
'added a dot here so that the range refered to ws.range("a10")
Set rng = .Range(.Offset(0, iCol - 1), .Offset(nRows - 1, iCol - 1))
End With

varrayData = rng

'other stuff happens here

DoTheCount = ianswer

End Function

But there's lots of stuff that happens elsewhere--what's sht, what's rng, what's
varraydata?

And for functions to calculate right away, they have to know what they depend
on.

When you write UDFs, the way you tell them what they depend on is by passing
those ranges in the function. Otherwise, they won't recalc when something
important changes.

You can also use:

Function DoTheCount(rCol As Range) As Integer
Application.volatile
....

But that only means that the function will recalc when excel recalcs. You could
be looking at "old" values--you could be a recalc behind.

 

[Post Tenebras Lux]

sht = "sheet1" (the name of the sheet with the cell or range to be referred
to by setting the rng (range object), which refers to a single column range
of about 1500 cells. It is set using the

ws = worksheets("sheet1") ' sheet1 contains the range of cells that rng
will refer to.
iCol = 37
nRows = 1500

With ws.Range("A10")
Set rng = .Range(.Offset(0, iCol - 1), .Offset(nRows - 1, iCol - 1))
End With


varrayData is a variant array variable created to store the data for more
efficient manipulation while the UDF is running.

The function works fine EXCEPT for those worksheet cells referred to by the
range object (rng), that contain the offset formula (rather than values).
The cell offset formula (e.g. =offset(A10, 0, 5) does not return a value in
the UDF (but does return a value in the worksheet cell).

In the UDF, rng.cell(1,1).value returns the following "=offset(A10, 0, 5)"
rather than the value of the cell referred to by the offset formula (which
might be 12 or any other number). Where rng.cell(10,1) refers to a value in
the cell, then rng.cell(10,1).value returns the value of that cell.

If you have any suggestions about why this happens (lets assume that the
rest of the function is written perfectly), I'd really appreciate some
guidance on how to resolve this.

thanks.

 

[Dave Peterson]

I don't really have any guesses.

I'd start by putting either some debug.print or msgbox's in the code so that I
could see that things pointed to what I wanted.


msgbox rng.address(external:=true)
....
even stuff like:
msgbox nRows
msgbox iCol
msgbox ws.Range("A10").Offset(0, iCol - 1).address(external:=true)
msgbox ws.Range("A10").Offset(nRows - 1, iCol - 1).address(external:=true)

sht = "sheet1" (the name of the sheet with the cell or range to be referred
to by setting the rng (range object), which refers to a single column range
of about 1500 cells. It is set using the

ws = worksheets("sheet1") ' sheet1 contains the range of cells that rng
will refer to.
iCol = 37
nRows = 1500

With ws.Range("A10")
Set rng = .Range(.Offset(0, iCol - 1), .Offset(nRows - 1, iCol - 1))
End With

varrayData is a variant array variable created to store the data for more
efficient manipulation while the UDF is running.

The function works fine EXCEPT for those worksheet cells referred to by the
range object (rng), that contain the offset formula (rather than values).
The cell offset formula (e.g. =offset(A10, 0, 5) does not return a value in
the UDF (but does return a value in the worksheet cell).

In the UDF, rng.cell(1,1).value returns the following "=offset(A10, 0, 5)"
rather than the value of the cell referred to by the offset formula (which
might be 12 or any other number). Where rng.cell(10,1) refers to a value in
the cell, then rng.cell(10,1).value returns the value of that cell.

If you have any suggestions about why this happens (lets assume that the
rest of the function is written perfectly), I'd really appreciate some
guidance on how to resolve this.

thanks.