Secluded Cells | Programming with Excel VBA

Harder to understand

Variable names give you hints about what data means. Cells(1, 2) in one program, sMealCost in the other.

When you don't have those hints, your brain has to keep what each cell means as you code. Annoying here, with only one input value. A killer when your programs are 50 lines long, with 12 input values. You're going to make mistakes.

It will also feel bad when you're coding. When your thinking meat's capacity is exceeded, you start to feel overwhelmed, and anxious. One of the most important things in programming is keeping your thinking meat happy.

Harder to debug

When you stop using variables, finding mistakes gets really hard, and unpleasant. So, not only are you more likely to make mistakes, they'll be harder to fix.

Georgina

Dude, that would suck.

Yes, it would.

Harder to change

Business always changes. New products, strategies, markets, people... change is constant.

Programs change, too. Programmers spend more time changing existing code, than writing new code.

Say your boss said to change the tip program. Add a row at the top, with some instructions, maybe.

A program written this way...

Sub DoSomething()
Cells(5, 2) = Cells(1, 2) * 0.15
Cells(6, 2) = Cells(1, 2) + Cells(5, 2)
End Sub

... would be a pain to change. You'd have to work out all the new cell locations, and change them consistently in all the code. Ack!

The chances of you making a mistake? Pretty high. That means more time testing, and debugging.

Ack to the max!

Here's the other version:

Sub Tippy()
Dim sMealCost As Single
Dim sTip As Single
Dim sTotal As Single
sMealCost = Cells(1, 2)
sTip = sMealCost * 0.15
sTotal = sMealCost + sTip
Cells(5, 2) = sTip
Cells(6, 2) = sTotal
End Sub

To change which cell the input comes from, change line 5. That's all! Once the input value goes into sMealCost, the variable is used in the rest of the program.

The cost of typing

Ray

The short program takes less typing, though. That saves money, right?

Yes, it does, but the cost of testing, debugging, and changing is much higher than the cost of typing. I don't have a firm number, but I'd say that testing, debugging, and changing takes maybe 50 times as long as typing, for a typical real-world program. Yes, I mean 5,000% more.

The differences will be less for us, with our simpler programs, but will still be more than 100%. Especially since the VBA editor makes typing variable names quicker, with Ctrl+Space. You'll learn more about that in the next lesson.

Don't forget the difference in anxiety, either. I'm an anxious person, so reducing that is a Good Thing for me.

A new rule: secluded Cells

We have some programming rules that will be enforced when you do exercises, like indenting, and using Option Explicit. I'm adding a new one: secluded Cells.

From now on, in every exercise, Cells should be by itself on the left or right of =. These lines are all OK:

' Input.
sNoseCount = Cells(3, 2)
tNameOfLeftHoof = Cells(5, 2)
sPriceGoatBeardRing = Cells(12, 11)
' Output.
Cells(21, 3) = sTotalGoatCount
Cells(8, 2) = tFullName
Cells(18, 3) = aAverageStench

In every case, Cells is by itself on the left or right of =.

These are not OK. If you use them, your exercise submissions won't be marked as complete:

Cells(22, 8) = Cells(2, 8) + Cells(3, 2)
sGoatLength = Cells(7, 3) * sGoatCount

Remember, when you use Cells, it should be by itself.

The secluded Cells rule applies to every form of input, like InputBox. So, this is a Bad Thing:

sNumGoats = InputBox("How many legs did you count?") / 4

Instead...

sNumLegs = InputBox("How many legs did you count?")
sNumGoats = sNumLegs / 4

Another example

There's a bug in this code. What is it?

Sub Argh()
Cells(12, 2) = (Cells(6, 2) * Cells(6, 3) + Cells(7, 3) * Cells(7, 2) + Cells(8, 2) * Cells(8, 3)) * Cells(10, 2)
End Sub

Adela

Now you're just being silly. How could we tell?

OK, you got me. There is literally no way to tell what the bug is. It's impossible.

Here's another version with the same bug.

Sub SomethingRotten()
Dim sLightExerciseBandPrice As Single
Dim sLightExerciseBandNumOrdered As Single
Dim sLightExerciseBandTotalPrice As Single
Dim sMediumExerciseBandPrice As Single
Dim sMediumExerciseBandNumOrdered As Single
Dim sMediumExerciseBandTotalPrice As Single
Dim sHeavyExerciseBandPrice As Single
Dim sHeavyExerciseBandNumOrdered As Single
Dim sHeavyExerciseBandTotalPrice As Single
Dim sSalesTaxRate As Single
Dim sOrderTotal As Single
Dim sOrderTotalPlusTax As Single
' Input.
sLightExerciseBandPrice = Cells(6, 2)
sLightExerciseBandNumOrdered = Cells(6, 3)
sMediumExerciseBandPrice = Cells(7, 2)
sMediumExerciseBandNumOrdered = Cells(7, 3)
sHeavyExerciseBandPrice = Cells(8, 2)
sHeavyExerciseBandNumOrdered = Cells(8, 3)
sSalesTaxRate = Cells(10, 2)
' Processing.
sLightExerciseBandTotalPrice = sLightExerciseBandPrice * sLightExerciseBandNumOrdered
sMediumExerciseBandTotalPrice = sMediumExerciseBandPrice * sMediumExerciseBandNumOrdered
sHeavyExerciseBandTotalPrice = sHeavyExerciseBandPrice * sHeavyExerciseBandNumOrdered
sOrderTotal = sLightExerciseBandTotalPrice + sMediumExerciseBandTotalPrice + sHeavyExerciseBandTotalPrice
sOrderTotalPlusTax = sOrderTotal * sSalesTaxRate
' Output.
Cells(12, 2) = sOrderTotalPlusTax
End Sub

Ray

Oh, I get it. It works out the price of an exercise band order, including tax.

I see how the variable names help.

There's another thing, too. Each line is simple. It does one small step.

Right! Each step is small, making the whole thing easier to understand, debug, and change.

A lot more typing, but worth it for the reduced anxiety alone.

Ethan

Hey! I see the bug! I think. It's in line 37. It multiplies by the tax rate, like 0.06. It should be 1 plus the tax rate, 1.06.

Great! Yes, that's it.

It's new

The secluded Cells rule is new, added after the rest of the course was written. I'll go through and change the code in the course, to fit the rule, but I might miss some. Please let me know.