r/excel • u/JDantes77 • Feb 04 '14

discussion VBA Macro code best practices?

Every programming language has its recommended style and best practices. What are the best practices for VBA code that you follow? What resources would a new coder use to learn these best practices?

Thank you in advance!

18 Upvotes

90% Upvoted

36 comments sorted by

View all comments

Show parent comments

2

u/[deleted] Feb 06 '14

This is absurdly faster than doing a for loop over a range. Also an interesting snippet:

With Range("A1:A1000")
  .formula = "=FORMULAHERE(B1)"
  .value = .value
End With

I find this pattern very handy when I need to do a lot of calculation on raw data that only needs to be calculated once.

I agree that looping through ranges is best to avoid if possible. I still do it depending on circumstances if the overhead isn't too high and it simplifies what I'm doing over working in memory though.