Ranges, auto recalculation, establishing function dependence

[garyae]

I have a function that takes in a number(DesValue) and a range of cells (MyRange). It then subtracts the value in the first, third, fifth, . . . nth cell from DesValue and returns it.

Here is the complete code:

Public Function bb(DesValue As Integer, MyRange As String) As Integer
bb = DesValue
Skip = 0
Dim InRange As Range
Set InRange = ActiveSheet.Range(MyRange)
For Each MyCell In InRange.Cells
If Skip = 0 Then
bb = bb - MyCell
Skip = 1
Else
Skip = 0
End If
Next MyCell

End Function

This function works fine with 2 exceptions.

1) No matter what I have tried when the function is called from excel the range must be in quotes. e.g. bb(E3,"F3:H3"). This is more of a hastle than anything. I must enter in this formula many times and typing in the quotes is a pain.

2) When I change the value of a cell in "MyRange" the formula does not automatically update (or recalculate) the new value. However if I change the value of "DesValue" it does automatically recalculate. I am pretty sure this is related to the first problem. I assume this happen because the function does not know to recalculate when a "MyRange" Value is changed because as far as the function is concerned the value that it returns is not dependent upon the values of "MyRange".

I think there might be two ways to fix these problems.

1) Read in MyRange as a Range instead of as a string.

2) Make the function aware that its answer is dependent of the value of the cells in "MyRange"

I have tried #1 several ways (read directly in as Range, read in as an array, etc.) but I am either doing it wrong or it is just not possible (probably the former .

I can not figure out how to do #2 at all.

Thanks in advance for your help,

 

[Damon Ostrander]

Hi Gary,

The reason for the problem is that your function declares MyRange as String, and since you pass the string as a literal string value that doesn't change when the range changes, there is no reason for the function to recalculate. Instead, pass MyRange As Range (i.e., a Range object) and eliminate

Set InRange = ActiveSheet.Range(MyRange)

and just use MyRange directly as in

For Each MyCell In MyRange

This should solve both problems.

 

[garyae]

It worked thanks a bunch! I tried something like that but I had:
for each MyCell in MyRange.cell

I think that did not work because it is implicit that a Range object contains cells so you do not have to specify .cell.

Is that right?

Thanks again

 

[Damon Ostrander]

Hi Gary,

Yes, MyRange is essentially a collection of cells, and For Each MyCell in MyRange loops through all the objects in this collection. There is no Cell property so MyRange.Cell is not a valid object, but MyRange.Cells is, and this should work as well since the Cells property yields all the cells in the range. But of course it is redundant. What ever you use for Y in "For Each X in Y" must be a collection of objects of which X is a member. In your case both X and Y are Range objects, but the same principle holds for worksheets, charts, shapes, controls, etc.

Damon

 

[garyae]

Hey Damon thanks for the tutorial and everything else!

Gary