User Defined Functions

[mosdwt]

I am trying to write a user defined function which returns a value to the cell in which it entered based on a particular formula. Before the function returns this value I want to write a number or perhaps text to a different cell on the same worksheet but the function cell keeps coming up with the error "VALUE". Can someone tell me why this is?

 

[SydneyGeek]

If you are creating a user-defined function, it can *only* return a value to the target cell -- the one containing the function. You can't use functions to populate other cells, it's not how they work.

For example, MyFunction() in C4 will return a value to C4. You cannot make the MyFunction() in C4 simultaneously do something to F4.

Denis

 

[mosdwt]

Thanks for your response. I thought that this may be answer as I can use the same code which does not work in the function but does work from a Macro. I just needed some confirmation.

 

[Legacy 98055]

There is a hack available that I have used in the past...
Download the example that contains the following function that takes two arguments. A range or some other variant containing a number, and a destination range. The function simply multiplies the first argument and then writes it to the destination range.*

mosdwt.zip

Place this code in a standard module...
<table width="100%" border="1" bgcolor="White" style="filterrogid:DXImageTransform.Microsoft.Gradient(endColorstr='#C0CFE2', startColorstr='#FFFFFF', gradientType='0');"><tr><TD><font size="2" face=Courier New>  <font color="#0000A0">Private</font> <font color="#0000A0">Declare</font> <font color="#0000A0">Function</font> SetTimer <font color="#0000A0">Lib</font> "user32" ( _
       <font color="#0000A0">ByVal</font> HWnd <font color="#0000A0">As</font> Long, <font color="#0000A0">ByVal</font> nIDEvent <font color="#0000A0">As</font> Long, _
       <font color="#0000A0">ByVal</font> uElapse <font color="#0000A0">As</font> Long, <font color="#0000A0">ByVal</font> lpTimerFunc <font color="#0000A0">As</font> Long) <font color="#0000A0">As</font> <font color="#0000A0">Long</font>
  <font color="#0000A0">Private</font> <font color="#0000A0">Declare</font> <font color="#0000A0">Function</font> KillTimer <font color="#0000A0">Lib</font> "user32" ( _
       <font color="#0000A0">ByVal</font> HWnd <font color="#0000A0">As</font> Long, <font color="#0000A0">ByVal</font> nIDEvent <font color="#0000A0">As</font> Long) <font color="#0000A0">As</font> <font color="#0000A0">Long</font>

  <font color="#0000A0">Private</font> pTimerID <font color="#0000A0">As</font> <font color="#0000A0">Long</font>
  <font color="#0000A0">Private</font> pDestination <font color="#0000A0">As</font> Range
  <font color="#0000A0">Private</font> pArg

  <font color="#0000A0">Public</font> <font color="#0000A0">Function</font> MyFunction(arg, Destination <font color="#0000A0">As</font> Range)
       <font color="#0000A0">Set</font> pDestination = Destination
       pArg = arg * 2
       pTimerID = SetTimer(0&, 0&, 1, <font color="#0000A0">AddressOf</font> TimerProc)
  <font color="#0000A0">End</font> <font color="#0000A0">Function</font>

  <font color="#0000A0">Private</font> <font color="#0000A0">Sub</font> TimerProc(ByVal HWnd <font color="#0000A0">As</font> Long, <font color="#0000A0">ByVal</font> uMsg <font color="#0000A0">As</font> Long, _
       <font color="#0000A0">ByVal</font> nIDEvent <font color="#0000A0">As</font> Long, <font color="#0000A0">ByVal</font> dwTimer <font color="#0000A0">As</font> Long)
       <font color="#0000A0">On</font> <font color="#0000A0">Error</font> <font color="#0000A0">Resume</font> <font color="#0000A0">Next</font>

       KillTimer 0&, pTimerID
       pDestination = pArg
  <font color="#0000A0">End</font> <font color="#0000A0">Sub</font>
</FONT></td></tr></table><button onclick='document.all("94200635619718").value=document.all("94200635619718").value.replace(/<br \/>\s\s/g,"");document.all("94200635619718").value=document.all("94200635619718").value.replace(/<br \/>/g,"");window.clipboardData.setData("Text",document.all("94200635619718").value);'>Copy to Clipboard</BUTTON><textarea style="position:absolute;visibility:hidden" name="94200635619718" wrap="virtual">
Private Declare Function SetTimer Lib "user32" ( _
ByVal HWnd As Long, ByVal nIDEvent As Long, _
ByVal uElapse As Long, ByVal lpTimerFunc As Long) As Long
Private Declare Function KillTimer Lib "user32" ( _
ByVal HWnd As Long, ByVal nIDEvent As Long) As Long

Private pTimerID As Long
Private pDestination As Range
Private pArg

Public Function MyFunction(arg, Destination As Range)
Set pDestination = Destination
pArg = arg * 2
pTimerID = SetTimer(0&, 0&, 1, AddressOf TimerProc)
End Function

Private Sub TimerProc(ByVal HWnd As Long, ByVal uMsg As Long, _
ByVal nIDEvent As Long, ByVal dwTimer As Long)
On Error Resume Next

KillTimer 0&, pTimerID
pDestination = pArg
End Sub</textarea>

*Tusharm does not endorse this method. Well, he probably doesn't...

mosdwt.zip