Question on importance of releasing variables..

[largeselection]

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?

 

[Jerry Sullivan]

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]

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!!!