How could I optimize this VBA project ?

J

Jey

Hi !

I have a little issue with a workbook : it takes wayyyy too long to
update...

So I have a sheet called "Map", where I must update comments on certain
cells... the problem comes from the 2 For... Next statements that take a lot
of time to process, because I have to activate 4 different sheets each
time... I have about 150 system, and 12 planets for each, that's 1800
possibilities !!!
Can I get the same result with another method ?

I tought about create another sheet where I could retrieve all infos I need,
but I can't figure out how to do this...

Currently, I have :

For each system
Computing coordinates => call the sheet "system"
For planet = 1 to 12
Retrieve the owner of the planet, and the alliance he is from =>
test with 2 sheets, "player" and "alliance"
Retrieve if this alliance is friendly or not => table on another
sheet, "diplo"
if friendly color of the line will be blue, otherwise, red...
keep in the memory the line and the shape
Next planet
Concatenate all the lines from the 12 planets, and copy this to the
comment of range(coordinates)
apply shape and colors to the comment
Next system

I can send the .xls file if you need the "true VBA" code ;)
 
Top