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?
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.
- 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 or 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.