Take the 2-minute tour ×

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?


Progress update

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

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

Then in a cell:


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.


Setting av value using

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


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

failes silently and doesn't execute the line under.


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


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.

share|improve this question
RE the test function failing - have you enabled macros for the file in question? IIRC, with them disabled, custom functions will not operate. –  DMA57361 Sep 7 '10 at 13:04
Thanks, I think maybe it was a visibility issue. Moving the code to a module solved it. Now on to the hard part :) –  tovare Sep 7 '10 at 13:06
Both those examples in your last update work for me (Excel 2003, what version are you using?). You can also use Sheets("Sheet1") and .Formula = 10 - both effectively the same when setting values (but Formula is quite different when retrieving). I doubt this will help though... –  DMA57361 Sep 7 '10 at 14:33
I tried two installations of Excel 2003. One had some VB-buttons disabled by the corporate admin, but I got around it. The second should be pretty unconstrained by coorporate snake oil, still didn't work though. I'll try some more tomorrow :-) –  tovare Sep 7 '10 at 19:16
If the sheet is protected then you can't set the value. You either have to unprotect it, or user the USERINTERFACEONLY parameter when locking it. –  Lance Roberts Sep 7 '10 at 20:28

2 Answers 2

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.

share|improve this answer
I'll try that, I just assumed it wouldn't work due to the amount of calculations that would need to be re-calculated in the first sheet every time. –  tovare Sep 6 '10 at 20:22

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: http://stackoverflow.com/a/3570907

share|improve this answer

Your Answer


