Can U spot my error combining code from 2 subs into 1 ??

[JoBlink]

Hi!

I have 2 subs in excel VBA module.
OBJECTIVE: combine code into one Sub / Function

First sub
DOES: CONTINUOUSLY monitor DDE updates with LinkOnData method,
DOES NOT: pass link strings to another sub

Second sub
DOES: Pass link strings to anoter sub
DOES NOT: monitor links contuinuously, only runs one cycle and stops.

Here is the code:

-------------------------------
Sub FitstSub()

Links = ActiveWorkbook.LinkSources(xlOLELinks)
If Not IsEmpty(Links) Then
For i = 1 To UBound(Links)
ActiveWorkbook.SetLinkOnData Links(i), "CallMyName"
'"CallMyName" is a sub that needs values of Links(i)
'Sub is called again and again,but values are not passed
Next i
Else
End If
End Sub

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

Sub SecondSub()

Links = ActiveWorkbook.LinkSources(xlOLELinks)
If Not IsEmpty(Links) Then
For i = 1 To UBound(Links)
ActiveWorkbook.SetLinkOnData Links(i), CallMyName(i)
'"CallMyName" is a sub that needs values of Links(i)
'Here I change receiving Sub to a Function NameX(i)
' it gets values on the first run, then stops
'If I try using "CallMyName(i)" I get error: Macro 'CallMyName(i) cannot be found
Next i
Else
End If
End Sub

 

[PHV]

Sub Name1
CallMyName(1)
End Sub
...
Sub NameN
CallMyName(N)
End Sub

SubThirdSub
Links = ActiveWorkbook.LinkSources(xlOLELinks)
If Not IsEmpty(Links) Then
For i = 1 To UBound(Links)
ActiveWorkbook.SetLinkOnData Links(i), "Name" & i
Next i
End If
End Sub

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[JoBlink]

Dear PHV,

Your solution works, but implementing it will be very inefficient in my case.

Links(i) are DDE query strings, containgin subject of a query and parameter to be returned. There are 100 subjects, with 7 parameters each.
If I did as you suggested, I'd need to write 700 subs.

What I wanted is to take these Links(i) query strings as strings, separate subject and parameter using Mid() function, assign subject and parameter to tempVariables and write concatenated comands to append them to appropriate places.
TO CREATE CONCATENATED COMMANDS, I NEED TO PASS THE VALUE OF A STRING TO A SUB/FUNCTION.

Do you see the point I am trying to make?
Can you think of a way to pass the value of Links(i) or just (i) to Sub NameN???

 

[PHV]

By writing the values in cells in a hidden sheet ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[MiggyD]

Have you tried making any global/public variables from Sub Main? or in the declares section?

--MiggyD

 

[JoBlink]

HI!

I declared all variables outside of subs. I changed
Dim Links as Variant
to
Global Links as Variant

I still get error due to the problem with the Links and "i" variables: either "type mismatch" or "subscript out of range" It means I failed to pass the value.

Here is the catch:

Links is a Variant Array where "i" is the number of links. "i" equals (SubjectsOneToHundred x ParametersOneToSeven) = 700.
I need to pass the value of Links, which is is something like =ServerName|SubjectOneToHundred!ParameterOneToSeven, to CallMyName sub which separates SubjectOne.. and ParameterOne using Mid() and InStr()
Then I have seven subs for each of the parameters. I write them in separate worksheets so I can see chronological history of updates, not just the latest one.

The whole point here is to create chonology by copying and pasting values after each update.
For now I can only see the last update.

 

[JoBlink]

Still looking for help on calling procedure using SetLinkOnData and passing a variable to it.

Help will be greatly appreciated.


I found 2 references: on passing a parameter between subroutines and on caling procedures.

NONE OF THEM WORKS WITH SetLinkOnData method.
EIther I get "Type Mismatch" error trying to assign variant value to string
OR
Method SetLinkOnData failed
OR
Expected: =
Etc, etc.

PASSING VARIABLE EXAMPLE
Sub pro1()
Dim var1 As Integer
var1 = 33
Call pro2(var1)
End Sub
Sub pro2(var1)
Range("A1").Value = var1
End Sub


CALL PROCEDURE SYNTAX:

Call MyProc(firstarg, secondarg)
MyProc firstarg, secondarg
Notice that the parentheses are omitted in the call when the Call statement isn't used.