Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question
1  
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
1  
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
1  
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

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.