Tips for being a more Productive Developer (Excel/VBA)

[doggo]

I am interested to here how other developers save time when developing in Excel / VBA. I think that I have room to improve my efficiency and I want to address the ASAP as I have a busy few months ahead.

Basically, what tools, techniques or stategies help you diagnose and churn out excel applications/solutions quickler?

 

[martinee]

Have a good idea of what you will be doing before you set out to actually do it. Sketch the layout of your data and be sure you will later be able to manipulate it as needed later on without needing to reformat it. If you plan ahead and ensure your data is properly normalized before you begin, you will save a lot of time later on. If you want a specific front end database, consider the use of forms and pivot tables. Also, it helps to know the special tricks of VBA and Excel functions. However, this list is ever-increasing and endless. Hope this helps a little though.

 

[doggo]

Agree with you there martinee that having a good data structure does save a lot of time later on as it enables you to quickly add on additional features.

What about specific Coding Techniques or tools that you may use that really increase your productivity?

 

[just_jon]

Use Option Explicit - forces Dim'ing of variables and can save time tracking down coding errors at run-time.

 

[erik.van.geit]

avoid unnecessary loops

instead of
For Each cell in Range("A1:A10000")
cell.FormulaR1C1 = "=RC[1]"
Next cell
use
Range("A1:A10000").FormulaR1C1 = "=RC[1]"

this is a "clear" example
but sometimes we think a loop is needed when not

(like deleting rows when first cell is empty)

kind regards,
Erik

 

[PA HS Teacher]

doggo,
I am at best an amateur, but watching how the pro's operate on here is making me a much more efficient Exceler (even if being on here makes the rest of my life less efficient in the short term.)

Here's as site that goes a long way toward asnwering your question.

http://www.ozgrid.com/Excel/ExcelSpreadsheetDesign.htm

 

[doggo]

Thanks Teacher - Very Good link - just what I was looking for . Anyone suggest any useful tools?