Will Too Many Public Variables Cause Speed Issues?

[ngunn]

Hello, this is my first time posting.
I've just started a new role taking over from someone who has just built a tool in VBA for a project. I'm reviewing the code to get up to speed with how the tool works. I haven't got all the way through it yet, but I know the code takes a couple of hours to run (which seems excessive to me), and also cashes often.
I've noticed that the person has used what I would consider a large number of Public variables - 54, amongst these there are several dictionaries.
I'm only meant to be spending time on the code if I see anything that would solve the speed or stability issues.
So I don't really want to spend time restructing the code only to find it doesn't make much of a difference.
My understanding is that best practice is to keep Public variables to a minimum due to memory uses.

 

[bebo021999]

I dont think so.
There are some common issues those cause code run slowly:
1- Read from sheet, write to sheet directly
For example:

For each cell in range("A1:A100000")
   range("A1").copy range("B1")

Next
to avoid it, try to read range value and store in to an array, calculate then paste back to sheet
2- There are too many unnecessary loop: for, do loop...

It may help if you could share how your code looks like.

 

[ngunn]

I dont think so.
There are some common issues those cause code run slowly:
1- Read from sheet, write to sheet directly
For example:

For each cell in range("A1:A100000")
   range("A1").copy range("B1")

Next
to avoid it, try to read range value and store in to an array, calculate then paste back to sheet
2- There are too many unnecessary loop: for, do loop...

It may help if you could share how your code looks like.

Thanks, I can't really share the code as it's in a zillion different modules and goes all over the place. It's pretty poorly written, and I'm only half way through it. So far there isn't a huge amount of reading from Excel (other than loading information into the dictionaries) and no writting back (yet). The number of public variables is the main thing that is jumping out at me, but there will likely be other issues in the second half of the code.
I'll keep going through it, and see what else I spot.