Should I use public variables? Or is it a good practice to write dim in every sub? VBA EXCEL

Is it efficient to use public variables in vba? Is it buggy? Should I stick with writing dim in every sub or can I be confident that public variables will perform just fine?

2 answers

  • answered 2018-10-16 17:26 Error 1004

    In my opinion If you have variables repeated in many modules is better to create a separated module and import all public variables in it to avoid repetition.

  • answered 2018-10-16 17:42 peakpeak

    The danger might be that you use your public variable as if it was local to the function you're executing. Next function might assume a fresh start and overwrite the value in the public variable which should be preserved.

    I use a mix and always favour local variables. I also prefix all globals with g_, eg g_masterFileName