Monday, February 11, 2013

Since opening this blog account I have gone back and forth on what to cover and how.  Who I am is probably of little interest to those who would read something called "Adventures in VBA."  Also, there are lots of great resources on the web that provide fairly detailed technical help for people who need it to solve "How do I use VBA to ..." questions and give the code to do it.

That said, here comes the start-up editorial policy for this blog.  I am going to try to cover some of the business situations I see and have seen, e.g. working with report extracts from the "big systems" and providing repeatable reports with them.  I have spent the better part of a year coming up with a few approaches, as I've learned VBA on a trial-and-learn basis, that I think are re-usable for others.  Some may be laughable as I've kludged together what I need using my limited knowledge...and I may use some of my blind alleys for reference to how not to do it

At this point I have three situations that I think are fairly common. Generally, my responsibility has been to take information from "big systems" and make the data usable for managing work.

  • Priorities: A workbook that has a list of prioritized open issues.  On a weekly basis, a big system extract is used to add new issues to the list and update the status of all issues. During a meeting the issues are put into the client's priority.  After the meeting the workbook is  refreshed to the common site (e.g. SharePoint, a website, or a LAN drive) so the supervisors and manager's can
  • Review: A workbook that is published after monthly meeting.  It uses an extract from a  "big system", plus notes taken from the previous month, plus information from another workbook.  In the real case, this is the workbook mentioned above and uses the same extract because I'm too lazy efficient to maintain two queries but I may use my earlier process as a learning point in case someone needs the two extract approach.
  • Performance: A workbook that is published each month, with daily performance data at the team member level. This uses a different "big system" for its extracts, and optionally an extract from the same big system mentioned above, but a completely different extract.  This one has reference tables for holidays, two levels of teams, primary and back-up responsibility.
.