0

I have this function below. Trying to set the value is causing it to fail. If i comment out the value section it works as planned but i need to show the other 2 values in the cells given. I get the #Value! error when they are not commented out. Thanks for assistance.

Function GetAreas(str As String, Optional ttl1 As Range, Optional ttl2 As Range) As String
    ttl1.Value = 250
    ttl2.Value = 200
    GetAreas = str
End Function

I am calling the function using a formula like this. 

=IF($H3<>"",GetAreas($H3, $J3, $K3),"")
Community
  • 1
  • 1
deathismyfriend
  • 2,182
  • 2
  • 18
  • 25
  • A UDF is only allowed to return a value to the that called. It cannot set another cell's value. – Excel Hero Oct 19 '15 at 00:14
  • @ExcelHero I'm guessing i would have to use a macro to do this then ? – deathismyfriend Oct 19 '15 at 00:15
  • @ExcelHero, maybe possible using techniqes not too dissimilar to your recent comment in another thread :-) http://stackoverflow.com/questions/8520732/i-dont-want-my-excel-add-in-to-return-an-array-instead-i-need-a-udf-to-change – MacroMarc Oct 19 '15 at 00:24
  • @deathismyfriend Yes. – Excel Hero Oct 19 '15 at 00:38
  • @MacroMarc Perhaps, but I'm not recommending it in this case. I am one that believes in the design objective of worksheet functions. – Excel Hero Oct 19 '15 at 00:44
  • @Excel Hero - absolutely agree with the principle of clean, which is why I've never written anything using the technique. Don't like messing with timers in VBA, and in any case if VBA is being used, then best to do it simply and 'properly'... – MacroMarc Oct 19 '15 at 00:47
  • Thanks for your help. I will do this using a macro as it does seem to be the best way to do it. – deathismyfriend Oct 19 '15 at 00:49
  • @ExcelHero Got it to work thanks for the help. If you want to give a quick short answer I will accept it since you led me to the answer – deathismyfriend Oct 19 '15 at 00:53

1 Answers1

0

The problem here is that you are attempting to have a UDF change the value of cells other than the cell that called it.

By design, this is not allowed.

Instead of a UDF, you should use a Sub.

Excel Hero
  • 14,253
  • 4
  • 33
  • 40