2

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.

Community
  • 1
tovare
  • 583

4 Answers4

3

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.

DMA57361
  • 18,793
wbeard52
  • 3,483
1

I was looking for an answer to a similar question and found something like that:

public function GetWhatever(byval p1 as variant, byval p2 as variant) as variant
  'Make sure you're in AutoCalculation mode, otherwise use me.calculate
  me.range("A1").value = p1
  me.range("A2").value = p2
  GetWhatever = me.range("A3").value
end function

SO question: https://stackoverflow.com/a/3570907

Community
  • 1
AsTeR
  • 259
0

One thing what I prefer is to create a DATA TABLE by varying the parameter and then LOOKUP for required value in the created data table.

A more proper way will be to write a code in Excel VBA and it can be called very easily, without much effort. However, if you already have the Excel sheet in your hand, you can easily go with former one by creating a DATA TABLE.

Ankit Tiwari
  • 1
0

Since this question was posted, Excel has been updated to add spill formulas. This allows a formula in one cell to returns results to multiple cells. For instance, if your data is in Sheet1, you could add this formula in Sheet2 cell A1 to fill 5 columns, 400 rows with twice the value from the corresponding cell in Sheet1:

=Sheet1!A1:E400*2

If you need to do more complicated math, you could reassign the data to a short name using the LET() function and then do whatever you want, still returning an array of data to those multiple cells in Sheet2:

=LET(x,Sheet1!A1:E400,x*2+SQRT(x)*EXP(x))
Engineer Toast
  • 5,218