newbie question-

[Bob111]

Hello everyone! I’m new on this forum and new to excel. I do have couple questions about macros in VBA and I hope that someone maybe will have couple minutes to help me or point to the right direction, if such problems been mentioned somewhere on this forum already.

Here is what I have.
I was subscriber for stocks real time market data, which was delivered via DDE. Basically-to receive data, all you have to do is type in cell following formula-
=MLINK|MFDC!'MSFT,LAST'

Then, as I understand, link was established and every time last price was changed-it will be change in cell to.
Number of current links is limited to 300. Than mean, you cannot receive more than 300 quotes on different stocks at same time. I don’t need a real time data, but I want to create a snapshot for about 1000-3000 stocks. So-as I understand, I need to create links for first 300 symbols, get data, write it into file, and then change formula in those cells to next 300 symbols and so on.
Here is what I wrote so far-

Public Type OC

Symbol As String
UpdateTime As Date
UpdateStatus As Integer

End Type

Public AllSymb() As OC


Sub ReqData()
Dim i As Integer
Dim FileNum As Integer
Dim LineCount As Integer
Dim One_Line As String


LineCount = 0

FileNum = FreeFile

Open "C:\NASDAQ\NAZ_ALL.txt" For Input As FileNum

Do While Not EOF(FileNum)

Line Input #FileNum, One_Line

LineCount = LineCount + 1

Loop

Close FileNum

ReDim AllSymb(LineCount - 1)

LineCount = 0

FileNum = FreeFile

Open "C:\NASDAQ\NAZ_ALL.txt" For Input As FileNum

Do While Not EOF(FileNum)

Line Input #FileNum, One_Line

One_Line = Trim(One_Line)

AllSymb(LineCount).Symbol = Trim(One_Line)


'Debug.Print AllSymb(LineCount)

LineCount = LineCount + 1




Loop

Close FileNum



For i = 1 To 200


Cells(i, 1) = UCase(AllSymb(i).Symbol)

AllSymb(i).UpdateStatus = 2

Cells(i, 2).Formula = "=MLINK|MFDC!'" & UCase(AllSymb(i).Symbol & ",TRADE_DATE'")

Cells(i, 3).Formula = "=MLINK|MFDC!'" & UCase(AllSymb(i).Symbol & ",OPEN_PRICE'")

Cells(i, 4).Formula = "=MLINK|MFDC!'" & UCase(AllSymb(i).Symbol & ",BIDSIZE'")

Cells(i, 5).Formula = "=MLINK|MFDC!'" & UCase(AllSymb(i).Symbol & ",BID'")

Cells(i, 6).Formula = "=MLINK|MFDC!'" & UCase(AllSymb(i).Symbol & ",ASK'")

Cells(i, 7).Formula = "=MLINK|MFDC!'" & UCase(AllSymb(i).Symbol & ",ASKSIZE'")

Cells(i, 8).Formula = "=MLINK|MFDC!'" & UCase(AllSymb(i).Symbol & ",LAST'")

Cells(i, 9).Formula = "=MLINK|MFDC!'" & UCase(AllSymb(i).Symbol & ",TRADE_VOL'")

Cells(i, 10).Formula = "=MLINK|MFDC!'" & UCase(AllSymb(i).Symbol & ",TRADE_TIME'")

Cells(i, 11).Formula = "=MLINK|MFDC!'" & UCase(AllSymb(i).Symbol & ",ACVOL'")


Next




End Sub

This sub is open file with tickers, load them into AllSymb() array and then-request data for first 300 symbols.
Now-I have to change those symbols somehow, after I receive data for first 300.
I have no idea, how to do it properly and if someone can recommend something-please do. Here is my lame solution-
In cell next total volume of shares traded, I did put 1, and in nest cell I wrote-if value in volume cell > that value in cell with number 1(which is always true)-then-call my function, which will write data into file and change links in row for another symbol.
Here is the function-

Public Function Update_Fnc(ByVal Location As String)

Dim sCell As String
Dim sRow As Integer
Dim sRecord As String
Dim Ticker As String
Dim FileNum As Integer
Dim Temp As String
Dim TempData As String
Dim i As Integer
Dim TempTime As Date
Dim Test As String

FileNum = FreeFile





sCell = Mid(Application.Caller.Address, 2, 1)

Temp = Application.Caller.Address

sRow = Mid(Temp, 3, Len(Temp) - 2)


If Cells(sRow, 2) <> "#WAIT" And Cells(sRow, 3) <> "#WAIT" And Cells(sRow, 4) <> "#WAIT" And Cells(sRow, 5) <> "#WAIT" And Cells(sRow, 6) <> "#WAIT" And Cells(sRow, 7) <> "#WAIT" And Cells(sRow, 7) <> "#WAIT" And Cells(sRow, 8) <> "#WAIT" And Cells(sRow, 9) <> "#WAIT" And Cells(sRow, 10) <> "#WAIT" And Cells(sRow, 11) <> "#WAIT" Then

sRecord = Cells(sRow, 2) & "," & Cells(sRow, 3) & "," & Cells(sRow, 4) & "," & Cells(sRow, 5) & "," & Cells(sRow, 6) & "," & Cells(sRow, 7) & "," & Cells(sRow, 8) & "," & Cells(sRow, 9) & "," & Cells(sRow, 10) & "," & Cells(sRow, 11)

Else

Exit Function

End If

Ticker = Cells(sRow, 1)

Test = Application.ActiveCell(sRow, 1).Value


TempData = Sheet1.Cells(sRow, 11)

Open "D:\Test\MFEED\" & Ticker & ".txt" For Append As #FileNum

Print #FileNum, sRecord


Close #FileNum


For i = LBound(AllSymb) To UBound(AllSymb)

If AllSymb(i).Symbol = Ticker Then

AllSymb(i).UpdateStatus = 1

AllSymb(i).UpdateTime = Format(Now, "H:M:SS")

TempTime = Format(Now, "H:M:SS")

Exit For

End If


Next


For i = LBound(AllSymb) To UBound(AllSymb)

If AllSymb(i).UpdateStatus = 2 And AllSymb(i).UpdateTime < TempTime Or AllSymb(i).UpdateStatus = 0 Then


ActiveSheet.Cells(sRow, 1) = UCase(AllSymb(i).Symbol) -------function exit here for some reason

ActiveSheet.Cells(sRow, 2).Formula = "=MLINK|MFDC!'" & UCase(AllSymb(i).Symbol & ",TRADE_DATE'")

ActiveSheet.Cells(sRow, 3).Formula = "=MLINK|MFDC!'" & UCase(AllSymb(i).Symbol & ",OPEN_PRICE'")

ActiveSheet.Cells(sRow, 4).Formula = "=MLINK|MFDC!'" & UCase(AllSymb(i).Symbol & ",BIDSIZE'")

ActiveSheet.Cells(sRow, 5).Formula = "=MLINK|MFDC!'" & UCase(AllSymb(i).Symbol & ",BID'")

ActiveSheet.Cells(sRow, 6).Formula = "=MLINK|MFDC!'" & UCase(AllSymb(i).Symbol & ",ASK'")

ActiveSheet.Cells(sRow, 7).Formula = "=MLINK|MFDC!'" & UCase(AllSymb(i).Symbol & ",ASKSIZE'")

ActiveSheet.Cells(sRow, 8).Formula = "=MLINK|MFDC!'" & UCase(AllSymb(i).Symbol & ",LAST'")

ActiveSheet.Cells(sRow, 9).Formula = "=MLINK|MFDC!'" & UCase(AllSymb(i).Symbol & ",TRADE_VOL'")

ActiveSheet.Cells(sRow, 10).Formula = "=MLINK|MFDC!'" & UCase(AllSymb(i).Symbol & ",TRADE_TIME'")

ActiveSheet.Cells(sRow, 11).Formula = "=MLINK|MFDC!'" & UCase(AllSymb(i).Symbol & ",ACVOL'")

AllSymb(i).UpdateStatus = 2






End If



Next



End Function


Up to the point, where I wrote “function exit here”-everything works just fine.
So –here my question number one-why it exits there with no sign of error or something? My guess that I wrote something wrong, but I do know how to write it properly.
Can you show mw, how and what I should write, to change link?
Or (second question)-maybe you can explain, how to do it right way from beginning?
Basically-all I need, if to call function or sub, which will change link, every time, when value in any row for particular symbol is changed.

Thank you!

 

[erik.van.geit]

Hello, Bob111,

WELCOME to the Board!

Newbie ?
You write code like you type a letter! :wink:

You're unlikely to get response, since it's a long question and your code is rather difficult to read.

Could you
1. cut a bit in your code so the non relevant parts wouldn't be there
2. before posting select your code and click on the "Code"-button

You say :

exits withour error

Is it really exiting on that line and not on the one above If ... = 2 And ... < TempTime Or ... = 0 Then
it seems impossible that just writing to a sheet can stop the code from running ... unless you've got some worksheet_change code in your sheetmodule with END

If AllSymb(i).UpdateStatus = 2 And AllSymb(i).UpdateTime < TempTime Or AllSymb(i).UpdateStatus = 0 Then 
ActiveSheet.Cells(sRow, 1) = UCase(AllSymb(i).Symbol) -------function exit here for some reason

TIP:
take a look at the "Debug" menu in your VBEditor
==> steeping through your code with F8, setting breakpoints with F9, ...

can this give you a start?

kind regards,
Erik