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

doggo

Board Regular
Joined
Jan 22, 2004
Messages
115
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?
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

martinee

Well-known Member
Joined
Nov 4, 2003
Messages
960
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

Board Regular
Joined
Jan 22, 2004
Messages
115
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

Legend
Joined
Sep 3, 2002
Messages
10,473
Use Option Explicit - forces Dim'ing of variables and can save time tracking down coding errors at run-time.
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832

ADVERTISEMENT

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
 

doggo

Board Regular
Joined
Jan 22, 2004
Messages
115
Thanks Teacher - Very Good link - just what I was looking for . Anyone suggest any useful tools?
 

Forum statistics

Threads
1,147,626
Messages
5,742,214
Members
423,714
Latest member
ftp2jz

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top