If I change the position of a cell in my model, will the VBA macros be updated?

excelos

Well-known Member
Joined
Sep 25, 2011
Messages
591
Office Version
  1. 365
Platform
  1. Windows
Hello

When I drag and drop a cell in an excel model, all the formulas in the workbook are updated to reflect the relevant reference.

Does this also happen in VBA macros and if not, is it possible to achieve that?

Thanks!
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Edit: better answer by JLGWhiz below
 
Last edited:
Upvote 0
The short answer is No. VBA is not part of the Excel application. VBA interfaces with Excel and other Microsoft products, but it is its own application. I suppose that with some elaborate code you could update the VBA code when you make additions, deleltions or relocations in Excel, but the general concept is that you only write VBA code for the existing conditions and any that can be predicted to change as a matter of proicess. But VBA is not designed to accomodate erratic changes and sporatic changes in worksheet data structure.
 
Upvote 0
If you give your cells a Defined Name and only use that Defined Name when referring to those cells, VBA will automatically reference the correct cells no matter where you move them. For example, give cell C5 the Defined Name "Rick" and give cell F9 the Defined Name "excelos". Now put values into those two cells (use numbers for now, but you can put anything into them... including formuals). Put this in the Immediate Window and hit the Enter Key...

? Range("Rick").Value * Range("excelos").Value

and note the answer. Now go to the worksheet and move those two cells to different locations. Go back to the Immediate Window and execute the above code line again. Same answer.

Note... there is a shortcut notation you can use to reference the value in cells with Defined Names. This line of code in the Immediate Window will produce the identical result as the one I gave above...

? [Rick] * [excelos]

And, for something simple like this multiplication, you could even do it this way...

? [Rick*excelos]

The point... VBA can automatically track ranges that have been given Defined Names.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,309
Members
448,564
Latest member
ED38

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
Back
Top