Comm. between Excel and MC ,Usefull MC & Excel functions
Posted: 21 Jul 2011
Hello
I'm using XLInputs , a more updated version of ELExcel.dll to send data from MC to Excel.
I opted to transmit one big comma separated string that content all the information that i want to sent.
Ex:
Datastring = 1.43910,1.43945,1.43905,1.43910,1.43875,1.43965,1.43810,1.43905,1.43430,1.43070,1.43545,1.43500,10:27,-,0,0.0000,0
Is the informations of
- the actual bar OHLC
- The Previous bar
- Last to Pivot High
-Last Two Pivot Low
- Actual Position string (Long, Short, - )
- Actual Position size
etc
Once in Excel, I wrote a function to place all the value to is appropriate cell.
Basically, you call the function with a string and the position of the wanted information
Cell actuallow = SplitFunction(Datastring,3) 3 representing the value of the actual low of the active bar.
Cell ActualLow = 1.43905.
** I named all my cell. But it could be cell (B10)
Install the function in a module. It would be accessible for your vba code and directly on your sheet cell formula
I'm using XLInputs , a more updated version of ELExcel.dll to send data from MC to Excel.
I opted to transmit one big comma separated string that content all the information that i want to sent.
Ex:
Datastring = 1.43910,1.43945,1.43905,1.43910,1.43875,1.43965,1.43810,1.43905,1.43430,1.43070,1.43545,1.43500,10:27,-,0,0.0000,0
Is the informations of
- the actual bar OHLC
- The Previous bar
- Last to Pivot High
-Last Two Pivot Low
- Actual Position string (Long, Short, - )
- Actual Position size
etc
Once in Excel, I wrote a function to place all the value to is appropriate cell.
Basically, you call the function with a string and the position of the wanted information
Cell actuallow = SplitFunction(Datastring,3) 3 representing the value of the actual low of the active bar.
Cell ActualLow = 1.43905.
** I named all my cell. But it could be cell (B10)
Install the function in a module. It would be accessible for your vba code and directly on your sheet cell formula
Code: Select all
Public Function SplitString(ByVal Text1 As String, WordPos As Long) As String
' EXCEL VBA SplitString FUNCTION
'By Martin Thériault
'July 21 2011
Dim CommaPos As Long
Dim LeftBorder, RightBorder As Long
Dim WordCounter As Long
Dim tempstring As String
Dim TestString As String
Dim pos As Long
pos = WordPos
CommaPos = 0
WordCounter = pos
'LeftBorder
If InStr(1, Text1, ",") <> 0 Then
While WordCounter - 1 <> 0
CommaPos = InStr(CommaPos + 1, Text1, ",")
pos = pos - 1
WordCounter = WordCounter - 1
Wend
LeftBorder = CommaPos + 1
If InStr(LeftBorder + 1, Text1, ",") <> 0 Then
RightBorder = InStr(LeftBorder + 1, Text1, ",")
Else
RightBorder = 9999
End If
Else
LeftBorder = 1
RightBorder = 9999
End If
SplitString = Mid(Text1, LeftBorder, (RightBorder - LeftBorder))
End Function