Need some new eyes for refactoring....

Printable View

I wrote a bunch of VB/VBA and it seems to be running a little slow. The following process takes 20ish seconds to complete and I was wondering if anyone see's any areas that could be improved for better performance.

vb Code:

Private Sub Button3_Click(sender As System.Object, e As System.EventArgs) Handles Button3.Click
        Dim datenow As String = DateTime.Now.ToString("MMM dd")
        xlApp = New Excel.Application
        xlbook = xlApp.Workbooks.Open(xlspath)
        xlsheet1 = xlbook.Worksheets("Sheet1")
        xlbook.Sheets("Sheet1").Name = "Summary"
        xlbook.Sheets("Sheet2").delete()
        xlbook.Sheets("Sheet3").delete()
        With xlsheet1
             .Columns("K:J").delete()
             .Columns("A:A").delete()
             .Columns("B:B").insert()
             .Cells(1, 1).Copy()
             .Cells(1, 2).PasteSpecial(Paste:=Excel.Constants.xlFormats, Operation:=Excel.Constants.xlNone, SkipBlanks:=False, Transpose:=False)
             .Cells(1, 2) = "Pic"
             .Cells(1, 8) = "APP"
             .Cells(1, 9) = "APP Version"
        End With
 
        Dim lastsheet = "Summary"
        For x = 2 To xlsheet1.Rows.Count
            If xlsheet1.Cells(x, 1).value = Nothing Then
                Exit For
            Else
                xlbook.Worksheets.Add(After:=xlbook.Worksheets(lastsheet))
                lastsheet = CStr(xlsheet1.Cells(x, 1).value)
                xlbook.Sheets(CStr("Sheet" & CStr(x - 1))).Name = lastsheet
                For y = 1 To xlsheet1.Columns.Count
                    If xlsheet1.Cells(1, y).value = Nothing Then
                        Exit For
                    Else
                        xlsheet1.Cells(1, y).Copy()
                        With xlbook.Sheets(lastsheet)
                            .cells(1, y).PasteSpecial(Paste:=Excel.Constants.xlFormats, Operation:=Excel.Constants.xlNone, SkipBlanks:=False, Transpose:=False)
                            .cells(1, y) = xlsheet1.Cells(1, y).value
                            .cells(2, y) = xlsheet1.Cells(x, y).value
                        End With
                    End If
                Next
                With xlbook.Sheets(lastsheet)
                    .columns("B:B").delete()
                    .columns("A:k").HorizontalAlignment = Excel.Constants.xlLeft
                    .columns("A:k").VerticalAlignment = Excel.Constants.xlTop
                    .columns("A:k").wraptext = True
                    .columns("A:k").autofit()
                    .columns("i:i").columnwidth = 20
                    .columns("j:j").columnwidth = 60
                    .columns("K:K").columnwidth = 60
                    .rows("1:100").autofit()
                End With
                xlsheet1.Hyperlinks.Add(Anchor:=xlsheet1.Cells(x, 1), Address:="", SubAddress:="'" & lastsheet & "'!A1", TextToDisplay:=(lastsheet))
                Dim pix = 100
                Dim txt = 215
                For Each foundFile As String In My.Computer.FileSystem.GetFiles(ComboBox1.SelectedItem, FileIO.SearchOption.SearchTopLevelOnly, "*" & lastsheet & "*")
                    Dim extension = Path.GetExtension(foundFile).ToLower.ToString
                    If extension = ".log" Or extension = ".txt" Then
                        xlbook.Sheets(lastsheet).OLEObjects.Add(FileName:=foundFile, Link:=False, DisplayAsIcon:=True, IconFileName:=Environment.GetFolderPath(Environment.SpecialFolder.Windows) & "\Notepad.exe", IconIndex:=0, IconLabel:=Path.GetFileName(foundFile), Left:=265, Top:=txt, Width:=75, Height:=75)
                        txt += 80
                        xlsheet1.Cells(x, 2) = xlsheet1.Cells(x, 2).value + "X"
                    ElseIf extension = ".png" Or extension = ".jpg" Or extension = ".jpeg" Or extension = ".gif" Then
                        xlbook.Sheets(lastsheet).Shapes.AddPicture(foundFile, Microsoft.Office.Core.MsoTriState.msoCTrue, Microsoft.Office.Core.MsoTriState.msoCTrue, 25, pix, 215, 350)
                        pix += 350
                        xlsheet1.Cells(x, 2) = xlsheet1.Cells(x, 2).value + "X"
                    End If
                Next
            End If
        Next
        With xlsheet1
            .Columns("D:D").delete()
            .Columns("J:L").delete()
            .Columns("C:C").delete()
            .Columns("A:K").autofit()
            .Columns("A:G").HorizontalAlignment = Excel.Constants.xlCenter
            .Cells(1, 8).HorizontalAlignment = Excel.Constants.xlCenter
            .Columns("A:H").VerticalAlignment = Excel.Constants.xlTop
        End With
        Dim sheet As Excel.Worksheet
        Dim title = xlsheet1.Cells(2, 6).value & "v" & xlsheet1.Cells(2, 7).value & " Defects " & xlsheet1.Cells(2, 3).value & " " & datenow & ".xls"
        Dim header = "&20 &B" & xlsheet1.Cells(2, 6).value & " " & xlsheet1.Cells(2, 7).value
        For Each sheet In xlbook.Worksheets
            sheet.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape
            sheet.PageSetup.LeftHeader = "&D &T"
            sheet.PageSetup.CenterHeader = header.ToString
            sheet.PageSetup.LeftFooter = title.ToString
            sheet.PageSetup.RightFooter = "&P/&N"
            sheet.PageSetup.Zoom = False
            sheet.PageSetup.FitToPagesTall = 2
            sheet.PageSetup.FitToPagesWide = 1
        Next
        xlsheet1.Activate()
        xlbook.SaveAs(Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory) & "\" & title.ToString)
CloseExcel()
End Sub

What the program does is load an exported excel report from another program and reconfigures it. First thing it does is it makes a new tab for each row, then it looks through a file for pictures or text documents containing the same name and attaches them. Finally, it changes the print settings and saves it to the desktop. I seem to have hit a small wall, and other then some syntax here and there Im not seeing much else.

Any and all suggestions are appreciated.

Zach

*edit - If anyone would like to see a sample unedited report or the finished product, let me know.

May 16th, 2012, 07:01 PM
kareninstructor

Re: Need some new eyes for refactoring....

Hello,

To the naked eye the code looks fine. One thing to try is ripping one part out, retry and see if that speeds up the process. First try without doing the image then try anything that does a copy or paste. If possible work on one chunk at a time.

Re: Need some new eyes for refactoring....

alright I did that, and it seems that the part that is taking 18 of the 20ish seconds is this chunk...

vb Code:

Dim sheet As Excel.Worksheet
        Dim title = xlsheet1.Cells(2, 6).value & "v" & xlsheet1.Cells(2, 7).value & " Defects " & xlsheet1.Cells(2, 3).value & " " & datenow & ".xls"
        Dim header = "&20 &B" & xlsheet1.Cells(2, 6).value & " " & xlsheet1.Cells(2, 7).value
        For Each sheet In xlbook.Worksheets
            sheet.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape
            sheet.PageSetup.LeftHeader = "&D &T"
            sheet.PageSetup.CenterHeader = header.ToString
            sheet.PageSetup.LeftFooter = title.ToString
            sheet.PageSetup.RightFooter = "&P/&N"
            sheet.PageSetup.Zoom = False
            sheet.PageSetup.FitToPagesTall = 2
            sheet.PageSetup.FitToPagesWide = 1
        Next

Is there a better way than this?

May 17th, 2012, 01:29 PM
Zmcpherson

Re: Need some new eyes for refactoring....

*edit*

deleted, I solved this unrelated issue.
May 17th, 2012, 03:38 PM
kareninstructor

Re: Need some new eyes for refactoring....
Quote:
Originally Posted by Zmcpherson

alright I did that, and it seems that the part that is taking 18 of the 20ish seconds is this chunk...

vb Code:

Dim sheet As Excel.Worksheet
Dim title = xlsheet1.Cells(2, 6).value & "v" & xlsheet1.Cells(2, 7).value & " Defects " & xlsheet1.Cells(2, 3).value & " " & datenow & ".xls"
Dim header = "&20 &B" & xlsheet1.Cells(2, 6).value & " " & xlsheet1.Cells(2, 7).value
For Each sheet In xlbook.Worksheets
sheet.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape
sheet.PageSetup.LeftHeader = "&D &T"
sheet.PageSetup.CenterHeader = header.ToString
sheet.PageSetup.LeftFooter = title.ToString
sheet.PageSetup.RightFooter = "&P/&N"
sheet.PageSetup.Zoom = False
sheet.PageSetup.FitToPagesTall = 2
sheet.PageSetup.FitToPagesWide = 1
Next

Is there a better way than this?
Okay this is all guessing since when I work this type of operation it is with a third party library called Aspose Cells where there is no automation thus lighting fast.

I would try first setting the variable title and header to an empty string, does this speed this up? If so I think there is a bottle-neck with how each or one of the variables (title or header) are gotten. You could cut to the chase and do one at a time as per above and find out only one is the issue.

Second, get rid of the for/each for a second, do only one sheet. If there are say three sheets does cutting it down to one sheet take a third of the time?

Code:

Dim title = xlsheet1.Cells(2, 6).value & "v" & xlsheet1.Cells(2, 7).value & " Defects " & xlsheet1.Cells(2, 3).value & " " & datenow & ".xls" Dim header = "&20 &B" & xlsheet1.Cells(2, 6).value & " " & xlsheet1.Cells(2, 7).value For Each sheet In xlbook.Worksheets sheet.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape sheet.PageSetup.LeftHeader = "&D &T" sheet.PageSetup.CenterHeader = header.ToString sheet.PageSetup.LeftFooter = title.ToString sheet.PageSetup.RightFooter = "&P/&N" sheet.PageSetup.Zoom = False sheet.PageSetup.FitToPagesTall = 2 sheet.PageSetup.FitToPagesWide = 1 Next

Also I am assuming you have Option Strict set to Off since xlsheet1.Cells(2, 6).value returns an Object, not a String which means there is lating binding which when added up every time the run time engine needs to translate will slow things down. The trade off is when turning Option Strict On your code will need to change so it will compile. Also some code may very well not easily be made to work as before so if you do this I highly recommend backing up your project to another location before starting.

Re: Need some new eyes for refactoring....

Alright, I ran a bunch of tests and here is what I have come up with.

To come up with the seconds I did...
dim EndTime = TimeNow.subtract(StartTime)

It takes 42.78 seconds to run through this:

vb Code:

For Each sheet In xlbook.Worksheets
            sheet.Columns.WrapText = True
            sheet.Columns.HorizontalAlignment = Excel.Constants.xlLeft
            sheet.Columns.VerticalAlignment = Excel.Constants.xlTop
            sheet.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape
            sheet.PageSetup.LeftHeader = datenow.ToString & " " & starttime.ToString
            sheet.PageSetup.CenterHeader = header.ToString
            sheet.PageSetup.LeftFooter = title.ToString
            sheet.PageSetup.RightFooter = "&P/&N"
            sheet.PageSetup.Zoom = False
            sheet.PageSetup.FitToPagesWide = 1
            sheet.PageSetup.FitToPagesTall = 2
            sheet.Rows.AutoFit()
            sheet.Columns.AutoFit()
        Next

It takes 3.53 seconds to run through this:

vb Code:

For Each sheet In xlbook.Worksheets
            sheet.Columns.WrapText = True
            sheet.Columns.HorizontalAlignment = Excel.Constants.xlLeft
            sheet.Columns.VerticalAlignment = Excel.Constants.xlTop
            sheet.Rows.AutoFit()
            sheet.Columns.AutoFit()
        Next

It takes 27.26 seconds to run though this:

vb Code:

For Each sheet In xlbook.Worksheets
            sheet.Columns.WrapText = True
            sheet.Columns.HorizontalAlignment = Excel.Constants.xlLeft
            sheet.Columns.VerticalAlignment = Excel.Constants.xlTop
            sheet.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape
            sheet.PageSetup.LeftHeader = datenow.ToString & " " & starttime.ToString
            sheet.PageSetup.RightFooter = "&P/&N"
            sheet.PageSetup.Zoom = False
            sheet.PageSetup.FitToPagesWide = 1
            sheet.PageSetup.FitToPagesTall = 2
            sheet.Rows.AutoFit()
            sheet.Columns.AutoFit()
        Next

and 15.23 seconds for this:

vb Code:

For Each sheet In xlbook.Worksheets
            sheet.Columns.WrapText = True
            sheet.Columns.HorizontalAlignment = Excel.Constants.xlLeft
            sheet.Columns.VerticalAlignment = Excel.Constants.xlTop
            sheet.PageSetup.CenterHeader = header.ToString
            sheet.PageSetup.LeftFooter = title.ToString
            sheet.PageSetup.Zoom = False
            sheet.PageSetup.FitToPagesWide = 1
            sheet.PageSetup.FitToPagesTall = 2
            sheet.Rows.AutoFit()
            sheet.Columns.AutoFit()
        Next

It looks like most of the time is spent in the Date and time in the left header, the right footer, and the page orientation. The date and time are already a fixed string, why is it taking so long to do that? Are there anyways to cut any of the above down?

Its true, I usually do not keep option strict on when I am working with VBA. How would you write this with option strict on?:

vb Code:

Dim header = "&20 &B" & xlsheet1.Cells(2, 6).value & " " & xlsheet1.Cells(2, 7).value

or this?

vb Code:

With xlbook.Sheets(lastsheet)
                    .columns("B:B").delete()
                    .columns("i:i").columnwidth = 20
                    .columns("j:K").columnwidth = 60
                End With

May 22nd, 2012, 05:12 PM
kareninstructor

Re: Need some new eyes for refactoring....
Quote:
Originally Posted by Zmcpherson

Alright, I ran a bunch of tests and here is what I have come up with.

To come up with the seconds I did...
dim EndTime = TimeNow.subtract(StartTime)

It takes 42.78 seconds to run through this:

vb Code:

For Each sheet In xlbook.Worksheets
sheet.Columns.WrapText = True
sheet.Columns.HorizontalAlignment = Excel.Constants.xlLeft
sheet.Columns.VerticalAlignment = Excel.Constants.xlTop
sheet.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape
sheet.PageSetup.LeftHeader = datenow.ToString & " " & starttime.ToString
sheet.PageSetup.CenterHeader = header.ToString
sheet.PageSetup.LeftFooter = title.ToString
sheet.PageSetup.RightFooter = "&P/&N"
sheet.PageSetup.Zoom = False
sheet.PageSetup.FitToPagesWide = 1
sheet.PageSetup.FitToPagesTall = 2
sheet.Rows.AutoFit()
sheet.Columns.AutoFit()
Next

It takes 3.53 seconds to run through this:

vb Code:

For Each sheet In xlbook.Worksheets
sheet.Columns.WrapText = True
sheet.Columns.HorizontalAlignment = Excel.Constants.xlLeft
sheet.Columns.VerticalAlignment = Excel.Constants.xlTop
sheet.Rows.AutoFit()
sheet.Columns.AutoFit()
Next

It takes 27.26 seconds to run though this:

vb Code:

For Each sheet In xlbook.Worksheets
sheet.Columns.WrapText = True
sheet.Columns.HorizontalAlignment = Excel.Constants.xlLeft
sheet.Columns.VerticalAlignment = Excel.Constants.xlTop
sheet.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape
sheet.PageSetup.LeftHeader = datenow.ToString & " " & starttime.ToString
sheet.PageSetup.RightFooter = "&P/&N"
sheet.PageSetup.Zoom = False
sheet.PageSetup.FitToPagesWide = 1
sheet.PageSetup.FitToPagesTall = 2
sheet.Rows.AutoFit()
sheet.Columns.AutoFit()
Next

and 15.23 seconds for this:

vb Code:

For Each sheet In xlbook.Worksheets
sheet.Columns.WrapText = True
sheet.Columns.HorizontalAlignment = Excel.Constants.xlLeft
sheet.Columns.VerticalAlignment = Excel.Constants.xlTop
sheet.PageSetup.CenterHeader = header.ToString
sheet.PageSetup.LeftFooter = title.ToString
sheet.PageSetup.Zoom = False
sheet.PageSetup.FitToPagesWide = 1
sheet.PageSetup.FitToPagesTall = 2
sheet.Rows.AutoFit()
sheet.Columns.AutoFit()
Next

It looks like most of the time is spent in the Date and time in the left header, the right footer, and the page orientation. The date and time are already a fixed string, why is it taking so long to do that? Are there anyways to cut any of the above down?

Its true, I usually do not keep option strict on when I am working with VBA. How would you write this with option strict on?:

vb Code:

Dim header = "&20 &B" & xlsheet1.Cells(2, 6).value & " " & xlsheet1.Cells(2, 7).value

or this?

vb Code:

With xlbook.Sheets(lastsheet)
.columns("B:B").delete()
.columns("i:i").columnwidth = 20
.columns("j:K").columnwidth = 60
End With
When in doubt not knowing how to do something with Excel use Google. I don't know what columns("B:B").delete() does for sure but would guess it deletes a column. So I type the following into Google vb.net excel delete column and look for this forum, StackOverflow or MSDN forums. As fate would have it the second link was MSDN forum with C Sharp code which at least to me seems good other than tunnelling (using more than one ".") so I converted it and ran the code as shown below, worked great. If I were to use this in my app I would de-tunnel the code.

Code:

Dim MyVar = xlWorkSheet.Range("A1", "B1") MyVar.EntireColumn.Delete(Nothing)

To ensure disposal of objects

Code:

GC.Collect() GC.WaitForPendingFinalizers() GC.Collect() GC.WaitForPendingFinalizers()

Crap, of course I cannot let the code above go with tunneling so here is a clean version meaning all objects are properly disposed.

Code:

Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click Dim InitialPath As String = "C:\DotnetLand2010\Excel\ExcelDemo\bin\Debug" OpenFileDialog1.Title = "Please select a file to open" OpenFileDialog1.FileName = "" If IO.Directory.Exists(InitialPath) Then OpenFileDialog1.InitialDirectory = InitialPath Else OpenFileDialog1.InitialDirectory = System.Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments) End If OpenFileDialog1.Filter = "Excel 2007 (*.xlsx)|*.xlsx|Excel pre 2007|*.xls" If OpenFileDialog1.ShowDialog = Windows.Forms.DialogResult.OK Then Dim xlApp As Excel.Application = Nothing Dim xlWorkBooks As Excel.Workbooks = Nothing Dim xlWorkBook As Excel.Workbook = Nothing Dim xlWorkSheet As Excel.Worksheet = Nothing xlApp = New Excel.Application xlApp.DisplayAlerts = False xlWorkBooks = xlApp.Workbooks xlWorkBook = xlWorkBooks.Open(OpenFileDialog1.FileName) xlApp.Visible = False If CheckBox1.Checked Then xlWorkSheet = CType(xlWorkBook.Sheets(1), Excel.Worksheet) Else xlWorkSheet = CType(xlWorkBook.ActiveSheet, Excel.Worksheet) End If Dim xlCells As Excel.Range = Nothing Dim xlRangeToRemove = xlWorkSheet.Range("A1:B1") Dim MyRange = xlRangeToRemove.EntireColumn MyRange.Delete(Nothing) xlWorkBook.SaveAs(OpenFileDialog1.FileName, Excel.XlFileFormat.xlOpenXMLWorkbook) xlWorkBook.Close() xlApp.UserControl = True xlApp.Quit() If Not MyRange Is Nothing Then Marshal.FinalReleaseComObject(MyRange) MyRange = Nothing End If If Not xlRangeToRemove Is Nothing Then Marshal.FinalReleaseComObject(xlRangeToRemove) xlRangeToRemove = Nothing End If If Not xlWorkSheet Is Nothing Then Marshal.FinalReleaseComObject(xlWorkSheet) xlWorkSheet = Nothing End If If Not xlWorkBook Is Nothing Then Marshal.FinalReleaseComObject(xlWorkBook) xlWorkBook = Nothing End If If Not xlWorkBooks Is Nothing Then Marshal.FinalReleaseComObject(xlWorkBooks) xlWorkBooks = Nothing End If If Not xlApp Is Nothing Then Marshal.FinalReleaseComObject(xlApp) xlApp = Nothing End If MessageBox.Show("Done") End If End Sub

One last thought, sometimes you will not find code for VB.NET, instead code such as this link. That site is a good one, if you look at his example for use in Excel and examine the VB.NET version they are close, we are using a Range and calling the same method, just in a different way.

Code:

Sub DeleteEmptyColumns(DeleteRange As Range) ' Deletes all empty columns in DeleteRange ' Example: DeleteEmptyColumns Selection ' Example: DeleteEmptyColumns Range("A1:Z1") Dim cCount As Integer, c As Integer If DeleteRange Is Nothing Then Exit Sub If DeleteRange.Areas.Count > 1 Then Exit Sub With DeleteRange cCount = .Columns.Count For c = cCount To 1 Step -1 If Application.CountA(.Columns(c)) = 0 Then .Columns(c).EntireColumn.Delete End If Next c End With End Sub

With all the above this is why I avoid using Excel automation but tinker with it for fun. Any time you have the $$$'s to spend for working with Excel libraries such as Aspose Cells pays for itself quickly and to boot does not require Excel to be installed.

So now you have a thought process of figuring out the other methods.

Hope this helps.

Re: Need some new eyes for refactoring....

man this is going to look stupid but... i figured it out... needless to say, im downloadind aspose right now to give it a whirl...

turns out

vb Code:

With xlbook.Sheets(lastsheet)
                        .columns("B:B").delete()
                        .columns("i:i").columnwidth = 20
                        .columns("j:K").columnwidth = 60
                    End With

can be turned into this without Option Strict errors:

vb Code:

With xlsheetDef
                    .Range("B:B").Delete()
                    .Range("i:i").ColumnWidth = 20
                    .Range("j:K").ColumnWidth = 60
                End With

and ...

vb Code:

lastsheet = xlsheet1.Cells(x, 1).value

is going to be this....

vb Code:

lastsheet = Convert.ToString(CType(xlsheet1.Cells(x, 1), Excel.Range).Value)

I cant see this being any faster : P

May 23rd, 2012, 06:16 PM
Zmcpherson

Re: Need some new eyes for refactoring....

Can I run multiple threads for this process?

Say I open 3 new threads that run different threadtasks()

and then I split the work (everything in the "for each" loop) up between the main thread and the three new threads. Would they complete the entire task faster?
May 24th, 2012, 08:21 AM
kareninstructor

Re: Need some new eyes for refactoring....

Quote:

Originally Posted by Zmcpherson

Can I run multiple threads for this process?

Say I open 3 new threads that run different threadtasks()

and then I split the work (everything in the "for each" loop) up between the main thread and the three new threads. Would they complete the entire task faster?

Only way to know is to try.
May 24th, 2012, 11:35 AM
Zmcpherson

Re: Need some new eyes for refactoring....

lol yeah it just freezes the computer to the point where a restart is necessary. Not a very useful program at that point : P .

Explain how this aspose thing works. I imported the dll like a com reference, Im guessing at the top of the class I use "Imports Aspose.cells". Now what? do I use the same syntax as Microsoft.Interop? ...

what would I need to change, say:

xlApp = New Excel.Application
xlbook = xlApp.Workbooks.Open(OpenFileDialog1.FileName)

to get it to work?
May 24th, 2012, 12:06 PM
kareninstructor

Re: Need some new eyes for refactoring....

You add Aspose Cells library as a reference to your project. Make sure Copy Local is set to True which means the dll will be placed into the same folder as your executable.

Example projects should be under C:\Program Files\Aspose\Aspose.Cells\Demos while help is under C:\Program Files\Aspose\Aspose.Cells\Help although when I need help I use online help at the Aspose web site.

If you purchase Aspose Cells then you need to include a license file with your app placed usually in the app executable folder. I use the following code to activate the license but you will not have that for the eval.

Code:

Module AsposeSupport Private LicenseFile As String = IO.Path.Combine(Application.StartupPath, "Aspose.Cells.lic") <System.Diagnostics.DebuggerStepThrough()> _ Public Sub PrepareCellsLibrary() If IO.File.Exists(LicenseFile) Then Dim license As Aspose.Cells.License = New Aspose.Cells.License license.SetLicense(LicenseFile) End If End Sub End Module

I went with the cells library after working with their PDF kit library for creating PDF and extraction of data from existing PDF files.
May 24th, 2012, 02:00 PM
Zmcpherson

Re: Need some new eyes for refactoring....

Do you truly believe that this will compile that bit of code faster?

I really respect you and your opinion, and I appreciate everything you do on these boards, but I would hate to learn a whole new set of objects and then rewrite all of my code for an equal or for a very marginal increase in speed.

However, if your telling me this will change the way I do vb and vba forever, then Im on board for sure.
May 25th, 2012, 09:16 AM
kareninstructor

Re: Need some new eyes for refactoring....

I can guarantee Aspose Cells library is used properly you will see a difference in speed from opening an Excel file to work done within the file. With that said, the opposite is equally true, use Aspose Cells incorrectly and you will not see operations anything but marginally faster than automation.

One tip to working with Aspose Cells is not to simply jump into your project and code away. Instead, take time to learn working with the library, which is best done prior to purchasing the library, and when you cannot figure something out ask in their forums. This is great being able to work with this library to ensure it does what you want. This is also why having done project requirements and design prior to coding so there are no surprises.
May 25th, 2012, 04:25 PM
Ashaelon

Re: Need some new eyes for refactoring....

Just as a note on this, something I have run into with performance with VBA and Excel. If you are doing any type of cell decorating (ie changing text alignment, formats, colors, etc) you will see a major performance drop as Excel needs to make each change and repaint after each change. Something to think about is to set the Excel Application's ScreenUpdating property to false when making these types of changes. This will increase the performance quite a bit.

I had this same problem in an Excel tool I built. When I changed the text alignment in the cells, the code seemed to bog down, but when I set screenupdating to false, it seemed to fly right through it.

Just a thought.
May 29th, 2012, 11:46 AM
Zmcpherson

Re: Need some new eyes for refactoring....

I just tried using the ScreenUpdating property, I didnt see any increase in the over all speed of the code. That would of been awesome though, thanks for the thought.
May 29th, 2012, 12:03 PM
kareninstructor

Re: Need some new eyes for refactoring....

Quote:

Originally Posted by Zmcpherson

I just tried using the ScreenUpdating property, I didnt see any increase in the over all speed of the code. That would of been awesome though, thanks for the thought.

I have not used ScreenUpdating in .NET automation but did with VB6 with Excel and did seem to make a difference. You might take a look at this thread where several post suggest using Application.calculation=xlManual before doing your code then application.Calculation=xlAutomatic when done (check out the next to last reply).

Re: Need some new eyes for refactoring....

Yeah, from everything Im reading on both .calculation and .ScreenUpdating it should make the code run faster. But my times are almost identical with both of those things on and and with both of those things off.

I did some more research and found this:
vb Code:
Dim bEvents As Boolean
Dim bAlerts As Boolean
Dim CalcMode As Long
Dim bScreen As Boolean
 
' save current settings
bEvents = Application.EnableEvents
bAlerts = Application.DisplayAlerts
CalcMode = Application.Calculation
bScreen = Application.ScreenUpdating
 
' disable events, alerts, automatic calculation &amp; screen updating
With Application
    .EnableEvents = False
    .DisplayAlerts = False
    .Calculation = xlCalculationManual
    .ScreenUpdating = False
End With
 
' your code here
 
Then right before the End Sub, paste this:
    
' restore previous settings
With Application
    .EnableEvents = bEvents
    .DisplayAlerts = bAlerts
    .Calculation = CalcMode
    .ScreenUpdating = bScreen
End With
But I am still right around 45 seconds with no improvements. I guess that's just how long it takes to create headers, footers, and change page orientation.