none
excel vba does not show errors in vsto workbook project in c# RRS feed

  • Question

  • i have created a C# project of excel 2007 workbook. in which i'm calling code from C# to VBA and From VBA to C#. at some points the vba code is either not working or the excel collapses, and there is no error massage. for example if i will run a normal vba code in regular workbook(not vsto project), like so:

    sub test()
       cells(i,5)=3
    end sub

    and i is not set i will get an error from vba but if i will do the exact same mistake in a workbook that is from the project, the vba either skip this line or crushes

    now, because i don't know where the problem lies (in the C# or in the VBA) i cant fix it...

    how can i make the vba show me the alerts in the project? and how can i make sure the excel will not collapse?

    (i have tried the "OnError GOTO" but it does not do anything)

    Monday, July 6, 2015 6:02 PM

All replies

  • Hi talbaget,

    What do you mean “OnError GOTO” does not do anything? What’s the result if you return the value in the error handler and check the returned value in your .net project?

    On the other hand, what do you mean excel collapses? Please provide the detail information.

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, July 7, 2015 9:02 AM
    Moderator
  • hi

    when a VBA sub is called from the C# (automatically get response from server) then if the value is not set, or if i have a division by zero, it just skip it and does not show me any error, or make the excel collapses(stop working and need to restart).

    if i try put the “OnError GOTO” in the sub of the vba it does not GOTO anywhere it just ignore it.

    i have a workbook that receives data automatically every sec from the C# project, or another workbook that sends parameters at a certain point (VBA calls a C# routine and the C# send it to the server), and then receive from that server (C# gets feedback from server and calls VBA with those values that then put them on the excel sheet).

    somewhere (sometimes) on those actions something get wrong and the excel stop working and i need to start it again.

    i want to pin point where the ERR is occurring so i can fix it, but if the VBA don't stop on errors then i cant know what has happened.

    Tuesday, July 7, 2015 11:39 AM
  • Hi talbaget,

    Base on my test, it works fine, and I can get the returned value.

    Public Function VBAWithError() As String
     On Error GoTo MyErrorHandler
     Cells(i, 5) = 3
     Exit Function
    MyErrorHandler:
     VBAWithError = "error occur"
    End Function
    
     public void CallExcel()
           {
               object oMissing = System.Reflection.Missing.Value;
               Excel.Application oExcel = new Excel.Application();
               oExcel.Visible = false;
               Excel.Workbooks oBooks = oExcel.Workbooks;
               Excel._Workbook workbook = oBooks.Open(@"XX\VBAWithNet.xlsm"); 
               RunMacro(oExcel, new object[] { "VBAWithError" });
               oBooks.Close();
               System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
               workbook = null;
               System.Runtime.InteropServices.Marshal.ReleaseComObject(oBooks);
               oBooks = null;
               oExcel.Quit();
               System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcel);
               oExcel = null;
    
           }
           private void RunMacro(object oApp,object[] oRunArgs)
           {
              object o=  oApp.GetType().InvokeMember("Run", System.Reflection.BindingFlags.Default | System.Reflection.BindingFlags.InvokeMethod, null, oApp, oRunArgs);
    
           }


    On the other hand, I can add the breakpoint in the VBA code, it could triggers the breakpoint when I debug .net application in VS. Please try to add the breakpoint and try again. 

    If you still have the issue, please share the sample project on the OneDrive that could reproduce that issue.

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, July 8, 2015 3:29 AM
    Moderator
  • hi

    i have build an office project like so:file->new->project->visual C#->office->2007->excel 2007 workbook.

    and in that way i do not open the vs only the excel workbook.

    while as i understand you are opening a new excel object every run, throw the vs.

    also my code uses .com references from the broker api and running all the time and gets data automatically.

    this is my C# code of Sheet1.cs

    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Linq;
    using System.Text;
    using System.Windows.Forms;
    using System.Xml.Linq;
    using Microsoft.Office.Tools.Excel;
    using Microsoft.VisualStudio.Tools.Applications.Runtime;
    using Excel = Microsoft.Office.Interop.Excel;
    using Office = Microsoft.Office.Core;
    using System.Drawing;
    using System.Runtime.CompilerServices;
    
    using TAOrderAPIWrapper;
    //using TAPriceEngineAPIWrapper;
    using TAWireTypesCPPWrapper;
    using Transact.CommonEnums;
    
    
    namespace TraderJul15
    {
        [System.Runtime.InteropServices.ComVisible(true)]
        [System.Runtime.InteropServices.ClassInterface(System.Runtime.InteropServices.ClassInterfaceType.None)]
        public partial class Sheet1 : TraderJul15.ISheet1
        {
            System.Threading.Timer Tim;
            private OrderAPIWrapper _orderAPI;
            private void Sheet1_Startup(object sender, System.EventArgs e)
            {
            }
    
            private void Sheet1_Shutdown(object sender, System.EventArgs e)
            {
            }
    
            protected override object GetAutomationObject()
            {
                return this;
            }
    
            #region VSTO Designer generated code
    
            /// <summary>
            /// Required method for Designer support - do not modify
            /// the contents of this method with the code editor.
            /// </summary>
            private void InternalStartup()
            {
                this.Startup += new System.EventHandler(Sheet1_Startup);
                this.Shutdown += new System.EventHandler(Sheet1_Shutdown);
            }
    
            #endregion
    
    
            public void LogOn(string gateKeeperURL, string vendorID, string user, string password, int apiMode, string ConName)
            {
                enumErrors retCode = enumErrors.ok;
                string ConStatus;
                string ConID = null;
                _orderAPI = new OrderAPIWrapper(gateKeeperURL, vendorID, user, password, (enumAPIMode)apiMode);
                _orderAPI.OnOrderNotification += new OrderAPIWrapper.OrderNotification(_orderAPI_OnOrderNotification);
                _orderAPI.OnConnectionLost += new OrderAPIWrapper.ConnectionLost(_orderAPI_OnConnectionLost);
                _orderAPI.OnAdminMessage += new OrderAPIWrapper.AdminMessage(_orderAPI_OnAdminMessage);
                List<WireAccountWrapper> accounts = _orderAPI.GetAccountList(out retCode);
                if (retCode == enumErrors.ok)
                {
                    if ((retCode = _orderAPI.Logon(accounts[0].accountID)) == enumErrors.ok)
                    {
                        List<WireAccountContractWrapper> contracts = _orderAPI.GetAccountContracts();
                        foreach (WireAccountContractWrapper contract in contracts)
                        {
                            if (contract.Contract.name == ConName)
                            {
                                ConID = contract.Contract.contractID.ToString();
                            }
                        }
                        ConStatus = "Connected";
                    }
                    else
                    {
                        ConStatus = "Validation Error: " + _orderAPI.GetErrorString(retCode);
                    }
                }
                else
                {
                    ConStatus = "Validation Error: " + _orderAPI.GetErrorString(retCode);
                }
                if (ConStatus == "Connected")
                {
                    Globals.Sheet1.Application.Run("Sheet1.ConStat", ConStatus, accounts[0].accountID.ToString(), ConID);
                }
                else
                {
                    updateCell("C7", "Error Connecting: "+ ConStatus, Color.Red);
                }
            }
            void _orderAPI_OnOrderNotification(OrderAPIWrapper sender, WireOrderWrapper order, enumErrors errorCode, enumOrderEvent orderEvent, int contractPosition)
            {
                if (errorCode == enumErrors.ok)
                {
                    //#### orderevent dependency
                    string[] ordNod = new string[20];
                    ordNod[0] = order.orderID.ToString();
                    ordNod[1] = order.originalOrderID.ToString();
                    ordNod[2] = order.orderStatus.ToString();
                    ordNod[3] = order.orderType.ToString();
                    ordNod[4] = order.isBuy.ToString();
                    ordNod[5] = order.filledQuantity.ToString();
                    ordNod[6] = order.remainingQuantity.ToString();
                    ordNod[7] = order.originalQuantity.ToString();
                    ordNod[8] = order.displayPrice.ToString();
                    ordNod[9] = order.displayFillPrice.ToString();
    
                    ordNod[10] = order.sentTime.Hour.ToString();
                    ordNod[11] = order.sentTime.Minute.ToString();
                    ordNod[12] = order.sentTime.Second.ToString();
                    ordNod[13] = order.sentTime.Millisecond.ToString();
    
                    ordNod[14] = order.filledTime.Hour.ToString();
                    ordNod[15] = order.filledTime.Minute.ToString();
                    ordNod[16] = order.filledTime.Second.ToString();
                    ordNod[17] = order.filledTime.Millisecond.ToString();
                    try
                    {
                        Globals.Sheet1.Application.Run("Sheet1.OrdNodOK", ordNod);
                    }
                    catch (Exception ex)
                    {
                        Tim = new System.Threading.Timer(obj => { SendErr(ex.ToString(), "OrdNodOK"); }, null, 1000, System.Threading.Timeout.Infinite);
                    }
                }
                else
                {
                    try
                    {
                        Globals.Sheet1.Application.Run("Sheet1.OrdNodNOT", errorCode.ToString());
                    }
                    catch (Exception ex)
                    {
                        Tim = new System.Threading.Timer(obj => { SendErr(ex.ToString(), "OrdNodNOT"); }, null, 1000, System.Threading.Timeout.Infinite);
                    }
                }
    
    
            }
            public void SendErr(string theErr, string WhereErr)
            {
                Globals.Sheet1.Application.Run("Sheet1.GetErr", theErr, WhereErr);
            }
            void _orderAPI_OnAdminMessage(OrderAPIWrapper sender, string msg)
            {
                //MessageBox.Show("Transact Admin Message" + msg, "Transact System Message");
            }
    
            void _orderAPI_OnConnectionLost(OrderAPIWrapper sender, string reason)
            {
                try
                {
                    Globals.Sheet1.Application.Run("Sheet1.LostCon", reason);
                }
                catch (Exception ex)
                {
                    Tim = new System.Threading.Timer(obj => { SendErr(ex.ToString(), "LostCon"); }, null, 1000, System.Threading.Timeout.Infinite);
                }
            }
            public void PlaceOrder(bool isBuy, int contractID, int orderType, int quantity, int price)
            {
                try
                {
                    WireOrderWrapper order = new WireOrderWrapper();
                    order.isBuy = isBuy;
                    order.contractID = contractID;
                    order.orderType = (enumOrderTypes)orderType;
                    order.quantity = quantity;
                    order.price = price;
                    order.priceFormatCode = enumPriceFormatCode.GlobexQuarterHundredThousandths;
                    order.orderRestriction = enumOrderRestrictions.none;
                    _orderAPI.PlaceOrder(order);
                }
                catch (Exception ex)
                {
                    Tim = new System.Threading.Timer(obj => { SendErr(ex.ToString(), "PlaceOrder"); }, null, 1000, System.Threading.Timeout.Infinite);
                }
            }
            public void LogOff()
            {
                _orderAPI.Logoff();
                _orderAPI.Dispose();
                _orderAPI = null;
                updateCell("C7", "OffLine", Color.Red);
            }
            public void ModOrder(int Ordid, int LmtP, int Qty)
            {
                try
                {
                    int StP = 0;
                    _orderAPI.ModifyOrder(Ordid, LmtP, StP, Qty);
                }
                catch (Exception ex)
                {
                    Tim = new System.Threading.Timer(obj => { SendErr(ex.ToString(), "ModOrder"); }, null, 1000, System.Threading.Timeout.Infinite);
                }
            }
            public void UpdateOrderStatus()
            {
                var workingorders = _orderAPI.GetWorkingOrders();
                string[] WorkOrd = new string[2];
                int i = 1;
                foreach (WireOrderWrapper order in workingorders)
                {
                    WorkOrd[i] = order.ToString();
                    i++;
                }
                try
                {
                    Globals.Sheet1.Application.Run("Sheet1.UpdateStat", WorkOrd);
                }
                catch (Exception ex)
                {
                    Tim = new System.Threading.Timer(obj => { SendErr(ex.ToString(), "UpdateStat"); }, null, 1000, System.Threading.Timeout.Infinite);
                }
            }
            public void CancelAllOrders()
            {
                _orderAPI.CancelAll();
            }
            
            public void GetPos(int Sym)
            {
                try
                {
                    WireAccountContractWrapper wireAccountContactWrapper = _orderAPI.GetAccountContract(Sym);
                    Globals.Sheet1.Application.Run("Sheet1.AccPos", wireAccountContactWrapper.position, wireAccountContactWrapper.pnlInTicks);
                }
                catch (Exception ex)
                {
                    Tim = new System.Threading.Timer(obj => { SendErr(ex.ToString(), "AccPos"); }, null, 1000, System.Threading.Timeout.Infinite);
                }
            }
            public void KeepAlive()
            {
                if (!_orderAPI.IsConnected())
                {
                    try
                    {
                        Globals.Sheet1.Application.Run("Sheet1.LostCon", "KeepAlive Found DisConnection");
                    }
                    catch (Exception ex)
                    {
                        Tim = new System.Threading.Timer(obj => { SendErr(ex.ToString(), "LostCon"); }, null, 1000, System.Threading.Timeout.Infinite);
                    }
                }
            }
    
    
            ///////////////////    EXCEL      /////////////////////////////////
    
            //update cell helper method
            private void updateCell(string cell, string value, Color color)
            {
                try
                {
                    Excel.Range range = this.Application.get_Range(cell);
                    if (value != null)
                        range.Value = value;
    
                    if (color != null)
                        range.Interior.Color = color;
                }
                catch
                {
                    //writeToErrorList("updateCell", ex.Message.ToString());
                }
            }
        }
    
    }
    

    and the code of ISheet1.cs

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    
    namespace TraderJul15
    {
        [System.Runtime.InteropServices.ComVisible(true)]
        public interface ISheet1
        {
            void PlaceOrder(bool isBuy, int contractID, int orderType, int quantity, int price);
            void LogOn(string gateKeeperURL, string vendorID, string user, string password, int apiMode, string ConName);
            void LogOff();
            void ModOrder(int Ordid, int LmtP, int Qty);
            void UpdateOrderStatus();
            void CancelAllOrders();
            void GetPos(int Sym);
            void KeepAlive();
        }
    }
    

    my VBA code in modules

    Public actionline As Integer
    Public actionCol As Integer
    Public closeCol As Integer
    Public in_tran As Double
    Public day_started As Integer
    Public modL As Integer
    
    Public Const gateKeeperURL = *********************
    Public Const vendorID = *******************
    
    Public lmt_modP As Double
    Public delayLine As Double
    Public Const delayCol = 18
    Public price_diff As Double
    Public num_of_mod As Integer
    
    Public username As String
    Public pass As String
    Public LogMode As Long
    Public ContName As String
    
    Public RTD_DDE As VbMsgBoxResult
    Public tran_0_1_2 As Integer
    Public inf_counter As Integer
    
    Public Const WAVFileBuy = "C:\Users\Administrator\FTP\Trader\buy.WAV"
    Public Const WAVFileSell = "C:\Users\Administrator\FTP\Trader\sell.WAV"
    
    Public tranP As Integer
    Public tmpRow As Integer
    Public SentTime As String
    Public errLine As Integer
    Public VbaerrLine As Integer
    
    Public Declare Function PlaySound Lib "winmm.dll" _
      Alias "PlaySoundA" (ByVal lpszName As String, _
      ByVal hModule As Long, ByVal dwFlags As Long) As Long
    
    Public Sub StartDay_Click()
        LogIn.Show
        Application.OnTime Now + TimeValue("00:00:10"), "startday"
    End Sub
    Public Sub LogIntoAccount(ByVal usern As String, ByVal pas As String)
        ContName = Cells(11, 1).Value
        tmpRow = 40
        Dim VSTOSheet1 As TraderJul15.Sheet1
        Set VSTOSheet1 = GetManagedClass(Sheet1)
        Call VSTOSheet1.LogOn(gateKeeperURL, vendorID, usern, pas, LogMode, ContName)
    End Sub
    Sub startday()
        Dim startDeci
        Get_Time_Zone
        Get_Roots
        startDeci = MsgBox("Is the alg runnig? (Click Yes)" & vbCrLf & _
                            "If not and you want to proceed without connection (Click no)" & vbCrLf & _
                            "Otherwise Click Cancel to abort", vbYesNoCancel)
        If startDeci = vbCancel Then Exit Sub
        If startDeci = vbYes Then
            Cells(11, 4) = InputBox("Enter The Quantity for the day:")
            Activate_cells
        End If
        Application.OnTime Now + TimeValue("00:00:02"), "ActivateDay"
        Application.OnTime Now + TimeValue("00:03:00"), "CallKeepAlive"
    End Sub
    Sub ActivateDay()
        'delayLine = 30
        tranP = 6
        VbaerrLine = Range("R" & Rows.Count).End(xlUp).Row + 1
        errLine = Range("H" & Rows.Count).End(xlUp).Row + 1
        modL = Range("M" & Rows.Count).End(xlUp).Row + 1
        actionline = 6
        actionCol = 11
        closeCol = 13
        inf_counter = 0
        For alg_r = actionline To actionline + 11
            Cells(alg_r, actionCol - 2).Formula = "='Alg Con'!A" & alg_r - 3
            Cells(alg_r, actionCol - 1).Formula = "='Alg Con'!B" & alg_r - 3
            Cells(alg_r, actionCol).Formula = "='Alg Con'!C" & alg_r - 3
            Cells(alg_r, actionCol + 1).Formula = "='Alg Con'!D" & alg_r - 3
            Cells(alg_r, closeCol).Formula = "='Alg Con'!J" & alg_r - 3
            Cells(alg_r, closeCol + 1).Formula = "='Alg Con'!K" & alg_r - 3
            Cells(alg_r, closeCol + 2).Formula = "='Alg Con'!L" & alg_r - 3
            Cells(alg_r, closeCol + 3).Formula = "='Alg Con'!T" & alg_r - 3
        Next alg_r
        'price_diff = 0
        Do While Cells(actionline, actionCol) <> 0
            actionline = actionline + 1
        Loop
        If Cells(actionline - 1, closeCol) <> 0 Then
            in_tran = 0
        Else
            actionline = actionline - 1
            in_tran = 1
        End If
        Application.OnTime Now + TimeValue("00:00:02"), "ActivateOnChange"
    
    End Sub
    Sub Activate_cells()
        Dim dateOrder As Integer     '0 for month/day/year, probably 1 for day/month/year
        dateOrder = Application.International(xlDateOrder)
        Dim suffix As String
        If dateOrder = 0 Then
            dMonth = Left(Date, 2)
            dDay = Mid(Date, 4, 2)
            suffix = " us"
        Else
            dDay = Left(Date, 2)
            dMonth = Mid(Date, 4, 2)
            suffix = ""
        End If
        dyear = Right(Date, 4)
        fname = "VOL " & dDay & dMonth & dyear & suffix
        For num_r = 3 To 15
            For num_c = 1 To 22
                Worksheets("Alg Con").Cells(num_r, num_c).Formula = "=Excel.SheetMacroEnabled.12|'" & Alg_Location & fname & ".xlsm'!'!TRAN!R" & num_r & "C" & num_c & "'"
            Next num_c
        Next num_r
        Worksheets("Main").Cells(7, 4).Formula = "=Excel.SheetMacroEnabled.12|'" & Alg_Location & fname & ".xlsm'!'!Data!R3C47'"
    
        Cells(3, 11) = "Alg Connected."
    End Sub
    Sub ActivateOnChange()
        day_started = 1
        getVars
    End Sub
    Sub StartTrade()
    10       On Error GoTo StartTrade_Error
    
    20        If in_tran = 0 Then
    30            If IsEmpty(Cells(actionline, actionCol)) = False And Cells(actionline, actionCol) <> 0 Then
    40                Cells(3, 11) = "Enter to: " & Cells(actionline, actionCol)
    50                Call paste_action
    60                in_tran = 1
    70                Application.OnTime Now + TimeValue("00:00:10"), "erase_got_it"
    80            End If
    90        Else
    100           If IsEmpty(Cells(actionline, closeCol)) = False And Cells(actionline, closeCol) <> 0 Then
    110               If IsEmpty(Cells(actionline + 1, actionCol)) = False And Cells(actionline + 1, actionCol) <> 0 Then
    120                   Cells(3, 11) = "Exit from: " & Cells(actionline, actionCol) & " and Enter to: " & Cells(actionline + 1, actionCol)
    130               Else
    140                   Cells(3, 11) = "Exit from: " & Cells(actionline, actionCol)
    150               End If
    160               Call paste_action
    170               If IsEmpty(Cells(actionline + 1, actionCol)) = False And Cells(actionline + 1, actionCol) <> 0 Then
    180                   in_tran = 1
    190               Else
    200                   in_tran = 0
    210               End If
    220               Application.OnTime Now + TimeValue("00:00:10"), "erase_got_it"
    230               actionline = actionline + 1
    240           End If
    250       End If
    260       getVars
    
    270      On Error GoTo 0
    280      Exit Sub
    
    StartTrade_Error:
    
    290       Cells(VbaerrLine, 18) = "Error " & Err.Number & " (" & Err.Description & ") in procedure StartTrade of Module Module1 in line:  in line: " & Erl
    VbaerrLine = VbaerrLine + 1
    End Sub
    Sub erase_got_it()
        Range("K3:M3").ClearContents
    End Sub
    Sub paste_action()
    10       On Error GoTo paste_action_Error
    
    20        num_of_mod = 0
    30        Cells(11, 5) = "limit"
    40        If Cells(actionline, actionCol) = "long" Then
    50            If in_tran = 0 Then
    60                Cells(11, 6) = Cells(7, 4).Value
    70                Cells(11, 3) = "buy"
    80                WAVFile = WAVFileBuy
    90            Else
    100               Cells(11, 6) = Cells(7, 4).Value
    110               Cells(11, 3) = "sell"
    120               WAVFile = WAVFileSell
    130           End If
    140       Else
    150           If in_tran = 0 Then
    160               Cells(11, 6) = Cells(7, 4).Value
    170               Cells(11, 3) = "sell"
    180               WAVFile = WAVFileSell
                  
    190           Else
    200               Cells(11, 6) = Cells(7, 4).Value
    210               Cells(11, 3) = "buy"
    220               WAVFile = WAVFileBuy
    230           End If
    240       End If
    250       Application.OnTime Now + TimeValue("00:00:04"), "CheckOrder"
    260       CallVSTOMethodPlaceOrder
    270       SentTime = Format(Now, "HH:nn:ss") & "." & Right(Format(Timer, "#0.00"), 2)
    280       If tran_0_1_2 <> 0 Then
    290           Call PlaySound(WAVFile, 0&, SND_ASYNC Or SND_FILENAME)
    300       End If
          '    If tran_0_1_2 = 0 Then
          '        If WAVFile = WAVFileSell Then
          '            Call PlaySound(WAVFileBuy, 0&, SND_ASYNC Or SND_FILENAME)
          '        Else
          '            Call PlaySound(WAVFileSell, 0&, SND_ASYNC Or SND_FILENAME)
          '        End If
          '    End If
    310       getVars
    
    320      On Error GoTo 0
    330      Exit Sub
    
    paste_action_Error:
    
    340       Cells(VbaerrLine, 18) = "Error " & Err.Number & " (" & Err.Description & ") in procedure paste_action of Module Module1 in line:  in line: " & Erl
    VbaerrLine = VbaerrLine + 1
    End Sub
    Sub CheckOrder()
    10       On Error GoTo CheckOrder_Error
    
    20        If (Cells(20, 7) <> 0 And (Cells(20, 3) = "open" Or Cells(20, 3) = "unfilled")) Or Cells(20, 3) <> "unsent" Then
    30            orid = Cells(20, 1)
    40            CallVSTOMethodUpdateOrderStatus
    50            Application.OnTime Now + TimeValue("00:00:01"), "ModOrder1"
    60            Application.OnTime Now + TimeValue("00:00:15"), "erase_mod"
    70        Else
    80            If Cells(20, 7) <> 0 And Cells(20, 3) = "unsent" Then
    90                WAVFile = "C:\Windows\Media\chord.WAV"
    100               Call PlaySound(WAVFile, 0&, SND_ASYNC Or SND_FILENAME)
    110               Cells(20, 3).Select
    120               With Selection.Interior
    130                   .Pattern = xlSolid
    140                   .PatternColorIndex = xlAutomatic
    150                   .Color = 255
    160                   .TintAndShade = 0
    170                   .PatternTintAndShade = 0
    180               End With
                      'CallVSTOMethodUpdateOrderStatus
    190               Application.OnTime Now + TimeValue("00:00:02"), "CheckOrder"
    200           End If
    210       End If
    220       CallVSTOMethodUpdateOrderStatus
    
    230      On Error GoTo 0
    240      Exit Sub
    
    CheckOrder_Error:
    
    250       Cells(VbaerrLine, 18) = "Error " & Err.Number & " (" & Err.Description & ") in procedure CheckOrder of Module Module1 in line:  in line: " & Erl
    VbaerrLine = VbaerrLine + 1
    End Sub
    Sub erase_mod()
        Cells(22, 1).ClearContents
    End Sub
    Sub ModOrder1()
    10       On Error GoTo ModOrder1_Error
    
    20        If (Cells(20, 7) <> 0 And (Cells(20, 3) = "open" Or Cells(20, 3) = "unfilled")) Then
    30            lmt_modP = (Cells(7, 4)) * 4
    40            CallVSTOMethodModifyOrderLimit
    50            Cells(22, 1) = "Modifying Order " & Cells(20, 1) & " To Last"
    60            If modL <> 0 Then
    70                Cells(modL, "L") = SentTime
    80                Cells(modL, "M") = Format(Now, "HH:nn:ss") & "." & Right(Format(Timer, "#0.00"), 2)
    90                Cells(modL, "M") = TimeValue(Now)
    100               Cells(modL, "N") = Cells(16, 1)
    110               Cells(modL, "O") = "Last"
    120               Cells(modL, "P") = lmt_modP / 4
    130               modL = modL + 1
    140           End If
    150           Application.OnTime Now + TimeValue("00:00:02"), "CallVSTOMethodUpdateOrderStatus"
    160           Application.OnTime Now + TimeValue("00:00:03"), "ModOrder1"
    170       End If
    
    180      On Error GoTo 0
    190      Exit Sub
    
    ModOrder1_Error:
    
    200       Cells(VbaerrLine, 18) = "Error " & Err.Number & " (" & Err.Description & ") in procedure ModOrder1 of Module Module1 in line:  in line: " & Erl
    VbaerrLine = VbaerrLine + 1
    End Sub
    Sub Buy_Btn()
    10       On Error GoTo Buy_Btn_Error
    
    20        Cells(11, 3) = "buy"
    30        Cells(11, 5) = "market"
    40        If in_tran = 0 Then
    50            in_tran = 1
    60        Else
    70            in_tran = 0
    80            actionline = actionline + 1
    90        End If
    100       CallVSTOMethodPlaceOrder
    110       getVars
    
    120      On Error GoTo 0
    130      Exit Sub
    
    Buy_Btn_Error:
    
    140       Cells(VbaerrLine, 18) = "Error " & Err.Number & " (" & Err.Description & ") in procedure Buy_Btn of Module Module1 in line: " & Erl
    VbaerrLine = VbaerrLine + 1
    End Sub
    Sub Sell_Btn()
    10       On Error GoTo Sell_Btn_Error
    
    20        Cells(11, 3) = "sell"
    30        Cells(11, 5) = "market"
    40        If in_tran = 0 Then
    50            in_tran = 1
    60        Else
    70            in_tran = 0
    80            actionline = actionline + 1
    90        End If
    100       CallVSTOMethodPlaceOrder
    110       getVars
    
    120      On Error GoTo 0
    130      Exit Sub
    
    Sell_Btn_Error:
    
    140       Cells(VbaerrLine, 18) = "Error " & Err.Number & " (" & Err.Description & ") in procedure Sell_Btn of Module Module1 in line: " & Erl
    VbaerrLine = VbaerrLine + 1
    End Sub
    Sub flatten()
    10       On Error GoTo flatten_Error
    
    20        check_open_and_cancel
              Dim VSTOSheet1 As TraderJul15.Sheet1
    30        Set VSTOSheet1 = GetManagedClass(Sheet1)
    40        If Cells(16, 2) <> 0 Then
    50            If Cells(16, 2) > 0 Then
    60                qu = Cells(16, 2)
    70                Call VSTOSheet1.PlaceOrder(False, Cells(11, 2), 2, qu, 0)
    80            Else
    90                If Cells(16, 2) < 0 Then
    100                   qu = Cells(16, 2) * (-1)
    110                   Call VSTOSheet1.PlaceOrder(True, Cells(11, 2), 2, qu, 0)
    120               End If
    130           End If
    140       End If
    
    150      On Error GoTo 0
    160      Exit Sub
    
    flatten_Error:
    
    170       Cells(VbaerrLine, 18) = "Error " & Err.Number & " (" & Err.Description & ") in procedure flatten of Module Module1 in line: " & Erl
    VbaerrLine = VbaerrLine + 1
    End Sub
    Sub check_open_and_cancel()
        If Cells(20, 7) <> 0 And Cells(20, 3) = "open" Then
            CallVSTOMethodcancelAll
            CallVSTOMethodUpdateOrderStatus
            Application.OnTime Now + TimeValue("00:00:01"), "check_open_and_cancel"
        End If
    End Sub
    'for the end of the day
    Sub End_Btn()
        day_started = 0
        If Cells(16, 2) <> 0 Then
            If MsgBox("You are still in position: " & Cells(16, 2) & " do you want to end anyway?", vbYesNo) = vbNo Then
                Exit Sub
            End If
        End If
        Cells(7, 1).ClearContents
        Cells(7, 2).ClearContents
        'Cells(7, 3).ClearContents
        Cells(7, 4).ClearContents
        With Range(Worksheets("Alg Con").Cells(3, 1), Worksheets("Alg Con").Cells(20, 22))
            .Value = .Value
        End With
        'CallVSTOMethodpriceLogOff
        'call to get the account parameters on the end of day
        CallVSTOMethodgetFilledOrders
        Application.OnTime Now + TimeValue("00:00:10"), "SaveF_as"
        Application.OnTime Now + TimeValue("00:00:15"), "SaveF"
    End Sub
    Sub End_Day()
        day_started = 0
       Cells(7, 1).ClearContents
        Cells(7, 2).ClearContents
        'Cells(7, 3).ClearContents
        Cells(7, 4).ClearContents
    '    Cells(28, 4).ClearContents
    '    Cells(28, 5).ClearContents
    '    Cells(30, 2).ClearContents
    '    Cells(31, 2).ClearContents
        With Range(Worksheets("Alg Con").Cells(3, 1), Worksheets("Alg Con").Cells(20, 22))
            .Value = .Value
        End With
        'CallVSTOMethodpriceLogOff
        'call to get the account parameters on the end of day
        CallVSTOMethodgetFilledOrders
        Application.OnTime Now + TimeValue("00:00:10"), "SaveF_as"
        Application.OnTime Now + TimeValue("00:00:15"), "SaveF"
    End Sub
    Sub CloseF()
    ActiveWorkbook.Close False
    End Sub
    Sub SaveF()
        Worksheets("Main").Select
        CallVSTOMethodLogOff
        Worksheets("Main").Cells(1, "D") = "Done"
        ActiveWorkbook.Save
    End Sub
    Sub SaveF_as()
        
        ddate = Date
        dDay = Left(ddate, 2)
        dMonth = Mid(ddate, 4, 2)
        dyear = Mid(ddate, 7, 4)
        ActiveWorkbook.SaveAs Filename:= _
            Save_Location & "Auto Trade " & dDay & dMonth & dyear & ".xlsm" _
            , FileFormat:=xlOpenXMLWorkbookMacroEnabled, Password:="", WriteResPassword:="", _
            ReadOnlyRecommended:=False, CreateBackup:=False
    End Sub
    Sub getVars()
        Cells(2, "J") = actionline
        Cells(2, "I") = in_tran
        Cells(2, "K") = day_started
    End Sub
    Public Sub LostConOrder()
    10       On Error GoTo LostConOrder_Error
    
    20        WAVFile = "C:\Users\Administrator\FTP\Trader\infinity.WAV"
    30        Call PlaySound(WAVFile, 0&, SND_ASYNC Or SND_FILENAME)
    40        ReConnectOrder
    50        in_tran = Cells(2, "M")
    60        actionline = Cells(2, "N")
    70        Application.OnTime Now + TimeValue("00:00:03"), "ActivateOnChange"
    
    80       On Error GoTo 0
    90       Exit Sub
    
    LostConOrder_Error:
    
    100       Cells(VbaerrLine, 18) = "Error " & Err.Number & " (" & Err.Description & ") in procedure LostConOrder of Module Module1 in line: " & Erl
    VbaerrLine = VbaerrLine + 1
    End Sub
    Sub ReConnectOrder()
              Dim VSTOSheet1 As TraderJul15.Sheet1
    10       On Error GoTo ReConnectOrder_Error
    
    20        Set VSTOSheet1 = GetManagedClass(Sheet1)
    30        Call VSTOSheet1.LogOn(gateKeeperURL, vendorID, username, pass, LogMode, ContName)
    
    40       On Error GoTo 0
    50       Exit Sub
    
    ReConnectOrder_Error:
    
    60        Cells(VbaerrLine, 18) = "Error " & Err.Number & " (" & Err.Description & ") in procedure ReConnectOrder of Module Module1 in line: " & Erl
    End Sub
    Sub Update_Btn()
    10       On Error GoTo Update_Btn_Error
    
    20        CallVSTOMethodUpdateOrderStatus
    30        CallVSTOMethodUpdatePnl
    40        Cells(16, 5) = Now
    
    50       On Error GoTo 0
    60       Exit Sub
    
    Update_Btn_Error:
    
    70        Cells(VbaerrLine, 18) = "Error " & Err.Number & " (" & Err.Description & ") in procedure Update_Btn of Module Module1 in line: " & Erl
    VbaerrLine = VbaerrLine + 1
    End Sub
    Sub Modify_Btn()
        ModOrder1
    End Sub
    Sub LogOff_Btn()
        CallVSTOMethodLogOff
    End Sub
    
    Sub CallKeepAlive()
    10       On Error GoTo CallKeepAlive_Error
    
    20        If day_started = 1 Then
    30            Application.OnTime Now + TimeValue("00:03:00"), "CallKeepAlive"
    40            CallVSTOMethodKeepAlive
    50            Cells(8, 1) = "KEEP"
    60            Cells(8, 2) = Now
    70        End If
    
    80       On Error GoTo 0
    90       Exit Sub
    
    CallKeepAlive_Error:
    
    100       Cells(VbaerrLine, 18) = "Error " & Err.Number & " (" & Err.Description & ") in procedure CallKeepAlive of Module Module1 in line: " & Erl
    VbaerrLine = VbaerrLine + 1
    End Sub
    
    
    
    Sub CallVSTOMethodLogOff()
              Dim VSTOSheet1 As TraderJul15.Sheet1
    10       On Error GoTo CallVSTOMethodLogOff_Error
    
    20        Set VSTOSheet1 = GetManagedClass(Sheet1)
    30        Call VSTOSheet1.LogOff
    
    40       On Error GoTo 0
    50       Exit Sub
    
    CallVSTOMethodLogOff_Error:
    
    60        Cells(VbaerrLine, 18) = "Error " & Err.Number & " (" & Err.Description & ") in procedure CallVSTOMethodLogOff of Module Module2 in line: " & Erl
    VbaerrLine = VbaerrLine + 1
    End Sub
    
    Sub CallVSTOMethodPlaceOrder()
              Dim VSTOSheet1 As TraderJul15.Sheet1
    10       On Error GoTo CallVSTOMethodPlaceOrder_Error
    
    20        Set VSTOSheet1 = GetManagedClass(Sheet1)
              'limit = 1, market = 2, stoplimit = 3, stop = 4, manual = 5,
    30        orderQ = Cells(11, 4)
    40        If Cells(11, 5) Like "limit" Then
    50            OrderType = 1
    60            If IsNull(actionline) = False And actionline <> 0 Then
    70                If IsEmpty(Cells(actionline + 1, actionCol)) = False And Cells(actionline + 1, actionCol) <> 0 Then
    80                    If Cells(actionline + 1, actionCol) = Cells(actionline, actionCol) Then
    90                        tran_0_1_2 = 0
    100                       GoTo no_tran
    110                   Else
    120                       orderQ = orderQ * 2
    130                       tran_0_1_2 = 2
    140                   End If
    150               Else
    160                   tran_0_1_2 = 1
    170               End If
    180           End If
    190       Else
    200           OrderType = 2
    210       End If
    220       If Cells(11, 3) Like "buy" Then
    230           OrderAction = True
    240       Else
    250           OrderAction = False
    260       End If
    270       contractID = Cells(11, 2)
    280       OrderLimitP = (Cells(11, 6) * 4)
    290       Call VSTOSheet1.PlaceOrder(OrderAction, contractID, OrderType, orderQ, OrderLimitP)
              'Cells(tranP, "Q") = OrderLimitP / 4
    300       tranP = tranP + 1
    no_tran:
    
    310      On Error GoTo 0
    320      Exit Sub
    
    CallVSTOMethodPlaceOrder_Error:
    
    330       Cells(VbaerrLine, 18) = "Error " & Err.Number & " (" & Err.Description & ") in procedure CallVSTOMethodPlaceOrder of Module Module2 in line: " & Erl
    VbaerrLine = VbaerrLine + 1
    End Sub
    
    Sub CallVSTOMethodModifyOrderLimit()
              Dim VSTOSheet1 As TraderJul15.Sheet1
    10       On Error GoTo CallVSTOMethodModifyOrderLimit_Error
    
    20        Set VSTOSheet1 = GetManagedClass(Sheet1)
    30        If Cells(20, 7) <> 0 Then
    40            Call VSTOSheet1.ModOrder(Cells(20, 1), lmt_modP, Cells(20, 7))
    50        End If
    
    60       On Error GoTo 0
    70       Exit Sub
    
    CallVSTOMethodModifyOrderLimit_Error:
    
    80        Cells(VbaerrLine, 18) = "Error " & Err.Number & " (" & Err.Description & ") in procedure CallVSTOMethodModifyOrderLimit of Module Module2 in line: " & Erl
    90        VbaerrLine = VbaerrLine + 1
    End Sub
    
    Sub CallVSTOMethodUpdateOrderStatus()
              Dim VSTOSheet1 As TraderJul15.Sheet1
    10       On Error GoTo CallVSTOMethodUpdateOrderStatus_Error
    
    20        Set VSTOSheet1 = GetManagedClass(Sheet1)
    30        Call VSTOSheet1.UpdateOrderStatus
    
    40       On Error GoTo 0
    50       Exit Sub
    
    CallVSTOMethodUpdateOrderStatus_Error:
    
    60        Cells(VbaerrLine, 18) = "Error " & Err.Number & " (" & Err.Description & ") in procedure CallVSTOMethodUpdateOrderStatus of Module Module2 in line: " & Erl
    VbaerrLine = VbaerrLine + 1
    End Sub
    
    Sub CallVSTOMethodgetFilledOrders()
              Dim VSTOSheet1 As TraderJul15.Sheet1
    10       On Error GoTo CallVSTOMethodgetFilledOrders_Error
    
    20        Set VSTOSheet1 = GetManagedClass(Sheet1)
    30        Call VSTOSheet1.getFilledOrders
    
    40       On Error GoTo 0
    50       Exit Sub
    
    CallVSTOMethodgetFilledOrders_Error:
    
    60        Cells(VbaerrLine, 18) = "Error " & Err.Number & " (" & Err.Description & ") in procedure CallVSTOMethodgetFilledOrders of Module Module2 in line: " & Erl
    VbaerrLine = VbaerrLine + 1
    End Sub
    
    Sub CallVSTOMethodUpdatePnl()
              Dim VSTOSheet1 As TraderJul15.Sheet1
    10       On Error GoTo CallVSTOMethodUpdatePnl_Error
    
    20        Set VSTOSheet1 = GetManagedClass(Sheet1)
    30        Call VSTOSheet1.GetPos(Cells(11, 2))
    
    40       On Error GoTo 0
    50       Exit Sub
    
    CallVSTOMethodUpdatePnl_Error:
    
    60        Cells(VbaerrLine, 18) = "Error " & Err.Number & " (" & Err.Description & ") in procedure CallVSTOMethodUpdatePnl of Module Module2 in line: " & Erl
    VbaerrLine = VbaerrLine + 1
    End Sub
    Sub CallVSTOMethodcancelAll()
              Dim VSTOSheet1 As TraderJul15.Sheet1
    10       On Error GoTo CallVSTOMethodcancelAll_Error
    
    20        Set VSTOSheet1 = GetManagedClass(Sheet1)
    30        Call VSTOSheet1.CancelAllOrders
    
    40       On Error GoTo 0
    50       Exit Sub
    
    CallVSTOMethodcancelAll_Error:
    
    60        Cells(VbaerrLine, 18) = "Error " & Err.Number & " (" & Err.Description & ") in procedure CallVSTOMethodcancelAll of Module Module2 in line: " & Erl
    VbaerrLine = VbaerrLine + 1
    End Sub
    Sub CallVSTOMethodKeepAlive()
              Dim VSTOSheet1 As TraderJul15.Sheet1
    10       On Error GoTo CallVSTOMethodKeepAlive_Error
    
    20        Set VSTOSheet1 = GetManagedClass(Sheet1)
    30        Call VSTOSheet1.KeepAlive
    
    40       On Error GoTo 0
    50       Exit Sub
    
    CallVSTOMethodKeepAlive_Error:
    
    60        Cells(VbaerrLine, 18) = "Error " & Err.Number & " (" & Err.Description & ") in procedure CallVSTOMethodKeepAlive of Module Module2 in line: " & Erl
    VbaerrLine = VbaerrLine + 1
    End Sub
    
    

    and my VBA code in Sheet1

    Sub ConStat(ByRef theState As String, ByRef AccId As String, ByRef ConId As String)
    10       On Error GoTo ConStat_Error
    
    20        Cells(7, 3) = theState
    30        Cells(7, 3).Select
    40        With Selection.Interior
    50            .Pattern = xlSolid
    60            .PatternColorIndex = xlAutomatic
    70            .Color = 5296274
    80            .TintAndShade = 0
    90            .PatternTintAndShade = 0
    100       End With
    110       Cells(7, 2) = AccId
    120       Cells(11, 2) = ConId
    
    130      On Error GoTo 0
    140      Exit Sub
    
    ConStat_Error:
    
    150       Cells(VbaerrLine, 18) = "Error " & Err.Number & " (" & Err.Description & ") in procedure ConStat of VBA Document Sheet1 in line: " & Erl
    VbaerrLine = VbaerrLine + 1
    End Sub
    Sub OrdNodOK(ByRef Notif As Variant)
    10       On Error GoTo OrdNodOK_Error
    
    20        For i = 0 To 9
    30            Cells(20, i + 1) = Notif(i)
    40        Next i
    50        Cells(20, 11) = Notif(10) & ":" & Notif(11) & ":" & Notif(12) & "." & Notif(13)
    60        Cells(20, 12) = Notif(14) & ":" & Notif(15) & ":" & Notif(16) & "." & Notif(17)
    
    70       On Error GoTo 0
    80       Exit Sub
    
    OrdNodOK_Error:
    
    90        Cells(VbaerrLine, 18) = "Error " & Err.Number & " (" & Err.Description & ") in procedure OrdNodOK of VBA Document Sheet1 in line: " & Erl
    VbaerrLine = VbaerrLine + 1
    End Sub
    Sub UpdateStat(ByRef Sts As Variant)
    10       On Error GoTo UpdateStat_Error
    
    20        For i = 0 To UBound(Sts)
    30            Cells(tmpRow, 1) = Sts(i)
    40            tmpRow = tmpRow + 1
    50        Next i
    
    60       On Error GoTo 0
    70       Exit Sub
    
    UpdateStat_Error:
    
    80        Cells(VbaerrLine, 18) = "Error " & Err.Number & " (" & Err.Description & ") in procedure UpdateStat of VBA Document Sheet1 in line: " & Erl
    90        VbaerrLine = VbaerrLine + 1
    End Sub
    Sub OrdNodNOT(ByRef errmsg As String)
    10       On Error GoTo OrdNodNOT_Error
    
    20        Cells(2, 4) = errmsg
    
    30       On Error GoTo 0
    40       Exit Sub
    
    OrdNodNOT_Error:
    
    50        Cells(VbaerrLine, 18) = "Error " & Err.Number & " (" & Err.Description & ") in procedure OrdNodNOT of VBA Document Sheet1 in line: " & Erl
    VbaerrLine = VbaerrLine + 1
    End Sub
    Sub LostCon(ByRef LosRes As String)
    10       On Error GoTo LostCon_Error
    
    20        WAVFile = "C:\Users\Administrator\FTP\Trader\infinity.WAV"
    30        Call PlaySound(WAVFile, 0&, SND_ASYNC Or SND_FILENAME)
    40        ReConnectOrder
    50        Cells(8, 3) = "Lost Con at: " & Now
    60        Application.OnTime Now + TimeValue("00:00:10"), "ActivateDay"
    
    70       On Error GoTo 0
    80       Exit Sub
    
    LostCon_Error:
    
    90        Cells(VbaerrLine, 18) = "Error " & Err.Number & " (" & Err.Description & ") in procedure LostCon of VBA Document Sheet1 in line: " & Erl
    VbaerrLine = VbaerrLine + 1
    End Sub
    Sub AccPos(ByRef pos As Integer, ByRef pnl As Integer)
    10       On Error GoTo AccPos_Error
    
    20        Cells(16, 2) = pos
    30        Cells(16, 4) = pnl * 50
    
    40       On Error GoTo 0
    50       Exit Sub
    
    AccPos_Error:
    
    60        Cells(VbaerrLine, 18) = "Error " & Err.Number & " (" & Err.Description & ") in procedure AccPos of VBA Document Sheet1 in line: " & Erl
    VbaerrLine = VbaerrLine + 1
    End Sub
    Sub FilledOrd(ByRef tmp As Variant)
        For R = 0 To UBound(tmp, 1)
            For C = 0 To UBound(tmp, 2)
                Worksheets("Portfolio").Cells(R + 2, C + 1) = tmp(R, C)
            Next C
        Next R
    End Sub
    Sub GetErr(ByRef theERR As String, ByRef WhereErr As String)
    10       On Error GoTo GetErr_Error
    
    20        Cells(errLine, 8) = Format(Now, "HH:nn:ss") & "." & Right(Format(Timer, "#0.00"), 2)
    30        Cells(errLine, 9) = WhereErr
    40        Cells(errLine, 10) = theERR
    50        errLine = errLine + 1
    
    60       On Error GoTo 0
    70       Exit Sub
    
    GetErr_Error:
    
    80        Cells(VbaerrLine, 18) = "Error " & Err.Number & " (" & Err.Description & ") in procedure GetErr of VBA Document Sheet1 in line: " & Erl
    VbaerrLine = VbaerrLine + 1
    End Sub
    Private Sub CommandButton1_Click()
        CallVSTOMethodUpdateOrderStatus
        Application.OnTime Now + TimeValue("00:00:01"), "flatten"
    End Sub
    

    Wednesday, July 8, 2015 3:08 PM
  • Hi talbaget,

    Base on my test with Application.Run method to run the macro in VS2008 with excel 2007, it works fine too (error handle), I can’t reproduce that issue.

    The Application.Run method returns the dynamic type value, and for this way, please let the macro in the Module.

    Could you share a simple sample project that could reproduce that issue on the OneDrive?

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Thursday, July 9, 2015 6:52 AM
    Moderator