Excel: using a worksheet as a function?

Question

I've got an excel worksheet that takes two inputs and generates an output.

I can currently open the worksheet, type the two into cells A1 and A2, and the result shows up in A3.

Is there a way I can make this into a function or subroutine, such that I can can use it in another worksheet to fill in a table of values?

Answer 1

Data tables could work for you, but you can't refer to a cell from a different sheet. At least, that's the case for Excel 2003, you can try it out in 2007 or 2010 to see if it works.

---

Other that that:

Yes, you can make a subroutine.
You can't make a worksheet function though, as they are not allowed to change sheets.

On that worksheet, create a function that goes:

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

You then can repeatedly call this function from another procedure, which is on the second worksheet, and copy the newly aquired result into the next available row on your worksheet:

for i = 1 to 10
  me.cells(i,3).value = SheetWithCalculations.GetWhatever(me.cells(i,1).value, me.cells(i,2).value)
next