VBA coding for multiple worksheets?

[jason061872]

My question for the board is, does VBA coding offer a bettermeans of capturing data and performing calculations such as averaging based acrossmultiple worksheets? I have created aspreadsheet that records scores of tasks performed by task type and state over19 weeks (one worksheet per week).
In addition to each week of task recording, I have alsocreated an additional worksheet that captures the data from each week of audits(each worksheet) and breaks it down by Task Type and State in which the taskwas performed. At a glance, I can seehow many times each task type was performed in a given state along with the totalaverage of each task performed. Example: Task A was performed 5times in Alabama, with a total average score of 2.67 for all 5 audits.

Would this work better in VBA?

 

[JLGWhiz]

There are good arguments on both sides of the issue. VBA frees up memory by eliminating the need for multiple and complex formulas while putting values in cells versus formulas which calculate values. Users cannot destroy the worksheet by over typing the formulas. But formulas are generally faster than VBA code and many users believe formulas are easier to construct and maintain than VBA. I have projects where I use both, and I suppose that my answer would be to use what you think gives you the best results.

 

[Fazza]

Sometimes the biggest gains are from having a good structure. Then working with the data can be enormously easier. There are many benefits: Excel has many tools for working with data in tables. Also it can be linked to a database.

Such as, instead of having 19 worksheets - one per week - have one worksheet with a field for week. Then there are easy options like filtering, pivot tables, etc, etc

 

[Twollaston]

My question for the board is, does VBA coding offer a bettermeans of capturing data and performing calculations such as averaging based acrossmultiple worksheets? I have created aspreadsheet that records scores of tasks performed by task type and state over19 weeks (one worksheet per week).
In addition to each week of task recording, I have alsocreated an additional worksheet that captures the data from each week of audits(each worksheet) and breaks it down by Task Type and State in which the taskwas performed. At a glance, I can seehow many times each task type was performed in a given state along with the totalaverage of each task performed. Example: Task A was performed 5times in Alabama, with a total average score of 2.67 for all 5 audits.

Would this work better in VBA?

I normally just use vba when I want to automate a task that i'd rather not do regularly. So if it's something you're putting a lot of work into maintaining, then likely vba could be a good solution for make alot of the boring tasks 1 click away. If it's something I only have to do 1 or 2 times, then I don't really automate it unless it's faster to complete with vba. So in summary I mainly just use it for speed in task completion