setlinkondata and application.caller help

[Ross]

Hi there,

I have the first code in the "Sheets1" code block, and the next in a
general Module. The setLinkOnData seems to work as when I manually
update the DDE link I do not get the beeps signifying that it's
running the first sub. When I step through the second one, it crashes
with Error 2023 at
"set tgt=application.caller"

If anyone can tell me how to get application.caller to work or else
how to pass the range as a parameter between the subs, I'd appreciate
it!

Thanks
Ross


Sub handleDDE()

aLinks = ActiveWorkbook.LinkSources(xlOLELinks)
If Not IsEmpty(aLinks) Then
For i = 1 To UBound(aLinks)
'MsgBox "Link " & i & ":" & Chr(13) & aLinks(i) &
i.Offset(0, -4).Value
If Right(aLinks(i), 4) = ".n;3" Then
Beep
Beep
ThisWorkbook.SetLinkOnData aLinks(i), "PriceCellFound"

End If
Next i
End If
End Sub


---------------------------------

Sub PriceCellFound()
Dim tgt As Range
Dim r As Range
Dim c As Range

Set tgt = Application.Caller
MsgBox ("got this far")
On Error GoTo ErrHandler
Application.EnableEvents = False

[some other stuff]

ErrHandler:
Application.EnableEvents = True
End Sub

 

[Dave Peterson]

You're trying to set tgt to the calling cell. But when you're debugging the
code (you started via F8/F5???), there is no cell that called the procedure.

You could use some kind of error trapping code:

If TypeOf Application.Caller Is Range Then
set tgt = application.caller
Else
set tgt = activecell
End If

Maybe just assign your code to the active cell (or your favorite cell):

Hi there,

I have the first code in the "Sheets1" code block, and the next in a
general Module. The setLinkOnData seems to work as when I manually
update the DDE link I do not get the beeps signifying that it's
running the first sub. When I step through the second one, it crashes
with Error 2023 at
"set tgt=application.caller"

If anyone can tell me how to get application.caller to work or else
how to pass the range as a parameter between the subs, I'd appreciate
it!

Thanks
Ross

Sub handleDDE()

aLinks = ActiveWorkbook.LinkSources(xlOLELinks)
If Not IsEmpty(aLinks) Then
For i = 1 To UBound(aLinks)
'MsgBox "Link " & i & ":" & Chr(13) & aLinks(i) &
i.Offset(0, -4).Value
If Right(aLinks(i), 4) = ".n;3" Then
Beep
Beep
ThisWorkbook.SetLinkOnData aLinks(i), "PriceCellFound"

End If
Next i
End If
End Sub

---------------------------------

Sub PriceCellFound()
Dim tgt As Range
Dim r As Range
Dim c As Range

Set tgt = Application.Caller
MsgBox ("got this far")
On Error GoTo ErrHandler
Application.EnableEvents = False

[some other stuff]

ErrHandler:
Application.EnableEvents = True
End Sub