Calc2Code Tips of the Day

Day 4

Yesterday wew showed how to create a loop in VBA to handle one of the few tings you can't easily do in a spreadsheet. Today, we're going to show how Cal4web gives you a way to create a loop in a spreadsheet. This will allow you to create a function that calculates things a specified number of times, that can vary from one call to the next.

We have to state upfront that this doesn't apply to all situations. But for those that are applicable, it will allow you to create a small spreadsheet to build a function that can operate on an unlimited number of rows and columns, well beyond even Excel 2007's limits.

Specifically, if you create a function that has one or more input arguments that are ranges, and build your spreadsheet correctly, then those ranges can have any number of rows and columns when the new function is called into.

A simple example spreadsheet is mysum.xls:

This just has a cell that adds the values in a range. Of course, this is the same as one of Excel's built-in functions, but it serves to demonstrate the Calc2Code feature. The inputs to the function are E5:F7. And nowhere in the spreadsheet cells that will be converted by Calc2Code (OK, there's only one of these cells, F9) contain a reference to E5 by itself, E6 by itself, or any other single cell. instead, the only time the input area is referenced is when it is references as the entire input range, E5:F7.

That's the trick: for any input that is a range, always reference the entire range in a formula, not a piece of it. This will cause Calc2Code to generate C++ code that is a loop, where the loop goes over the number of rows and/or columns of the input range, based on the size of the input range when the function is called, and not based on the size of the input range when the function is built. So when mysum is called with a range that has 100 rows and 5 columns, the generated function will loop over all 500 elements, even though the range had only 3 rows and 2 columnswhen Calc2Code built it.

A more sophisticated example spreadsheet is weightedaverage2.xls:

We have two columns: one is how often a value was observed, and the next is the value that was observed. These could be the height of people, and the number of people of each height, or grades, and the number of courses in which that grade was received, or anything else. We want to take the product of each element in the first column times each corresponding element in the second column.

Normally in Excel H4 would contain =F4*G4 and we'd copy it down to the bottom of the input area. But we cannot have a formula with F4 in it, we need it to have F4:F8. And it can't have G4 in it, it needs to have G4:G8. Fortunately, Excel lets us do just that. Highlight H4:H8, and enter =F4:F8*G4:G8, and instead of hitting Enter, hit Ctrl-Shift-Enter, and Excel will fill all of them with the proper formula. This is an array formula, and it makes creating loops much more straightforward than they would be otherwise.

Since the formula in Column H references the entire input range, it will have the same number of rows in it when the function is called as the input ranges will have. And the SUM() that is done on column H in H10 will therefore operate correctly as well.

Here's the configuration screen for the weightedaverage function:

And what if you need to do something with an individual value in an input range? Let's say the rule is that if the first input is a single value instead of a range, then the frequencies for all values are equal to that first input (an equal weighted average). The spreadsheet weightedaverage3.xls shows how to do this off to the right:

The formula in L4:L8 creates a new range that is either equal to the first input range when the size of the input range is not equal to 1, or it creates a range that is the same size as the second input, but filled up with the first (and only) element of the first input range.

It can seem a little complicated to use Excel formulas this way, but it gives you the ability to create functions which are not limited in the same way as some of our earlier examples.

Click here to see the next tip.