Macro works in VBA editor but not when run with hotkey

I think you have renamed the macro after assigning the shortcut. If so the shortcut is looking for the original name. I have created a macro using the same shortcut, but the new name. Added your code & it works.

Please Login or Register  to view this content.

I have also amended your code slighly, see the notes. There are other amendments that would speed it up.

hi all,

I'm hoping to optimise some of my latest coding in the near future & saw this thread when searching for "speed..." to get some tips for my own coding project, so I've had a go at making the "other amendments that would speed it up" which Roy mentioned. I've been able to make a very slight difference in speed with the below code (tested on 5 text files on my C drive) by removing any selection or activation & turning off screen updating. For interest sake I've included the timer code as well.

Roy or others,
Are these the sort of changes you would make?
Would you do anything differently (eg the variable types used in the declarations or a "for each" instead of the loop approach)?

Please Login or Register  to view this content.

hth
Rob

Hi Rob, your code is much more efficient. You might find this (http://www.excel-it.com/clear_code.htm) interesting, also you can often speed things up by switching off Calculation.

Here's a method to change application settings

Please Login or Register  to view this content.

thanks Roy,

I've bookmarked your link & will go back to it as I complete new projects - it's too easy for me to forget this level of detail unless I'm actively applying it as I learn.

I wasn't sure if the calculation mode would make much difference in this case & it isn't a nice easy boolean variable so I left it out although I do use it in some of my other work* (in hindsight though it wouldn't hurt)... I guess CambotB can easily switch the subs.

*Occasionally I have the "bad/indifferent" (?) habit of relying on calculated cell values within in my coding (more so in earlier projects).


Thanks for the AppSetting sub, I'll be including it in my personal.xls as soon as I'm back at work :-)
(edit: I hadn't really looked into it but I didn't realise it was so easy to identify the existing settings.)
Just curious (& w/o testing), is there a special reason for the "with application" clauses being listed within each section of the IF statement?
Or could it be moved to the beginning & end of the sub and used for all the properties in one go?

Thanks
Rob

You need to use the With statement like that because it is part of an If statement, the first With will not be used if the argument isn't true.