VB6 code executes slowly

[dbrew0]

Hi all,

I've got an Excel 2000 VBA app that I've ported over to VB6. The code runs fine, no errors, and gives expected results. The problem is that the VB6 version runs very slowly. For example, if the app is run within Excel VBA, it completes in about 2 seconds, but the VB6 version takes over 20 seconds to complete.

Any thoughts on where I should be looking for the problem?

Thanks.

 

[vladk]

It could be late binding... Make sure you don't have any data types like Object.

 

[vladk]

And don't forget, when you run it inside Excel, the latter is running for you already, it was started before. Inside VB6 you might wait for Excel start and count it as running macro time..

 

[Andrzejek]

Also, if you show all the changes/populating cells/formating to the user, refreshing of the screen will take some time. Try to do all your work in Excel while Excel is not visible, and then after you have it all done, show a completed Excel ( .Visible = True)

That was my experience when Excel was working slow.

Plus - you avoid users exiting Excel before you are done populating cells.

HTH

---- Andy

 

[HughLerwill]

dbrew0,

If you are currently using CreateObject to setup your Excel object you may consider using GetObject instead(or as well), your code will then use the existing instance of Excel (if one exists) and setup of the Excel object within your code will be much quicker.

Some code like the following may suit you;

On Error Resume Next
Set objXl = GetObject(, "Excel.Application")
'Get existing instance of Excel if possible, if not Create one
If Err Then Set objXl = CreateObject("Excel.Application")
On Error GoTo 0
With objXl
'do your stuff
End With
Set objXl = Nothing

Early binding as opposed to late is said to be quicker but not much in practice with today's (fast)proccessors. I find late more flexible and less fussy when dealing with different versions of the Excel library.

HTH Hugh,

 

[dbrew0]

Hey all....Thanks for the thoughts and suggestions! The problem seems to occur after the form is closed (by user intervention) and the .txt file is opened. After that is where the noticable slow-down occurs - when the code is manipulating rows,cells, etc. I have actually clicked on the spreadsheet the code is working on and visibly watched it work (which I couldn't do in the VBA version because it was too fast). And I also hit the Task Manager and was shown that my cpu load was pegged at 100% while this code chugged.

See the code below (I know seeing is worth 1000 words). Other than the reference to the Excel object, the code is identical to the VBA code. As you can see, I've tried changing how the Excel application is set and referenced....with the same slow execution. I have also tried running the code with Excel object hidden, but was no different.

Option Explicit

Public FilePathTxt As String
Public FileNameTxt As String
Public FileExtTxt As String
Public JobNum As String
Public JobQty As Integer
Public FilePathBom As String
Public FileNameBom As String
Public FileExtXls As String
Public ExitFlag As Boolean
Public objXL As Excel.Application

Dim ItemLevel(20) As Integer
Dim ItemLvlQty(20) As Integer
Dim LastRowNum As Integer

Sub main()

'  Dim objXL As Excel.Application
  Dim WBook As Workbook
  
'Start Excel and make it visible
'  Set objXL = CreateObject("excel.Application")
  Set objXL = New Excel.Application
  objXL.Visible = True
  
  
  FilePathTxt = "F:\CadData\100_ASSM\"
  FileExtTxt = ".txt"
  FileExtXls = ".XLS"
  
  frmFileFind.Show vbModal  'added vbModel to make form be sole focus until it is closed.
  
  If ExitFlag = True Then
    Exit Sub
  End If
  
'  With Application.FileSearch
  With objXL.FileSearch
    .NewSearch
    .LookIn = FilePathTxt
    .FileName = FileNameTxt & FileExtTxt
    If .Execute = 0 Then
      MsgBox "Can not find any files with that name.  Cannot continue."
      Exit Sub
    ElseIf .Execute > 1 Then
      MsgBox "More than 1 file was found with that name.  Cannot continue."
      Exit Sub
    End If
    
  End With
    
' Check and make sure the workbook isn't already open.
  For Each WBook In Workbooks
    If WBook.Name = FileNameTxt + FileExtXls Then
      MsgBox "This file: " & FileNameTxt & FileExtXls & " already exists." & Chr(13) & "Cannot continue.  Please close the open file and try again.", vbCritical, "File Exists"
      Exit Sub
    End If
  Next
  
' Open the .txt file.  Includes all of the import options
  Workbooks.OpenText FileName:=FilePathTxt & FileNameTxt & FileExtTxt, Origin:=xlWindows, _
        StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, _
        Tab:=True, Semicolon:=False, Comma:=False, Space:=False, Other:=False, _
        FieldInfo:=Array(Array(1, xlTextFormat), Array(2, xlTextFormat), Array(3, xlGeneral), Array(4, xlTextFormat), Array(5, xlTextFormat), _
        Array(6, xlTextFormat), Array(7, xlTextFormat), Array(8, xlTextFormat), Array(9, xlTextFormat), Array(10, xlTextFormat), Array(11, xlTextFormat), _
        Array(12, xlTextFormat), Array(13, xlTextFormat), Array(14, xlTextFormat), Array(15, xlTextFormat), Array(16, xlTextFormat), Array(17, xlTextFormat), _
        Array(18, xlTextFormat))
  
  
' Make sure the newly opened workbook is active
  Workbooks(FileNameTxt & FileExtTxt).Activate
  
' Change column and row properties to be like the bom file
  ActiveSheet.Columns("Q").NumberFormat = "@"  ' Changes column format to text. Need to use the @ instead of Text - why??  Don't know.
  ActiveSheet.Columns("L").NumberFormat = "mm/dd/yyyy"
  ActiveSheet.Columns("I").NumberFormat = "#,##0.00"
  ActiveSheet.Columns("J").NumberFormat = "#,##0.00"
  ActiveSheet.Columns("C").NumberFormat = "#,##0"
  
' Calculate quantity of sub parts
  CalcQtyOfSubParts

  AutoWidthColumns
  ActiveSheet.Columns("F").WrapText = True
  ActiveSheet.Range(Cells(2, 1), Cells(LastRowNum, 1)).EntireRow.VerticalAlignment = xlVAlignTop
  
' Eliminates leading spaces in column 6 (description)
  ElimLeadSpaces (6)
  
' Sorts the spreadsheet by Vendor, Part Num, Description, all ascending
  ActiveSheet.Range(Cells(1, 1), Cells(LastRowNum, 50)).Sort key1:=ActiveSheet.Columns("B"), order1:=xlAscending, key2:=ActiveSheet.Columns("G"), order2:=xlAscending, key3:=ActiveSheet.Columns("F"), order3:=xlAscending, header:=xlYes
  
' Find and eliminate duplicate part numbers adding the qty together
  ElimDuplicateParts
  
' Find last row number again since duplicate rows were eliminated.
  LastRowNum = FindLastRow

' Add manufacturer name and part number onto end of description
'  AddMfrPartNum

'' Find and eliminate rows that have RM and MS in the Vendor (column 2)
'  ElimRMMS
'' Find last row number again since rows with RM and MS were eliminated.
'  LastRowNum = FindLastRow

' Apply job qty to QTY column and adds forumula for Material Price column
  AddJobQtyMatlPrice

' Delete unnecessary columns (NewQty, Temp Config, MFR) from the temp bom file
  ActiveSheet.Columns("S").Delete
  ActiveSheet.Columns("R").Delete
  ActiveSheet.Columns("D").Delete

' Update open Job specific excel bom from temp bom
  UpdateExcelBOM
  
' Closes the temp imported .txt file - without saving any changes
  Workbooks(FileNameTxt + FileExtTxt).Close (False)
  
End Sub

Private Sub AutoWidthColumns()
  
  Dim x As Integer
  Dim y As Integer
  
'  Workbooks(FileNameTxt & FileExtXls).Activate
  
  x = 1
  y = FindLastColumn
  
  Range(Cells(1, x), Cells(1, y)).EntireColumn.AutoFit


End Sub

Private Sub CalcQtyOfSubParts()
' This routine updates the quantity of a sub-part based on the required upper assembly it is associated with.
'   It also sets the item numbers of each item in an A.B.C.D format based on sub level of the part of a top level assembly item.

  Dim FirstRowNum As Integer
  Dim column As Integer
  Dim x As Integer
  Dim SubItemNum As String
  Dim ItemNum As String
  Dim NumLeadSpace As Integer
  Dim Levelptr As Integer
  
  LastRowNum = FindLastRow
  
  FirstRowNum = 2   '1st row has header info
  column = 16        'need to find how many leading spaces in value of Config/Length column (Solidworks bom)
  Levelptr = 0      'set pointer to top level item
  
  For x = FirstRowNum To LastRowNum
    
    If ActiveSheet.Cells(x, 17).Value <> "" Then
      ClearArray (0)
      ItemNum = ActiveSheet.Cells(x, 17).Value
      Levelptr = 0
      ItemLevel(Levelptr) = CInt(ItemNum)
      ItemLvlQty(Levelptr) = ActiveSheet.Cells(x, 3).Value
      ActiveSheet.Cells(x, 4).Value = ActiveSheet.Cells(x, 3).Value
      
    Else
      NumLeadSpace = FindNumLeadingSpaces(x, column)
      If (NumLeadSpace / 2) > Levelptr Then
        Levelptr = Levelptr + 1
        ItemLevel(Levelptr) = ItemLevel(Levelptr) + 1
      
      ElseIf (NumLeadSpace / 2) = Levelptr Then
        ItemLevel(Levelptr) = ItemLevel(Levelptr) + 1
        ClearArray (Levelptr + 1)
        
      Else
        Levelptr = NumLeadSpace / 2
        ItemLevel(Levelptr) = ItemLevel(Levelptr) + 1
        ClearArray (Levelptr + 1)
      
      End If
      
      ActiveSheet.Cells(x, 17).Value = BuildItemString
      ItemLvlQty(Levelptr) = ActiveSheet.Cells(x, 3).Value
      ActiveSheet.Cells(x, 4).Value = CalculateQty(x, Levelptr)
      
    End If
  
  Next

End Sub

Private Sub ClearArray(StartPt As Integer)
  
  Dim x As Integer
  
  For x = StartPt To 19
    ItemLevel(x) = 0
    ItemLvlQty(x) = 0
  Next
  
End Sub

Private Sub ElimLeadSpaces(column As Integer)
' This routine eliminates leading spaces in a string.

  Dim row As Integer
  Dim LeadSpaces As Integer
  Dim CellText As String
    
  For row = 2 To LastRowNum
    LeadSpaces = FindNumLeadingSpaces(row, column)
    If LeadSpaces > 0 Then
      CellText = ActiveSheet.Cells(row, column).Value
      ActiveSheet.Cells(row, column).Value = LTrim(CellText)
    End If
    ActiveSheet.Cells(row, column).Value = UCase(ActiveSheet.Cells(row, column).Value)
  Next

End Sub

Private Sub ElimDuplicateParts()
' This routine eliminates duplicate parts while adding the quantities together

  Dim row As Integer
  Dim column As Integer
  Dim LeadSpaces As Integer
  
  column = 18
  
' copies the Config/Length column to a temp column
  ActiveSheet.Columns("P").Copy    ' Copies column P to clipboard
  ActiveSheet.Columns("R").Insert  ' Inserts clipboard contents at column R, shifting columns to the right
  ActiveSheet.Cells(1, column).Value = "TEMP CONFIG"

' finds and eliminates all leading spaces in temp column (will be used for string comparison with part number)
  For row = 2 To LastRowNum
    LeadSpaces = FindNumLeadingSpaces(row, column)
    If LeadSpaces > 0 Then
      ActiveSheet.Cells(row, column).Value = LTrim(ActiveSheet.Cells(row, column).Value)
    End If
  Next

' looks for duplicate parts, if found, adds qty and eliminates the duplicate row

  For row = 2 To LastRowNum - 1
    If ActiveSheet.Cells(row, 3).Value = "" Then
      Exit For
    End If
    If ActiveSheet.Cells(row, 7).Value = ActiveSheet.Cells(row + 1, 7).Value Then
      If ActiveSheet.Cells(row, 18).Value = ActiveSheet.Cells(row + 1, 18).Value Then
         
        ActiveSheet.Cells(row, 4).Value = ActiveSheet.Cells(row, 4).Value + ActiveSheet.Cells(row + 1, 4).Value
        ActiveSheet.Cells(row, 17).Value = ActiveSheet.Cells(row, 17).Value + ", " + ActiveSheet.Cells(row + 1, 17).Value
        ActiveSheet.Rows(row + 1).Delete
        
        row = row - 1
        
      End If
    End If
    
  Next

End Sub

Private Sub AddMfrPartNum()
'  This routine adds the manufacturer name and part number onto the end of the description.

  Dim row As Integer
  
  For row = 2 To LastRowNum
    Select Case UCase(ActiveSheet.Cells(row, 2).Value)
      Case "ESI", "GENERIC", "MS", "RM"
'       Do nothing
        ActiveSheet.Cells(row, 19).Value = ""
      Case Else
        Select Case Right(UCase(ActiveSheet.Cells(row, 7).Value), 3)
          Case "_PL", "_AI", "_AM"
'           Do nothing
            ActiveSheet.Cells(row, 19).Value = ""
          Case Else
'             Add manufacturer name and part number onto end of description
            ActiveSheet.Cells(row, 19).Value = FindMfr(row)
            ActiveSheet.Cells(row, 6).Value = ActiveSheet.Cells(row, 6).Value + ", " + ActiveSheet.Cells(row, 19).Value + " #" + ActiveSheet.Cells(row, 7).Value
        End Select
    End Select
  
  Next
End Sub

Private Sub ElimRMMS()
'  This routine eliminates the rows that have RM (Raw Materials) or MS (Machine Supplies) in the vendor because they won't be ordered with the bom.

  Dim row As Integer
  
  For row = 2 To LastRowNum
    If ActiveSheet.Cells(row, 3).Value = "" Then
      Exit For
    End If

    Select Case UCase(ActiveSheet.Cells(row, 2).Value)
      Case "MS", "RM"
        ActiveSheet.Rows(row).Delete
    End Select
  Next

End Sub

Private Sub AddJobQtyMatlPrice()
' This routine multiplies the updated parts quantity (NewQty column) by the Job Qty entered on the form.  It then puts the result back into the original Qty column.
'   Then it adds the forumula for Material price column, if it is not an ESI part number

  Dim row As Integer
  Dim MatlCostFormula As String
  
  
  For row = 2 To LastRowNum
    ActiveSheet.Cells(row, 3).Value = ActiveSheet.Cells(row, 4) * JobQty
    If ActiveSheet.Cells(row, 2).Value <> "ESI" Then
      If ActiveSheet.Cells(row, 9).Value = "" Then ActiveSheet.Cells(row, 9).Value = 0
      MatlCostFormula = "=R" + CStr(row) + "C3*R" + CStr(row) + "C9"
'      Debug.Print MatlCostFormula
'      ActiveSheet.Cells(row, 10).Formula = Application.ConvertFormula(MatlCostFormula, xlR1C1, xlA1, xlRelative)
      ActiveSheet.Cells(row, 10).Formula = objXL.ConvertFormula(MatlCostFormula, xlR1C1, xlA1, xlRelative)
    End If
  Next

End Sub

Private Sub UpdateExcelBOM()
' This routine updates the open job specific ESI excel bom from the temp bom file

  Dim PrevActBook As String
  Dim PrevActSheet As String
  Dim row As Integer
  
  
  PrevActBook = ActiveWorkbook.Name
  PrevActSheet = ActiveSheet.Name
  
  Workbooks(FileNameBom + FileExtXls).Activate
  Worksheets(FileNameBom).Activate
  
  row = FindMechRow + 1
'  Debug.Print row
  ActiveSheet.Rows(row).Insert  ' Inserts a blank row after Mechanical header cell
  
  Workbooks(PrevActBook).Activate
  Worksheets(PrevActSheet).Activate
  
  ActiveSheet.Range(Cells(2, 1), Cells(LastRowNum, 1)).EntireRow.Copy
  
  Workbooks(FileNameBom + FileExtXls).Activate
  Worksheets(FileNameBom).Activate
  
  ActiveSheet.Cells(row, 1).Insert  ' Inserts the copied cells from the temp bom file to just below the Mechanical header cell
  
  ActiveSheet.Range(Cells(11, 1), Cells(LastRowNum + (11 - 2), 1)).EntireRow.AutoFit
  
  Workbooks(FileNameBom + FileExtXls).Save  ' Saves the excel bom file

End Sub

Private Function FindLastColumn() As Integer
  
  Dim y As Integer
  
  For y = 1 To 500
    If ActiveSheet.Cells(1, y).Value = "" Then
      FindLastColumn = y - 1
      Exit Function
    End If
  Next
  
End Function

Private Function FindLastRow() As Integer
  
  Dim row As Integer
  
  For row = 2 To 10000
  '  Use 3rd column (QTY) to check for number of rows. Other columns may contain blanks
    If ActiveSheet.Cells(row, 3).Value = "" Then
      FindLastRow = row - 1
      Exit Function
    End If
  Next
  
End Function

Private Function FindNumLeadingSpaces(row As Integer, column As Integer) As Integer
' Function finds the number of leading spaces in a text string

  Dim CellText As String
  Dim TextLen As Long
  Dim x As Long
  Dim NumSpaces As Integer
  
  CellText = ActiveSheet.Cells(row, column).Value
  TextLen = Len(CellText)
  NumSpaces = 0
  
  For x = 1 To TextLen
    If Mid(CellText, x, 1) = Chr(32) Then
      NumSpaces = NumSpaces + 1
    Else
      Exit For
    End If
  Next x
  
  'Debug.Print NumSpaces, CellText
  
  FindNumLeadingSpaces = NumSpaces

End Function

Private Function BuildItemString() As String
' Function builds the indented item string in a A.B.C.D... format

  Dim tempstring As String
  Dim x As Integer
  
  tempstring = CStr(ItemLevel(0))
  
  For x = 1 To 19
    If ItemLevel(x) = 0 Then
      Exit For
    Else
'    Debug.Print ItemLevel(x)
      tempstring = tempstring + "." + CStr(ItemLevel(x))
    End If
  Next

  BuildItemString = tempstring

End Function

Private Function CalculateQty(row As Integer, Levelptr As Integer) As Integer
  
  Dim Qty As Integer
  Dim x As Integer
  
  Qty = ActiveSheet.Cells(row, 3).Value
  
  For x = Levelptr - 1 To 0 Step -1
    Qty = Qty * ItemLvlQty(x)
  Next
  
  CalculateQty = Qty

End Function

Private Function FindMfr(row As Integer) As String
'  This function finds the manufacturer name as it is in the file path string in Solidworks.

  Dim tempstring As String
  Dim stringlen As Integer
  Dim x As Integer
  
' Find the unique identifier \DA\MDA\ string, then remove any characters before this string
  stringlen = Len(ActiveSheet.Cells(row, 19).Value)
  For x = 1 To stringlen - 8
    If UCase(Mid(ActiveSheet.Cells(row, 19).Value, x, 8)) = "\DA\MDA\" Then
      tempstring = UCase(Right(ActiveSheet.Cells(row, 19).Value, stringlen - (x - 1 + 8)))
      Exit For
    End If
  Next

' In the string that's left over, find the next \ and remove any characters to the right of it, leaving the manufacturer's name.  Return this name.
  stringlen = Len(tempstring)
  For x = 1 To stringlen
    If Mid(tempstring, x, 1) = Chr(92) Then       ' chr(92)= \
      FindMfr = Left(tempstring, x - 1)
      Exit For
    End If
  Next

End Function

Private Function FindMechRow() As Integer
' Finds the row that has MECHANICAL.  Indicates the start of the mechanical bom.
'  Assumes the active workbook is the job number excel bom file.

  Dim row As Integer
  
  For row = 1 To 1000
    If UCase(CStr(ActiveSheet.Cells(row, 1).Value)) = "MECHANICAL" Then
      FindMechRow = row
      Exit Function
    End If
  Next

End Function

 

[HughLerwill]

dbrew0,

Some features in your VBA code need further adaption when transfered into VB6. For example your FindMechRow routine;

Private Function FindMechRow() As Integer
' Finds the row that has MECHANICAL. Indicates the start of the mechanical bom.
' Assumes the active workbook is the job number excel bom file.

Dim row As Integer

For row = 1 To 1000
If UCase(CStr(objXL.ActiveSheet.Cells(row, 1).Value)) = "MECHANICAL" Then
FindMechRow = row
Exit Function
End If
Next

End Function

You should be qualifying Your Excel object when using ActiveSheet as in the above. Check the rest of your code for similar usage (I have not). Not qualifying Your Excel object in front of Excel methods or properties will likely lead to other undesirable outcomes not just slowness.

HTH Hugh,

 

[HughLerwill]

dbrew0,

With regard to FindMechRow again. I would also checkout use of Excels Find method which would probably be quicker under VBA and VB6.

something like this (untested)in VBA;

Dim r as Range
Set r = Cols(1).Find(what:="MECHANICAL", after:=Cells(1, 1))
if r is nothing then
msgbox "Not found"
else
msgbox "Found on row " & r.row
end if

HTH Hugh,

 

[dbrew0]

Hugh,

Thanks for the replies. I've gone through and qualified all of the Excel methods and properties with the Excel object. I didn't realize that was necessary, but it make sense. That improved speed a little.

Upon further troubleshooting, I've noticed that the code gets slow when it is working through something row by row:
(i.e. in the CalcQtyOfSubParts() routine I have

For FirstRowNum to LastRowNum
.
.
.
Next

I can watch the app fill in each cell that needs to change as it works its way down the spreadsheet. In my test spreadsheet, I've got about 200 rows that have to be gone through one at a time checking for various things and modifying specific cells, etc. Is there a reason why a native Excel VBA routine would run through the same for...next loop at lightening speed and the VB6 would be slow?

Thanks,
Dan

 

[HughLerwill]

dbrew0,

With regard to your FindFirstRow/Col routines Excel will do it for you and probably faster in VBA and VB.

Try stuff like;

rowLast = Cells.SpecialCells(xlLastCell).Row

colLast = Cells.SpecialCells(xlLastCell).Column

HTH Hugh

 

[dbrew0]

Oh and one other general question, when I'm qualifying the Excel methods and properties, can I use the objXL. just at the beginning of the method and not have to use it on each part of the method?

Like (without qualifying)
ActiveSheet.Range(Cells(2, 1), Cells(LastRowNum, 1)).EntireRow.Copy

Is this sufficient?
objXL.ActiveSheet.Range(Cells(2, 1), Cells(LastRowNum, 1)).EntireRow.Copy

Or do I need to put the objXL in front of the Cells as well?

Dan

 

[HughLerwill]

dbrew0,

Cells as well I'm afraid AND you will probably need ObjXl.ActiveSheet.Cells(.... This extends to just about any Excel specific keyword (property, method or object) that VB6 does not natively understand.

Following on from the above; Checkout using the With keyword in you code it could save you a lot of typing and run faster too, try stuff like;

With ObjXL.ActiveSheet
.Cells(1,12)="row 12"
.Cells(1,13)="row 13"
end with

(from your last code I notice you are using early binding and so must have a reference into the Excel library)
You can debug the required qualification by using late binding as in;

dim ObjXl as object
set ObjXl = CreateObject(Excel.Application)

Then drop the reference to the Excel libray. Then try to compile the code. VB should refuse to compile and highlight the unqualified references. When and if it compiles the qualifications are all done.
When you drop the reference to the Excel library intellisense in the ide will cease to work on Excel keywords which is inconvienient so after you have debugged the qualifications you can reinstall the reference to the Excel library(and optionally return to early binding too).

HTH Hugh,



HTH Hugh,

 

[BobRodes]

Sorry, I didn't look in detail at all the responses, as I'm not a stone expert in working with the Excel Object Library. However, often the slowdown problems with Excel Automation applications have to do with marshaling data across a process boundary. The VB application runs in one process, and the Excel application runs in another, and there is considerable overhead having one EXE talking to another when compared to having an EXE talk to itself.

So, you'll definitely want to check things like accessing the Excel application object in a loop in VB. That will run very slowly. Best practice is to write top level procedures in the Excel environment, and call those procedures through the automation object. Do not attempt to use the automation object directly for detailed Excel VBA work, or you will have performance issues.

HTH

JBob

 

[dbrew0]

Hugh - Thanks for the suggestions. I've implemented them and have seen a small improvement.

Bob - thanks for the input. I understand what you are saying and it makes a lot of sense. That seems like what is happening. When I am cycling through each row in a loop, I'm checking things using the Excel object from within the VB code, which obviously has to cross the bounderies with data. I never thought crossing bounderies like that would cause such a slow down in execution. Thanks for the insight.

Bob, would the same thing happen if I used VB 2005? I know it's on the .NET platform and that has it's own set of issues with syntax, etc. I'm just wondering if the .NET platform would help this situation.

Dan

 

[BobRodes]

No, I don't believe that .Net will help you. .Net also uses an Automation client to access Excel. As long as two processes are trying to talk to one another, there will be overhead when marshaling data between them. (Er...sorry about this..."This behavior is by design.") The issue has to do with interprocess communication, rather than which development platform you're using.

What happens with marshaling is that each application sends data as if it were in its own process. The client sends data to a "proxy" of the server, which resides in its own process. Then the proxy packages and transports the data. A "stub" of the client, residing in the server's process, then retrieves and presents the data in the server's process. This creates a "level of indirection" between the client and server, meaning that the actual sending of data between processes (again, called "marshaling") is encapsulated from those processes. This means that the marshaling process can be improved or altered without breaking the applications using it. But, it also means that there is overhead associated with the various layers of code that are accessed in interprocess communication.

Now, Microsoft recommends minimizing marshaling traffic when using Automation, and there are several things that you can do with your existing application to accomplish this.

First, wherever possible, let your application do its work in the Excel process. Put top level procs in your Excel process, and call those procs from your VB application. Second, if you're sending data across the boundary each time you iterate through a loop, consider instead putting needed data into an array in the Excel process, and then accessing the array in your VB process. This will cut down signifcantly on marshaling overhead.

There are plenty of other ideas you will probably be able to come up with. The basic themes of these ideas are to minimize round trips between the processes, and therefore to do as much preparation work as possible from the Excel environment.

HTH

Bob

p. s. if you want to get a little better handle on why marshaling creates overhead, the diagram in

http://windowssdk.msdn.microsoft.com/en-us/library/ms692621.aspx

should be instructive.

 

[dbrew0]

Thanks for the reply Bob. And for the insight into MS and the 'behavior by design' thought processes.

It's now making a lot more sense.

Dan

 

[BobRodes]

Good luck! There are plenty of things that you can do to minimize marshaling traffic.

Bob

 

[HughLerwill]

dbrew0,

Another tweak, which may speed things up a little more.

You make much use of ActiveSheet; I would expect ActiveSheet to be slower than directly addressing a specific Sheet. You could try assigning ActiveSheet to a variable dimmed/ set as Sheet and using that specific Sheet in preference to repeated use of ActiveSheet.

HTH Hugh