Ranges, auto recalculation, establishing function dependence

garyae

New Member
Joined
Oct 29, 2002
Messages
15
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,
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Damon Ostrander

MrExcel MVP
Joined
Feb 17, 2002
Messages
4,239
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

New Member
Joined
Oct 29, 2002
Messages
15
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

MrExcel MVP
Joined
Feb 17, 2002
Messages
4,239
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
 

Forum statistics

Threads
1,144,307
Messages
5,723,627
Members
422,505
Latest member
Noar33

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top