Semi-Automatic vs Fully-Automatic: Part 2


Today I’m going to write some more about Office Automation, but specifically Microsoft Excel native capabilities and VBA (Visual Basic for Applications [recorded macros,
and hand-written code]) vs. the fully automatic Excel application. The differences are HUGE and can be life-altering (especially if something goes wrong)!

First of all, Microsoft Excel is INCREDIBLY POWERFUL using native capability, without using a single macro. I am a BIG believer in using Excel to it’s fullest for whatever task you need BEFORE even using your first macro (or other VBA code). In my opinion, VBA should ONLY be applied when you have no other option. You should always, Always, ALWAYS put in copious notes, describing what you are having EACH CHUNK OF CODE do, semi-auto model AND fully automatic.

First, what’s wrong? (I’ll put the comments in GREEN, same as in the VBA project explorer)

Sheets(“MAIN”).Activate

‘PLA PD Dollars
Cells(12, 4) = PD_Dollars(1, 1)
Cells(12, 5) = PD_Dollars(1, 2)
‘ Cells(12, 7) = PD_Dollars(1, 0)
‘PLA PD Lines
Cells(13, 4) = PD_lines(1, 1)
Cells(13, 5) = PD_lines(1, 2)
‘ Cells(13, 7) = PD_lines(1, 0)
‘PLA Dollars Missed
Cells(14, 4) = PD_Dollars(1, 4)
Cells(14, 5) = PD_Dollars(1, 5)
‘ Cells(14, 7) = PD_Dollars(1, 3)
‘PLA Lines Missed
Cells(15, 4) = PD_lines(1, 4)
Cells(15, 5) = PD_lines(1, 5)
‘ Cells(15, 7) = PD_lines(1, 3)
‘PLA BUDCO Lines Missed
Cells(16, 4) = PD_lines(2, 4)
Cells(16, 5) = PD_lines(2, 5)
‘ Cells(16, 7) = PD_lines(2, 3)

‘CUC PD Dollars
Cells(18, 4) = PD_Dollars(3, 1)
Cells(18, 5) = PD_Dollars(3, 2)
‘ Cells(18, 7) = PD_Dollars(3, 0)
‘CUC PD Lines
Cells(19, 4) = PD_lines(3, 1)
Cells(19, 5) = PD_lines(3, 2)
‘ Cells(19, 7) = PD_lines(3, 0)
‘CUC Lines Missed
Cells(20, 4) = PD_Dollars(3, 4)
Cells(20, 5) = PD_Dollars(3, 5)
‘ Cells(20, 7) = PD_Dollars(3, 3)
‘CUC Lines Missed
Cells(21, 4) = PD_lines(3, 4)
Cells(21, 5) = PD_lines(3, 5)
‘ Cells(21, 7) = PD_lines(3, 3)
‘CUC BUDCO Lines Missed
Cells(22, 4) = PD_lines(4, 4)
Cells(22, 5) = PD_lines(4, 5)
‘ Cells(22, 7) = PD_lines(4, 3)

So, did anyone figure out what is wrong with this piece of code? Yes, of course, NO NOTES (or at least pretty useless notes to someone that doesn’t know what CUC and PD is)!

The piece of VBA that this is from is actually VERY elegantly written, and all actions happen in memory, making it very fast. However, you have no idea what cells are being populated. A segment of this code further down shows calculations again, all happening in memory. A nightmare for the inexperienced, and a big tangled ball of string for the experienced coder. I would have preferred that the calculations be visible on a sheet, and make the trade-off of a little bit of speed for the assurance of the calculations and the  transparency of a sheet that you can check. Either way, (Full or Semi-Auto) PUT IN LOTS OF NOTES (preceding each with the accent mark ‘ )!

Another example:

Sub DateSortRoutine()

‘ VB coded by:
‘ Charles A. McCrumb 02/25/2004
‘ActiveWindow.WindowState = xlMinimized
Dim CUM As Currency
‘ Set Cumulative to zero

CUM = 0

‘ Select and sort the Looseners Sheet
Sheets(“looseners”).Select
Selection.Sort Key1:=Range(“P2”), Order1:=xlAscending, Key2:=Range(“B2”) _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom
Range(“A1”).Select
Sheets(“looseners”).Select
Selection.Subtotal GroupBy:=16, Function:=xlSum, TotalList:=Array(8, 11), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True

‘ Select and sort the Assemblies Sheet
Sheets(“assemblies”).Select
Selection.Sort Key1:=Range(“P2”), Order1:=xlAscending, Key2:=Range(“B2”) _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom
Range(“A1”).Select
Sheets(“assemblies”).Select
Selection.Subtotal GroupBy:=16, Function:=xlSum, TotalList:=Array(8, 11), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True

‘ Select Looseners Sheet and check for the text string “Total” and make it bold
Sheets(“looseners”).Select
Range(“P1”).Select
Chekker1:
‘Check for blanks, if none keep going, else stop.
While ActiveCell.Range(“A1”) <> “”
GoDown1
While Right(ActiveCell.Range(“A1”), 5) = “Total”
ActiveCell.Offset(0, -5).Select
Selection.Font.Bold = True
ActiveCell.Offset(0, -3).Select
Selection.Font.Bold = True
ActiveCell.Offset(0, 8).Select
GoTo Chekker1
Wend
Wend

See what happened? Notes, showing just what is going on!

Even in the fully automatic model, notes will keep you and everyone else from going crazy. They will also help you establish trust between me and you, and yourself and others.

Cheers!

Charles McCrumb, Office Automation Expert
Herbert, McCrumb & Associates
We can make ANY business work better!
Visit us at http://www.hm-associates.com
Like us on Facebook for instant updates on what Herbert, McCrumb & Associates is doing, how we’re doing it, and how we’re saving real people time and money!
http://www.facebook.com/HerbertMcCrumbAssociates
email: charles@hm-associates.com
Skype: charles.mccrumb
Bus: (626) 593-6700

Advertisements

One thought on “Semi-Automatic vs Fully-Automatic: Part 2

  1. Pingback: Full-Automation vs Semi-Automation | hmassociates

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s