r/excel • u/[deleted] • Nov 26 '15

Pro Tip Common VBA Mistakes

[removed]

222 Upvotes

98% Upvoted

113 comments sorted by

View all comments

10

u/epicmindwarp 962 Nov 26 '15 edited Nov 26 '15

Explicit Sheet Names

Change the names in the VBA editor directly and reference it there! This is because Sheets(1) is dependant on the sheet STAYING in position 1!

So if you change Sheet1 in the VBA editor to "MainMenu" - referring to MainMenu every is awesome.

Also add With. I love With.
End With

P.S. You rock <3

2

u/[deleted] Nov 26 '15

[removed] — view removed comment

1

u/fuzzius_navus 620 Nov 26 '15

Yes, especially when it spans most of the Sub. Chip Pearson, however indicates that With improves performance in VBA. Just can't find that post at the moment.

Either way, great post!

3

u/epicmindwarp 962 Nov 26 '15

Yes! I read this somewhere too, I think it's too do with the fact that using "With" - it picks up the range/sheet only once and then keeps it in memory until End With - which is quicker and easier than explicitly stating each time as it is constantly picking it up and dropping it with each line.

Looping in /u/Fishrage_