Using excel sheet as a function

Question

I wonder how I can use an excel sheet as a function.

Let's say I have a complex calculation in Sheet1 with one parameter I would like to iterate over a range like [1 to 400] in a second sheet to create an x, f(x) table in Sheet2.

I'll be using Excel or Open Office.

How would I go about doing this?

Thanks.

---

Progress update

Attempted to do a test in the Sheet (General):

Public Function test() as Boolean
    test = True
End Function

Then in a cell:

=test()

Resulting in #Name?

Also tried scouering online tutorials.

---

Partially solved:

• Visibility of the function is achieved by putting the code in a module.
• Insert -> module in VBA.

---

Progress:

Setting av value using

 Worksheets("Sheet1").Range("A1").Value = 10

 or

 Worksheets("Sheet1").Cells(1,1).Value = 10

failes silently and doesn't execute the line under.

---

Progress:

The techniques work fine if I dumb down the code to a parameterless subroutine and assign it to a button.

Workaround

Implementation was really straight forward when implementing using a VBA macro launched from a button rather than using a function.

I iterated over a range of rows to get the inputs to the calculations.

Answer 1

The best way is to create the algorithm in Excel VBA. You can open up VBA and type this

Public Function Test1(x as Integer, y as Integer)  
    Test1 = x*y  
End Function   

You can call this function like any other function from the formula bar
In formula bar for A1

• =Test1(2,4) with a result of 8.

Change the algorithm in VBA to get the results you are looking for.