Secluded Cells


Anyone know what does this code is for?

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



Hmm... maybe... no, that's not it.


I know! It's the first tip program!


It is? Oh, it has 0.15 in it, so maybe it is.

Yes, it's the first tip program. Here's the real code:

  • 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

And the new version:

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

What did I change to make the new one?


You got rid of the variables.

There's less typing, I guess, but wow, it makes my head hurt.

Yes, on both counts. It's less typing, but...

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.


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:

  1. Sub Tippy()
  2.   Dim sMealCost As Single
  3.   Dim sTip As Single
  4.   Dim sTotal As Single
  5.   sMealCost = Cells(1, 2)
  6.   sTip = sMealCost * 0.15
  7.   sTotal = sMealCost + sTip
  8.   Cells(5, 2) = sTip
  9.   Cells(6, 2) = sTotal
  10. 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


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


  • 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

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.

  1. Sub SomethingRotten()
  3.     Dim sLightExerciseBandPrice As Single
  4.     Dim sLightExerciseBandNumOrdered As Single
  5.     Dim sLightExerciseBandTotalPrice As Single
  7.     Dim sMediumExerciseBandPrice As Single
  8.     Dim sMediumExerciseBandNumOrdered As Single
  9.     Dim sMediumExerciseBandTotalPrice As Single
  11.     Dim sHeavyExerciseBandPrice As Single
  12.     Dim sHeavyExerciseBandNumOrdered As Single
  13.     Dim sHeavyExerciseBandTotalPrice As Single
  15.     Dim sSalesTaxRate As Single
  16.     Dim sOrderTotal As Single
  17.     Dim sOrderTotalPlusTax As Single
  19.     ' Input.
  20.     sLightExerciseBandPrice = Cells(6, 2)
  21.     sLightExerciseBandNumOrdered = Cells(6, 3)
  23.     sMediumExerciseBandPrice = Cells(7, 2)
  24.     sMediumExerciseBandNumOrdered = Cells(7, 3)
  26.     sHeavyExerciseBandPrice = Cells(8, 2)
  27.     sHeavyExerciseBandNumOrdered = Cells(8, 3)
  29.     sSalesTaxRate = Cells(10, 2)
  31.     ' Processing.
  32.     sLightExerciseBandTotalPrice = sLightExerciseBandPrice * sLightExerciseBandNumOrdered
  33.     sMediumExerciseBandTotalPrice = sMediumExerciseBandPrice * sMediumExerciseBandNumOrdered
  34.     sHeavyExerciseBandTotalPrice = sHeavyExerciseBandPrice * sHeavyExerciseBandNumOrdered
  36.     sOrderTotal = sLightExerciseBandTotalPrice + sMediumExerciseBandTotalPrice + sHeavyExerciseBandTotalPrice
  37.     sOrderTotalPlusTax = sOrderTotal * sSalesTaxRate
  39.     ' Output.
  40.     Cells(12, 2) = sOrderTotalPlusTax
  42. End Sub

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.


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.