O
onedaywhen
I am working on an Excel VBA project that uses lots of custom objects
i.e. I have lots of class modules and instantiate many objects in my
initialization routine called when the workbook is opened.
I find it frustrating that it is very easy for a user to reset the
project and in doing so kill my objects, static variables etc. Perhaps
the easiest way to do this *intentionally* is to open the VBE and
press the VCR stop button; workbook-level or VBA project protection
does not prevent this. There also seems to be many ways a user can do
this unintentionally; a change which affects the VBA project of any
open workbook (e.g. adding an ActiveX control to a worksheet) seems to
reset my project.
These situations can be circumvented, AFAIK. For example, in every top
level sub procedure (effectively all the event procedures) I test my
objects and re-run my initialization routine if necessary. However,
the fact this situation can arise has dented my confidence in a VBA
Excel solution.
Related to this is the risk posed by the VBE Immediate Window. Again,
AFAIK a user cannot be prevented from accessing and using the
Immediate Window and they can use it to make changes to my protected
workbook. I don't wish to put ideas into anyone's head but my project
relies on a number of defined Names which seem to be fully accessible
via the Immediate Window. Re-initializing my Names would be a lot more
difficult than resetting my objects. I use defined names because of
their dynamic nature (i.e. I do not need to 'hard code' range
addresses) and I not sure I'd be able to detect changes that happened
between calls to my VBA project.
Of course the issue with Names is not limited to VBA. I could write a
managed code .NET solution (which I'm contemplating) but the user
would still be able use VBA to change aspects of a protected workbook
on which my code relies.
At this point I decided that if a user wants to mess with my defined
Names then fine, just don't expect my application to work again
(unless you want to pay me to fix it). But it does make me wonder
about whether an Excel solution that uses in-process code (as opposed
to an .exe solution that automates Excel) seems a bit unprofessional.
I would appreciate any views on these issues.
i.e. I have lots of class modules and instantiate many objects in my
initialization routine called when the workbook is opened.
I find it frustrating that it is very easy for a user to reset the
project and in doing so kill my objects, static variables etc. Perhaps
the easiest way to do this *intentionally* is to open the VBE and
press the VCR stop button; workbook-level or VBA project protection
does not prevent this. There also seems to be many ways a user can do
this unintentionally; a change which affects the VBA project of any
open workbook (e.g. adding an ActiveX control to a worksheet) seems to
reset my project.
These situations can be circumvented, AFAIK. For example, in every top
level sub procedure (effectively all the event procedures) I test my
objects and re-run my initialization routine if necessary. However,
the fact this situation can arise has dented my confidence in a VBA
Excel solution.
Related to this is the risk posed by the VBE Immediate Window. Again,
AFAIK a user cannot be prevented from accessing and using the
Immediate Window and they can use it to make changes to my protected
workbook. I don't wish to put ideas into anyone's head but my project
relies on a number of defined Names which seem to be fully accessible
via the Immediate Window. Re-initializing my Names would be a lot more
difficult than resetting my objects. I use defined names because of
their dynamic nature (i.e. I do not need to 'hard code' range
addresses) and I not sure I'd be able to detect changes that happened
between calls to my VBA project.
Of course the issue with Names is not limited to VBA. I could write a
managed code .NET solution (which I'm contemplating) but the user
would still be able use VBA to change aspects of a protected workbook
on which my code relies.
At this point I decided that if a user wants to mess with my defined
Names then fine, just don't expect my application to work again
(unless you want to pay me to fix it). But it does make me wonder
about whether an Excel solution that uses in-process code (as opposed
to an .exe solution that automates Excel) seems a bit unprofessional.
I would appreciate any views on these issues.