Question on importance of releasing variables..

largeselection

Active Member
Joined
Aug 4, 2008
Messages
353
My question is really how important is this? I am a real newbie so I haven't really done this before. I recently created a fairly complex (for me) workbook. And it has many (maybe 30) public variables which are used and changed at various points in the code. Recently this workbook has been behaving badly.

An example: I have some cells which are pointing to a cell in a pivot table. There is a named range based on those cells. The named range values are passed to a userform to be displayed on the form. After I run my code if I change the pivot table I notice that the cells referencing the table don't update. So I check the formula only to find out that those cells which used to be =a11 now just have a hard coded value as though I had pasted values. Which means this same value passes to the named range etc.

Essentially excel is converting a few cells from formulas to values. There is nothing in the code which does anything like this or even writes back to this sheet. The only connection between these cells and the code is the named range.

So I think something must be breaking and after much research think it might be related to not releasing variables?
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
It's generally better to avoid the use of public variables unless there is a compelling benefit in their use.

That being said, the symptom you describe of formulas being converted to values is probably not due to the use of public variables.

One scenario that could cause that to happen would be through the use of the ControlSource links between in any of your UserForm controls and any worksheet cell(s) with formulas.

This thread included a good discussion of the problems that can create...
http://www.mrexcel.com/forum/excel-...ly-=true-deleting-formulas-2.html#post2974413

It's a long thread, but you can just start at the linked Post 55 by ZVI to read the part that might be applicable to the problem you describe.
 

largeselection

Active Member
Joined
Aug 4, 2008
Messages
353
Awesome reference thank you! I am indeed using the control source referencing a named range which is referencing a cell so it seems like that is the issue. I will change to populate the textboxes with the userform initialize event rather than using the control source.

Thanks!!!
 

Watch MrExcel Video

Forum statistics

Threads
1,123,359
Messages
5,601,153
Members
414,431
Latest member
JustmemyselfandI

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