2

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?

Mark Harrison
  • 297,451
  • 125
  • 333
  • 465

1 Answers1

4

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
GSerg
  • 76,472
  • 17
  • 159
  • 346
  • What you write doesn't make any sense ....You write "You can't make a worksheet function though, as they are not allowed to change sheets." and then you show a function in which you change the cell ???? " me.range("A1").value = p1" – Stéphane Gerber Oct 25 '15 at 10:20
  • Care to explain why it doesn't make any sense @StéphaneGerber? – GSerg Oct 25 '15 at 10:21
  • Can I contact you somewhere, I really need this to work, thank you. – Stéphane Gerber Oct 25 '15 at 10:26
  • @StéphaneGerber You are contacting me here (in an unhelpful way, so the discussion might be deleted by a mod for good). If you think this code does not do what it [should do](http://stackoverflow.com/q/3570720/11683), please explain why in a comment. – GSerg Oct 25 '15 at 10:28
  • Ok, got it ! You can only call function from VBA, not from the sheet. I didn't want to sound rude, it just Excel making me mad ... – Stéphane Gerber Oct 25 '15 at 10:43
  • This http://stackoverflow.com/questions/15659779/set-a-cell-value-from-a-function helped me too ... – Stéphane Gerber Oct 25 '15 at 10:46