Why is writing to PC-DMIS from VBA so slow?

I have two scripts that do very similar things : Read scan point data from PC-DMIS and then write scan point data back into PC-DMIS. One version of the script is written and executed in PC-DMIS basic. The other is written in Excel VBA and is executed through Excel by calling the macro from a very simple PC-DMIS script.

The PC-DMIS only version writes the scan data out to a temp file and then reads it back out of the file and writes to PC-DMIS. The excel version reads the data out into memory in a 2D array and then writes it back into PC-DMIS from the array, no external files involved.

I figured the Excel version would be faster since everything is in memory. It turns out that it is wicked slow and takes about 11 seconds to write a 64 point scan while the PC-DMIS version does it in less than 1 second. All the time is in the writing via the Puttext method. Reading from PC-DMIS into the array takes a fraction of a second but writing it back out into PC-DMIS takes 11 seconds. In the PC-DMIS only version both operations take a fraction of a second.

Is VBA just inherently slow for this kind of thing? Anyone else experienced this?

Parents

DaSalo over 9 years ago

Are you suggesting that apply application.visible to the PC-DMIS application object, to the Excel application object, or both? I already have been applying it to the excel object and the speed differential that I note above is with this property set to False. The speed difference is staggering. With a 500 pnt scan it is the difference between a finger snap or get up and get yourself a cup of coffee while you wait. Could there be some virus protection or something of that sort that is getting in the way of outgoing data from VBA? I don't feel like there is anything about this code that should be the least bit computationally intensive. It must be something to do with the interaction between VBA and PC-DMIS. For some reason VBA can pull data without interference but can't push data at the same rate.

Here is the code that I am using to call the macro from PC-DMIS:

Sub Main(Cmd_ID As String, Rtrn_ID As String)
      Dim xl As Object
      Dim xlBook As Object      
      Dim sPath As String

      sPath = "C:\BLADERUNNER\Scripts\"
      Set xl = CreateObject("Excel.application")
      Set xlBook = xl.Workbooks.Open(sPath & "Scan_Utilities.xlsm", 0, True)      
       xl.Application.Visible = False
       xl.run "Module1.CheckFunct", Cmd_ID, Rtrn_ID     
       xl.Quit

      Set xlBook = Nothing
      Set xl = Nothing
End Sub

And here is the function that is deathly slow in VBA :

Function WriteScanFromArray(ByRef Cmd As Object, ByRef ScanArry As Variant) As Boolean

Dim i As Integer
Dim Retval As Boolean

WriteScanFromArray = False

'Verify that target command is a basic scan
    If Not AttachBasicScan(Cmd) Then
        Exit Function
    End If

'Check that Array is allocated and 2D
    If IsArrayAllocated(ScanArry) Then
        If NumberOfArrayDimensions(ScanArry) <> 2 Then
            MsgBox "Array is not 2D!", 0, "Error!"
            Exit Function
        End If
    Else
        MsgBox "Array not allocated!", 0, "Error!"
        Exit Function
    End If
    
'Check Upper and Lower bounds of Array
    If UBound(ScanArry, 2) <> 9 Then
        MsgBox "Array must have 10 values per record!", 0, "Error!"
        Exit Function
    End If
    If LBound(ScanArry, 2) <> 0 Then
        MsgBox "Array must use 0 for lower boundry!", 0, "Error!"
        Exit Function
    End If

'Write array into PC-DMIS feature.
    For i = LBound(ScanArry, 1) To UBound(ScanArry, 1)
        Retval = Cmd.PutText(CStr(UBound(ScanArry, 1) + 1), N_HITS, 0)
        Retval = Cmd.PutText(CStr(ScanArry(i, 0)), THEO_X, i + 1)
        Retval = Cmd.PutText(CStr(ScanArry(i, 1)), THEO_Y, i + 1)
        Retval = Cmd.PutText(CStr(ScanArry(i, 2)), THEO_Z, i + 1)
        Retval = Cmd.PutText(CStr(ScanArry(i, 3)), THEO_I, i + 1)
        Retval = Cmd.PutText(CStr(ScanArry(i, 4)), THEO_J, i + 1)
        Retval = Cmd.PutText(CStr(ScanArry(i, 5)), THEO_K, i + 1)
        Retval = Cmd.PutText(CStr(ScanArry(i, 6)), MEAS_X, i + 1)
        Retval = Cmd.PutText(CStr(ScanArry(i, 7)), MEAS_Y, i + 1)
        Retval = Cmd.PutText(CStr(ScanArry(i, 8)), MEAS_Z, i + 1)
        Retval = Cmd.PutText(CStr(ScanArry(i, 9)), T_VALUE, i + 1)
    Next i

WriteScanFromArray = True
End Function

Reply

DaSalo over 9 years ago

Sub Main(Cmd_ID As String, Rtrn_ID As String)
      Dim xl As Object
      Dim xlBook As Object      
      Dim sPath As String

      sPath = "C:\BLADERUNNER\Scripts\"
      Set xl = CreateObject("Excel.application")
      Set xlBook = xl.Workbooks.Open(sPath & "Scan_Utilities.xlsm", 0, True)      
       xl.Application.Visible = False
       xl.run "Module1.CheckFunct", Cmd_ID, Rtrn_ID     
       xl.Quit

      Set xlBook = Nothing
      Set xl = Nothing
End Sub

And here is the function that is deathly slow in VBA :

Function WriteScanFromArray(ByRef Cmd As Object, ByRef ScanArry As Variant) As Boolean

Dim i As Integer
Dim Retval As Boolean

WriteScanFromArray = False

'Verify that target command is a basic scan
    If Not AttachBasicScan(Cmd) Then
        Exit Function
    End If

'Check that Array is allocated and 2D
    If IsArrayAllocated(ScanArry) Then
        If NumberOfArrayDimensions(ScanArry) <> 2 Then
            MsgBox "Array is not 2D!", 0, "Error!"
            Exit Function
        End If
    Else
        MsgBox "Array not allocated!", 0, "Error!"
        Exit Function
    End If
    
'Check Upper and Lower bounds of Array
    If UBound(ScanArry, 2) <> 9 Then
        MsgBox "Array must have 10 values per record!", 0, "Error!"
        Exit Function
    End If
    If LBound(ScanArry, 2) <> 0 Then
        MsgBox "Array must use 0 for lower boundry!", 0, "Error!"
        Exit Function
    End If

'Write array into PC-DMIS feature.
    For i = LBound(ScanArry, 1) To UBound(ScanArry, 1)
        Retval = Cmd.PutText(CStr(UBound(ScanArry, 1) + 1), N_HITS, 0)
        Retval = Cmd.PutText(CStr(ScanArry(i, 0)), THEO_X, i + 1)
        Retval = Cmd.PutText(CStr(ScanArry(i, 1)), THEO_Y, i + 1)
        Retval = Cmd.PutText(CStr(ScanArry(i, 2)), THEO_Z, i + 1)
        Retval = Cmd.PutText(CStr(ScanArry(i, 3)), THEO_I, i + 1)
        Retval = Cmd.PutText(CStr(ScanArry(i, 4)), THEO_J, i + 1)
        Retval = Cmd.PutText(CStr(ScanArry(i, 5)), THEO_K, i + 1)
        Retval = Cmd.PutText(CStr(ScanArry(i, 6)), MEAS_X, i + 1)
        Retval = Cmd.PutText(CStr(ScanArry(i, 7)), MEAS_Y, i + 1)
        Retval = Cmd.PutText(CStr(ScanArry(i, 8)), MEAS_Z, i + 1)
        Retval = Cmd.PutText(CStr(ScanArry(i, 9)), T_VALUE, i + 1)
    Next i

WriteScanFromArray = True
End Function

Children

No Data