Assigning value to a cell in a function

Question

I am writing a code where one of the lines( Range("H5").value = 10 ) should assign a specific value to a specific cell. However it gives me an #VALUE! error on my cell. Here is my code please help!!:

Function TotalAdUnits(Money As Currency, CycleEarning As Integer) As Integer

 TapCost = 16
 TotalAdUnits = 0
 AdUnit = 0
 i = 1

 Money = Money + CycleEarning

 Do While (Money >= TapCost)

  Money = Money - TapCost
  TotalAdUnits = TotalAdUnits + Range("L1").Offset(i, 0)
  TapCost = Range("J2").Offset(i, 0)

  Range("H5").value = 10

  i = i + 1

 Loop

End Function

You cannot set a cells value from a UDF - a function used in this way can only return a value to the calling cell (or range, if it's used as an array function). Edit your function to return the value, instead of trying to set it directly. — Tim Williams, Jul 21 '12 at 21:24
@TimWilliams It can actually be done via a function if you are hell-bent on it. Complex but doable, http://stackoverflow.com/a/9493006/641067 — brettdj, Jul 22 '12 at 02:13

score 1 · Answer 1 · answered Jul 21 '12 at 23:06

You cannot set a cells value from a UDF - a function used in this way can only return a value to the calling cell (or range, if it's used as an array function). Edit your function to return the value, instead of trying to set it directly.

Having said that, I'm not clear on exactly what you're trying to do with that function. Why set the same value in a loop?

Also, if you reference ranges directly from your function, and those ranges are not passed as parameters, you need to include Application.Volatile in your function so Excel knows to recalculate it any time the sheet changes.

Function TotalAdUnits(Money As Currency, CycleEarning As Integer) As Integer

 TapCost = 16
 TotalAdUnits = 0
 AdUnit = 0
 i = 1

 Money = Money + CycleEarning

 Do While (Money >= TapCost)

     Money = Money - TapCost
     TotalAdUnits = TotalAdUnits + Range("L1").Offset(i, 0)
     TapCost = Range("J2").Offset(i, 0)

     Range("H5").value = 10

     i = i + 1

 Loop

 'need to return a value...
 TotalAdUnits = 10 '?
End Function

Actually, what I meant was for that line the following: Range("H5").value = Range("J2").Offset(i, 0) — user1543143, Jul 22 '12 at 01:37

Assigning value to a cell in a function

1 Answers1