using a sheet in an excel user defined function

Question

The VBA I'm trying to write is fairly simple but Ive never written VBA and coming from the visual studio & C# world, this truly is hell!!

So I'll really be grateful for any help/pointers/hints here

So I have two important sheets. The Range sheet has 2 values per date. It needs a result, The Calc sheet takes two values and gets me a result.

I want to Put the Current, and OneYear values for each date into the Calc sheet and get the result into the result column.

So I tried defining a UDF but I learned later I cannot modify sheet contents in a UDF.

I tried recording a macro and then hoped to go from there, but I got something like this:

Range("A2").Select
Sheets("24Feb05-24FEB10").Select
Range("K9").Select
ActiveCell.FormulaR1C1 = "=Calc!R[-8]C[-10]"
Range("K9").Select

Im assuming R1C1 is Row and Column but why on earth does it say -8 and -10!??

Can someone get me in the right direction?

Note: There are about 2000+ dates.

Hope my question is clear!

---

Update I'll clear up a few more things:

Im grossly simplifying the Calc sheet, in reality, it needs 5 values (there are columns upto SixYears) And then that sheet calls about 4 other sheets and does some finance calculations that some non-tech-savvy guy wrote.

I tried to get the finance guy to express to me in algebra what he's doing, so I could just write up a C# function.

I also tried writing a UDF, but like I said it needs to put in the values and get the result, which means the UDF will modify the sheet.

Function Func1(txt As Variant) As String
//Returns the nth element from a delimited text string
    Dim txt As String
    txt = text
   Sheet4.Cells(1, 1) = txt
   Func1 = Sheet4.Cells(2, 1)//get the result value'
End Function

Basically Im having a head ache learning/understand VBA syntax and the API.

Im looking for maybe a small example of how I would take values from one sheet into another and then get the some other values back? Maybe in a loop!?

Answer 1

You might be making this more complicated than you need to. As a programmer, I'm sure you appreciate that when learning a new system, a little time with the help, Google, "Dummies" books, etc. is time well spent. Excel emphasizes a very different programming style than, say, C#.

So, here are some options, starting with the simplest and most Excel-idiomatic:

1) Use a formula in your result column. If the calculation you want to do is simple enough, this is the way to go. As a trivial example, say your calculation is just adding the two numbers. You'd put the formula

=B2+C2

into cell E2, and then drag it down the E column. Excel will know what to do and make forumlas for E3=B3+C3 and so on.

You can do quite a lot with just formulas. Excel has many built-in worksheet functions, and you can find tons of material on the web and in the bookstore.

2) Write a UDF in VBA and call it from the result column. A UDF is a function that doesn't modify anything else in the worksheet. It just calculates a result based on its input parameters. You would write a UDF when you have something too complex to do in a formula, or when you want to use the formula in multiple places. (Note that dragging a forumla across a Range is idiomatic Excel and counts as "one place" for DRY purposes).

So, to write a UDF (let's use the addition example, but you'd write more complex logic), you'd put a function like this into a VBA code module:

Public Function myUDF(current, oneYear)
    myUDF = current + oneYear
End Function

You'd then put the formula

=myUDF(B2, C2)

into cell E2 and drag that down the E column.

Note that I've neglected stuff like error handling in this example. This is one reason why formulas are more "Excel" than UDFs. Now you have to start writing code that departs from Excel's declarative functional style, and it's a different mental model.

EDIT: 2.5) Use an Excel Data Table - I'm embarrased that I forgot this before, but if you have exactly one or two cells that are your independent parameters, and exactly one cell that is your result, then Excel has a built-in "what-if" analysis tool that you can use. The name "data table" is regrettable since both of those words are so heavily overloaded, but that's what it's called. You tell Excel what your input(s) and output are, and it will do what you want. See:

http://office.microsoft.com/en-us/excel-help/calculate-multiple-results-by-using-a-data-table-HP010072656.aspx

Here is the little intro from that link:

A data table is a range of cells that shows how changing one or two variables in your formulas (formula: A sequence of values, cell references, names, functions, or operators in a cell that together produce a new value. A formula always begins with an equal sign (=).) will affect the results of those formulas. Data tables provide a shortcut for calculating multiple results in one operation and a way to view and compare the results of all the different variations together on your worksheet.

3) Write a macro that uses your calc sheet like it was a function. You might do this if you've got really complex logic on that sheet, and it's too hard to convert to VBA because it changes all the time or because it's being developed by an Excel user who doesn't know VBA and isn't familiar with imperative programming. I'm not going to supply code, but the pseudocode might look like this:

Public Sub doCalc()
    'for each cell in the result column
        'copy the corresponding current and oneYear cells to the calc sheet
        'Excel will recalc the calc sheet
        'copy the result cell on the calc sheet back to the Range sheet in the right place
    'end
End Sub

This is now departing pretty far from the "Excel way", since you have to set up your data yourself, and then run the macro. If you go back and change some of the "inputs", you need to re-run the macro. (This can be automated to some extent, using Worksheet events.)

Lot's of programmers who are new to Excel jump right to this step, relegating Excel to a giant grid control and neglecting the reason it exists in the first place - automatic recalculation.

EDIT IN RESPONSE TO ADDITIONAL INFO IN QUESTION:

O.K., it looks like case (3) is appropriate because you have a complex sheet not easily manually translated into VBA or C# code. Here is some (oversimplified, hardcoded, lacking EH, etc.) code to give you more of an idea of how to do this in VBA:

First, there is the VBA macro that pretends it's a UDF. Note that it is a Sub, not a function, and you don't call it from a formula. You call subroutines in response to the user doing something like choosing a menu option or pressing a command button:

Public Sub fakeFunct(input1, input2, outputRng As Range)
    CalcSheet.[b1] = input1
    CalcSheet.[b2] = input2

    'Excel recalcs what's in cell B3 if you have it set to automatic recalcualation

    outputRng = CalcSheet.[b3]
End Sub

In this example, 'CalcSheet' is the "code name" of your worksheet module. (For more about this, see Chip Pearson's excellent website: http://www.cpearson.com/excel/codemods.htm)

You'd actually call the above macro from another one that loops through your "inputs":

Public Sub loopFakeFunct()
    Dim i As Long
    For i = 2 To 2000
        Call fakeFunct(RangeSheet.Cells(i, 2), RangeSheet.Cells(i, 3), RangeSheet.Cells(i, 5))
    Next i
End Sub

This is the thing you call in response to user action, or from the Worksheet_Change event, etc. It will be slow and clunky, and I'm leaving a lot of practical VBA stuff out, but it should be enough to get you started. (I used 'RangeSheet' instead of 'Range' as a sheet code name to avoid conflicts with the Range class and the Application.Range method defined by Excel.)

4) Use some third-party tool that converts your worksheet into a UDF that you can then call as in (2). There are commerical tools out there that will take your sheet, let you specifiy which cells are inputs and outputs, and then generate C++ code or whatever that can be compiled into an XLL add-in.

5) Wait for Microsoft to do this:

http://research.microsoft.com/en-us/um/people/simonpj/papers/excel/excel.htm

6) Use Resolver One:

http://www.resolversystems.com/products/resolver-one/

It has a feature that allows you to use a whole workbook as a function in another one, or I think in any Python code.

Answer 2

You recorded your macro in "relative" mode. R[-8]C[-10] means: the cell 8 rows above and 10 columns to the left of current cell.