Position independent code

[cagdas.ozgenc]

Greetings.

I wrote a few event handlers that do calculations when some cells are
modified.
For example:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$6" Then
Application.EnableEvents = False
Range("B6").Value = Range("C6").Value / Range("B13").Value
Application.EnableEvents = True
Else
Application.EnableEvents = False
Range("C6").Value = Range("B6").Value * Range("B13").Value
Application.EnableEvents = True
End If
End Sub

But then I insert rows to my sheets add/remove/reposition some stuff
and all my VBA functions fail since Cell Addresses are hard-coded.

How can I reference cells by name, or some sort of a dynamic reference
(like Excel formulas that get automagically updated when you move
things around)?

Thanks

 

[Gary Keramidas]

give those cells a range name and use that instead of the address

 

[cagdas.ozgenc]

Please give an example, what is a range name?

Thank you

 

[Bob Phillips]

A range name is what it says on the box, a range that has been given a name
(handle) that can be used in formulae, and/or VBA.

To create a name,
select the range in question
goto Insert>Name>Define...
add the name, say myRange, to the Names in Workbook (overtyping anything
pre-loaded)
the RefersTo box will alraedy be loaded with the range address
OK out

The code would then be, assuming that if C6 becomes C7, then B6 becomes B7,
B13 becomes B14, etc.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("myRange")) Is Nothing Then
Application.EnableEvents = False
Target.Offset(0,-1).Value = Target.Value / Target.Offset(7,-1).Value
Application.EnableEvents = True
Else
Application.EnableEvents = False
Target.Value = Target.Offset(0,-1).Value * Target.Offset(7,-1).Value
Application.EnableEvents = True
End If
End Sub
--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Gary Keramidas]

in place of $C$6, create a range name. you can select the cell and either,
1 type a name into the address box next to the formula bar (it will show C6) and
press enter, or
2. click insert/name/define and type a name and press add
give it a unique name like Grand_Total



then in your code use Range("Grand_Total") instead of Range($C$6)

do this for the cells that will move