none
Unexplained performance issue RRS feed

  • Question

  • I have created a Visual Basic 2013 application. The application writes cell data to Excel, formats the cells in Excel and displays the finished spreadsheet. 

    While running the same dataset (input parameters) some machines produce the spreadsheet in 15 seconds, other take 85 seconds. There are no times in between. The spreadsheet is 1200 rows by 15 columns.

    The 15 second machines include:

    1. I5 laptop with 8 gb ram Win 7 64 pro 3 years old Excel 2010

    2. I5 desktop with gb ram Wind 7 64 pro 4 years old Excel 2010

    The 85 second computers include:

    1. I5 laptop with 16 gb ram Win 10 Pro 1 month old, Excel 2013

    2. VirtualMachine with 4 processors, 16 gb ram Win 7 64 bit Excel 2010

    I need to eliminate the speed differences. But I have no idea where to start looking. The code is deployed by Click Once so I think it should be identical on all the machines. I can say with certainty that they are all on the same version of the software as I display that on the start screen.

    Where do I begin trouble shooting this issue?

    Thanks,

    Pat

    Thursday, April 12, 2018 4:09 PM

All replies

  • Hi mpdillon99,

    You had mentioned that,"While running the same dataset (input parameters) some machines produce the spreadsheet in 15 seconds, other take 85 seconds."

    It is possible that the machines that takes 15 seconds is using the 64 bit of Excel and machine that takes 85 seconds is using 32 bit Excel.

    A 32-bit system can process the data in 32-bit pieces whereas 64-bit can process double that.  Because more data is being processed at once, the system will operate more quickly, and will use the physical memory more efficiently.  Installing the 64-bit version of Excel will certainly make your Excel models run faster and more efficiently.

    You can try to post your sample code with dummy data.

    We will try to make a test with your sample code to check the execution time.

    We can check whether we can simplify the code to decrease the overall execution time.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, April 13, 2018 3:10 AM
    Moderator
  • Deepak,

    Thank you for your response. I did not see it earlier. I am only at this client on Thursday. 

    All installations of excel are 32 bit.

    I will try to dummy up some data and strip down the program. I am not sure I have time to complete that today.

    thanks,

    pat

    Thursday, April 19, 2018 6:40 PM
  • Deepak,

    How do I attach a zip file? Looks like I Can't attach a zip. The code block will be large. Sorry.

    I ran the attached project on an older laptop. It took 32 seconds. The old laptop was windows 7 64 bit 8 GB memory and Excel 2010 32 bit.

    I ran it a second time on a Virtual machine (host system has 32 processors and 128 GB ram) which was configured as Windows 7 64 bit, 8 GB memory and Excel 2010 32 bit. The report took 103 seconds.

    Any insights you could offer would be appreciated.

    Thanks,

    pat

    Module modCondensed
        Public Sub BeginRpt(ByVal UserBeginDtDate As Date, ByVal UserEndDtDate As Date, ByVal TelerikBoolean As Boolean, ByRef frm As Form1)
            Dim objExcel As New Microsoft.Office.Interop.Excel.Application  'Object 'New Microsoft.Office.Interop.Excel
            Dim WorkbookNameString As String = String.Empty
            Dim r As Long = 0
            Dim i As Integer = 0
            Dim WorksheetNameString As String = String.Empty
            Dim TotalBookIndexInteger As Integer = 0
            Dim BeginDtRptDate As Date = Now
            Dim BeginDtMthDate As Date = Now
            Dim EndDtRptDate As Date = Now
            Dim BeginDtRptLong As Long = 0
            Dim BeginDtMthLong As Long = 0
            Dim EndDtRptLong As Long = 0
            Dim LocString As String = String.Empty
            Dim BeginYtdDtLong As Long = 0
            Dim EndYtdDtLong As Long = 0
            Dim BeginMthLong As Long = 0
            Dim EndMthLong As Long = 0
            Dim BeginYtdLYDtLong As Long = 0
            Dim EndYtdDtLYLong As Long = 0
            Dim BeginMthLYLong As Long = 0
            Dim EndMthLYLong As Long = 0
            Dim BeginDtDate As Date = Now
            
    
            If Not TelerikBoolean Then
                objExcel.Workbooks.Add()
    
                TotalBookIndexInteger = objExcel.Application.Workbooks.Count
                objExcel.Application.Workbooks(TotalBookIndexInteger).Activate()
                'Remove Worksheets 1 & 2
                For i = 1 To objExcel.Worksheets.Count - 1
                    objExcel.Application.DisplayAlerts = False
                    objExcel.Worksheets(objExcel.Worksheets.Count).Delete()
                Next i
    
                objExcel.Application.DisplayAlerts = True
            Else
    
    
            End If
            PublicVariables.CompanyLetterStringP = "A"
            Select Case PublicVariables.CompanyLetterStringP
                Case "B"
                    'PublicVariables.CompanyLetterStringP = "A"
                    'PublicVariables.CompanyDBStringP = "Amatex"
                    '
                    WorksheetNameString = "Amatex"
                    If Not TelerikBoolean Then
                        objExcel.ActiveWorkbook.Sheets(objExcel.Worksheets.Count).Name = WorksheetNameString
                        '
                        ExcelHeader(r, WorksheetNameString, objExcel, EndDtRptDate)
                        'SetHeaderView(r, objExcel, WorksheetNameString, 2, r, ProjectPathStringM)
                    Else
                        'worksheet.Name = WorksheetNameString
                        'TelerikHeader(r, WorksheetNameString, worksheet, EndDtRptDate)
                    End If
    
                    '
                    ProcessCompany(r, WorksheetNameString, objExcel, BeginDtRptLong, BeginDtMthLong, EndDtRptLong, BeginYtdDtLong, EndYtdDtLong, BeginMthLong,
                                   EndMthLong, BeginYtdLYDtLong, EndYtdDtLYLong, BeginMthLYLong, EndMthLYLong, 0, TelerikBoolean, frm)
                    '
                    'Norfab
                    PublicVariables.CompanyLetterStringP = "N"
                    PublicVariables.CompanyDBStringP = "Norfab"
                    '
                    'Dim worksheet1 As Worksheet = workbook.Worksheets.Add
                    'WorksheetNameString = "Norfab"
                    If Not TelerikBoolean Then
                        objExcel.Worksheets.Add(After:=objExcel.Worksheets(objExcel.Worksheets.Count))
                        objExcel.ActiveWorkbook.Sheets(objExcel.Worksheets.Count).Activate()
                        objExcel.ActiveWorkbook.Sheets(objExcel.Worksheets.Count).Name = WorksheetNameString
                        '
    
                        objExcel.ActiveWorkbook.Sheets(objExcel.Worksheets.Count).Name = WorksheetNameString
                        '
                        ExcelHeader(r, WorksheetNameString, objExcel, EndDtRptDate)
                        'SetHeaderView(r, objExcel, WorksheetNameString, 2, r, ProjectPathStringM)
                    Else
    
                        'worksheet1.Name = WorksheetNameString
                        'TelerikHeader(r, WorksheetNameString, worksheet1, EndDtRptDate)
                    End If
    
                    '
                    ProcessCompany(r, WorksheetNameString, objExcel, BeginDtRptLong, BeginDtMthLong, EndDtRptLong, BeginYtdDtLong, EndYtdDtLong, BeginMthLong,
                                   EndMthLong, BeginYtdLYDtLong, EndYtdDtLYLong, BeginMthLYLong, EndMthLYLong, 0, TelerikBoolean, frm)
                    '
                    '
                Case "A"
                    PublicVariables.CompanyLetterStringP = "A"
                    PublicVariables.CompanyDBStringP = "Amatex"
                    '
                    WorksheetNameString = "Amatex"
                    objExcel.ActiveWorkbook.Sheets(objExcel.Worksheets.Count).Name = WorksheetNameString
                    '
                    ExcelHeader(r, WorksheetNameString, objExcel, EndDtRptDate)
                    'SetHeaderView(r, objExcel, WorksheetNameString, 2, r, ProjectPathStringM)
                    '
                    ProcessCompany(r, WorksheetNameString, objExcel, BeginDtRptLong, BeginDtMthLong, EndDtRptLong, BeginYtdDtLong, EndYtdDtLong, BeginMthLong, EndMthLong, BeginYtdLYDtLong,
                                   EndYtdDtLYLong, BeginMthLYLong, EndMthLYLong, 0, TelerikBoolean, frm)
                Case "N"
                    PublicVariables.CompanyLetterStringP = "N"
                    PublicVariables.CompanyDBStringP = "Norfab"
                    '
                    WorksheetNameString = "Norfab"
                    objExcel.ActiveWorkbook.Sheets(objExcel.Worksheets.Count).Name = WorksheetNameString
                    '
                    ExcelHeader(r, WorksheetNameString, objExcel, EndDtRptDate)
                    'SetHeaderView(r, objExcel, WorksheetNameString, 2, r, ProjectPathStringM)
                    '
                    ProcessCompany(r, WorksheetNameString, objExcel, BeginDtRptLong, BeginDtMthLong, EndDtRptLong, BeginYtdDtLong, EndYtdDtLong, BeginMthLong,
                                   EndMthLong, BeginYtdLYDtLong, EndYtdDtLYLong, BeginMthLYLong, EndMthLYLong, 0, TelerikBoolean, frm)
            End Select
            '
            If Not TelerikBoolean Then
                objExcel.Worksheets(1).activate()
                objExcel.Worksheets(1).range("A8").select()
                'objExcel.ActiveWindow.SmallScroll(Down:=7)
                objExcel.Visible = True
            Else
            
            End If
    
            
            With frm
                .lblRow.Text = "Elapsed time: " & CStr(Microsoft.VisualBasic.DateDiff(DateInterval.Second, BeginDtDate, Now)) & " seconds."
            End With
            objExcel.Visible = True
        End Sub
        Private Sub ExcelHeader(ByRef r As Long, ByVal WorksheetNameString As String, ByRef objExcel As Object, ByVal RptDate As Date)
            Dim BeginCellAddressString As String = String.Empty
            Dim EndCellAddressString As String = String.Empty
            'Try
            r = 1
            Select Case PublicVariables.CompanyLetterStringP
                Case "A"
                    objExcel.Worksheets(WorksheetNameString).Cells(r, 1).Value = "VS Sales by Style by Product Category - Amatex"
                Case "N"
                    objExcel.Worksheets(WorksheetNameString).Cells(r, 1).Value = "VS Sales by Style by Product Category - Norfab"
                Case "B"
    
            End Select
    
            r += 1
            objExcel.Worksheets(WorksheetNameString).Cells(r, 1).Value = "Ending date: " & Format(RptDate, "MM/dd/yyyy")
            r += 1
            objExcel.Worksheets(WorksheetNameString).Cells(r, 1).Value = "Calendar Year"
            r += 1
            objExcel.Worksheets(WorksheetNameString).Cells(r, 1).Value = "Report date: " & Format(Now, "MM/dd/yyyy HH:mm:ss")
            r += 2
            '
            objExcel.Worksheets(WorksheetNameString).Cells(r, 5).Value = "Month to Date"
            objExcel.Worksheets(WorksheetNameString).Cells(r, 5).font.size = 14
            objExcel.Worksheets(WorksheetNameString).Cells(r, 5).Font.bold = True
            BeginCellAddressString = objExcel.Worksheets(WorksheetNameString).Cells(r, 4).address(False, False)
            EndCellAddressString = objExcel.Worksheets(WorksheetNameString).Cells(r + 1, 8).address(False, False)
            objExcel.Worksheets(WorksheetNameString).range(BeginCellAddressString, EndCellAddressString).select()
            With objExcel.application.selection.interior
                .Pattern = 1
                .PatternColorIndex = -4105
                .ThemeColor = 1
                .TintAndShade = -0.149998474074526
                .PatternTintAndShade = 0
            End With
    
            objExcel.Worksheets(WorksheetNameString).Cells(r, 11).Value = "Year to Date"
            objExcel.Worksheets(WorksheetNameString).Cells(r, 11).font.size = 14
            objExcel.Worksheets(WorksheetNameString).Cells(r, 11).Font.bold = True
            BeginCellAddressString = objExcel.Worksheets(WorksheetNameString).Cells(r, 10).address(False, False)
            EndCellAddressString = objExcel.Worksheets(WorksheetNameString).Cells(r + 1, 15).address(False, False)
            objExcel.Worksheets(WorksheetNameString).range(BeginCellAddressString, EndCellAddressString).select()
            With objExcel.application.selection.interior
                .Pattern = 1
                .PatternColorIndex = -4105
                .ThemeColor = 5
                .TintAndShade = 0.599993896298105
                .PatternTintAndShade = 0
            End With
            'objExcel.Worksheets(WorksheetNameString).range("A8").select()
            objExcel.Worksheets(WorksheetNameString).Columns(7).Font.Color = 255
            objExcel.Worksheets(WorksheetNameString).Columns(13).Font.Color = 255
            objExcel.Worksheets(WorksheetNameString).Columns(16).Font.Color = 255
            '
            r += 1
            objExcel.Worksheets(WorksheetNameString).Cells(r, 1).Value = "Prod Cat"
            objExcel.Worksheets(WorksheetNameString).Cells(r, 2).Value = "Item No"
            objExcel.Worksheets(WorksheetNameString).Cells(r, 3).Value = " Description"
            objExcel.Worksheets(WorksheetNameString).Cells(r, 4).Value = "Qty"
            objExcel.Worksheets(WorksheetNameString).Cells(r, 5).Value = "Last Year Qty"
            objExcel.Worksheets(WorksheetNameString).Cells(r, 6).Value = "Sales"
            If Not PublicVariables.ProdCatSummaryBooleanP Then
                objExcel.Worksheets(WorksheetNameString).Cells(r, 7).Value = "Avg Price"
                objExcel.Worksheets(WorksheetNameString).Cells(r, 7).Font.color = 0
            End If
    
            objExcel.Worksheets(WorksheetNameString).Cells(r, 8).Value = "Last Year Sales"
            objExcel.Worksheets(WorksheetNameString).Cells(r, 10).Value = "Qty"
            objExcel.Worksheets(WorksheetNameString).Cells(r, 11).Value = "Last Year Qty"
            objExcel.Worksheets(WorksheetNameString).Cells(r, 12).Value = "Sales"
            If Not PublicVariables.ProdCatSummaryBooleanP Then
                objExcel.Worksheets(WorksheetNameString).Cells(r, 13).Value = "Avg Price"
                objExcel.Worksheets(WorksheetNameString).Cells(r, 13).font.color = 0
            End If
    
            objExcel.Worksheets(WorksheetNameString).Cells(r, 14).Value = "Last Year Sales"
            objExcel.Worksheets(WorksheetNameString).Cells(r, 15).Value = "Pct Sales Change"
            '
            'objExcel.Worksheets(WorksheetNameString).Cells(r, 16).Value = "Unit Cost"
            'objExcel.Worksheets(WorksheetNameString).Cells(r, 17).Value = "Ext Cost"
            'objExcel.Worksheets(WorksheetNameString).Cells(r, 18).Value = "Gross Margin"
            'objExcel.Worksheets(WorksheetNameString).Cells(r, 19).Value = "GM %"
    
            objExcel.Worksheets(WorksheetNameString).Columns(1).columnwidth = 9
            objExcel.Worksheets(WorksheetNameString).Columns(2).columnwidth = 15
            objExcel.Worksheets(WorksheetNameString).Columns(3).columnwidth = 30
            objExcel.Worksheets(WorksheetNameString).Columns(4).columnwidth = 14
            objExcel.Worksheets(WorksheetNameString).Columns(5).columnwidth = 14
            objExcel.Worksheets(WorksheetNameString).Columns(6).columnwidth = 14
            objExcel.Worksheets(WorksheetNameString).Columns(7).columnwidth = 14
            objExcel.Worksheets(WorksheetNameString).Columns(8).columnwidth = 14
            objExcel.Worksheets(WorksheetNameString).Columns(9).columnwidth = 3
            objExcel.Worksheets(WorksheetNameString).Columns(10).columnwidth = 14
            objExcel.Worksheets(WorksheetNameString).Columns(11).columnwidth = 14
            objExcel.Worksheets(WorksheetNameString).Columns(12).columnwidth = 14
            objExcel.Worksheets(WorksheetNameString).Columns(13).columnwidth = 14
            objExcel.Worksheets(WorksheetNameString).Columns(14).columnwidth = 14
            objExcel.Worksheets(WorksheetNameString).Columns(15).columnwidth = 14
            objExcel.Worksheets(WorksheetNameString).Columns(16).columnwidth = 14
            objExcel.Worksheets(WorksheetNameString).Columns(17).columnwidth = 14
            objExcel.Worksheets(WorksheetNameString).Columns(18).columnwidth = 14
            objExcel.Worksheets(WorksheetNameString).Columns(19).columnwidth = 14
    
            '
            objExcel.Worksheets(WorksheetNameString).Columns(1).HorizontalAlignment = -4131
            objExcel.Worksheets(WorksheetNameString).Columns(2).HorizontalAlignment = -4131
            objExcel.Worksheets(WorksheetNameString).Columns(3).HorizontalAlignment = -4131
            objExcel.Worksheets(WorksheetNameString).Columns(4).HorizontalAlignment = -4152
            objExcel.Worksheets(WorksheetNameString).Columns(5).HorizontalAlignment = -4152
            objExcel.Worksheets(WorksheetNameString).Columns(6).HorizontalAlignment = -4152
            objExcel.Worksheets(WorksheetNameString).Columns(7).HorizontalAlignment = -4152
            objExcel.Worksheets(WorksheetNameString).Columns(9).HorizontalAlignment = -4152
            objExcel.Worksheets(WorksheetNameString).Columns(10).HorizontalAlignment = -4152
            objExcel.Worksheets(WorksheetNameString).Columns(11).HorizontalAlignment = -4152
            objExcel.Worksheets(WorksheetNameString).Columns(12).HorizontalAlignment = -4152
            objExcel.Worksheets(WorksheetNameString).Columns(12).HorizontalAlignment = -4108
            '
            objExcel.Worksheets(WorksheetNameString).Columns(16).HorizontalAlignment = -4152
            objExcel.Worksheets(WorksheetNameString).Columns(17).HorizontalAlignment = -4152
            objExcel.Worksheets(WorksheetNameString).Columns(18).HorizontalAlignment = -4152
            objExcel.Worksheets(WorksheetNameString).Columns(19).HorizontalAlignment = -4108
            '
            objExcel.Worksheets(WorksheetNameString).Columns(1).numberformat = "@"
            objExcel.Worksheets(WorksheetNameString).Columns(2).numberformat = "@"
            objExcel.Worksheets(WorksheetNameString).Columns(3).numberformat = "@"
            objExcel.Worksheets(WorksheetNameString).Columns(4).numberformat = "#,##0"
            objExcel.Worksheets(WorksheetNameString).Columns(5).numberformat = "#,##0"
            objExcel.Worksheets(WorksheetNameString).Columns(6).numberformat = "$ #,##0"
            objExcel.Worksheets(WorksheetNameString).Columns(7).numberformat = "$ #,##0"
            objExcel.Worksheets(WorksheetNameString).Columns(8).numberformat = "$ #,##0"
            '
            objExcel.Worksheets(WorksheetNameString).Columns(9).numberformat = "#,##0"
            objExcel.Worksheets(WorksheetNameString).Columns(10).numberformat = "#,##0"
            objExcel.Worksheets(WorksheetNameString).Columns(11).numberformat = "#,##0"
            objExcel.Worksheets(WorksheetNameString).Columns(12).numberformat = "$ #,##0"
            objExcel.Worksheets(WorksheetNameString).Columns(13).numberformat = "$ #,##0"
            objExcel.Worksheets(WorksheetNameString).Columns(14).numberformat = "$ #,##0"
            objExcel.Worksheets(WorksheetNameString).Columns(15).numberformat = "##0.00 %"
            '
            objExcel.Worksheets(WorksheetNameString).Columns(16).numberformat = "$ #,##0"
            objExcel.Worksheets(WorksheetNameString).Columns(17).numberformat = "$ #,##0"
            objExcel.Worksheets(WorksheetNameString).Columns(18).numberformat = "$ #,##0"
            objExcel.Worksheets(WorksheetNameString).Columns(19).numberformat = "##0.00 %"
    
            objExcel.Worksheets(WorksheetNameString).Cells(r, 1).Font.bold = True
            objExcel.Worksheets(WorksheetNameString).Cells(r, 2).Font.bold = True
            objExcel.Worksheets(WorksheetNameString).Cells(r, 3).Font.bold = True
            objExcel.Worksheets(WorksheetNameString).Cells(r, 4).Font.bold = True
            objExcel.Worksheets(WorksheetNameString).Cells(r, 5).Font.bold = True
            objExcel.Worksheets(WorksheetNameString).Cells(r, 6).Font.bold = True
            objExcel.Worksheets(WorksheetNameString).Cells(r, 7).Font.bold = True
            objExcel.Worksheets(WorksheetNameString).Cells(r, 8).Font.bold = True
            objExcel.Worksheets(WorksheetNameString).Cells(r, 10).Font.bold = True
            objExcel.Worksheets(WorksheetNameString).Cells(r, 11).Font.bold = True
            objExcel.Worksheets(WorksheetNameString).Cells(r, 12).Font.bold = True
            objExcel.Worksheets(WorksheetNameString).Cells(r, 13).Font.bold = True
            objExcel.Worksheets(WorksheetNameString).Cells(r, 14).Font.bold = True
            objExcel.Worksheets(WorksheetNameString).Cells(r, 15).Font.bold = True
            objExcel.Worksheets(WorksheetNameString).Cells(r, 16).Font.bold = True
            objExcel.Worksheets(WorksheetNameString).Cells(r, 17).Font.bold = True
            objExcel.Worksheets(WorksheetNameString).Cells(r, 18).Font.bold = True
            objExcel.Worksheets(WorksheetNameString).Cells(r, 19).Font.bold = True
            '
    
            'SetPrinterProperties(objExcel, WorksheetNameString, r, r, True)
            '    r += 1
            'Catch ex As Exception
            '    Dim z As String = GetExceptionInfo(ex)
            '    'z = z & vbCrLf & vbCrLf & ParmString & vbCrLf & vbCrLf & DeclareString
            '    Using frmZ As New FrmError(z)
            '        frmZ.ShowDialog()
            '    End Using
            'End Try
        End Sub
        Private Sub ProcessCompany(ByRef r As Long, ByVal WorksheetNameString As String, ByRef objExcel As Object, ByRef BeginDtRptLong As Long, ByRef BeginDtMthLong As Long, ByRef EndDtRptLong As Long, ByVal BeginYtdDtLong As Long, _
                                   ByVal EndYtdDtLong As Long, ByVal BeginMthLong As Long, ByVal EndMthLong As Long, ByVal BeginYtdLYDtLong As Long,
                                   ByVal EndYtdDtLYLong As Long, ByVal BeginMthLYLong As Long, ByVal EndMthLYLong As Long, ByVal FYInteger As Integer,
                                   ByVal TelerikBoolean As Boolean, ByRef frm As Form1)
    
            Dim MacConn As New System.Data.SqlClient.SqlConnection
            Dim MacCmd As New System.Data.SqlClient.SqlCommand
            Dim MacReader As System.Data.SqlClient.SqlDataReader = Nothing
            Dim ParmString As String = String.Empty
            Dim NoOfRecordsUpdatedInteger As Integer = 0
            Dim MacConnString As String = String.Empty
            Dim ProdCatString As String = String.Empty
            Dim ProdCatFgString As String = String.Empty
            Dim ItemNoString As String = String.Empty
            Dim DescString As String = String.Empty
            Dim QOHDecimal As Decimal = 0
            Dim QtyAllocatedDecimal As Decimal = 0
            Dim SlsSixMthDecimal As Decimal = 0
            Dim SlsTwelveMthDecimal As Decimal = 0
            Dim SlsSixMthProdCatDecimal As Decimal = 0
            Dim SlsTwelveMthProdCatDecimal As Decimal = 0
            Dim SlsSixMthLocDecimal As Decimal = 0
            Dim SlsTwelveMthLocDecimal As Decimal = 0
            Dim SlsSixMthGttlDecimal As Decimal = 0
            Dim SlsTwelveMthGttlDecimal As Decimal = 0
            '
            Dim LYSlsSixMthDecimal As Decimal = 0
            Dim LySlsTwelveMthDecimal As Decimal = 0
            Dim LYSlsSixMthProdCatDecimal As Decimal = 0
            Dim LYSlsTwelveMthProdCatDecimal As Decimal = 0
            Dim LySlsSixMthLocDecimal As Decimal = 0
            Dim LySlsTwelveMthLocDecimal As Decimal = 0
            Dim LySlsSixMthGttlDecimal As Decimal = 0
            Dim LySlsTwelveMthGttlDecimal As Decimal = 0
            '
            Dim QtySixMthDecimal As Decimal = 0
            Dim QtyTwelveMthDecimal As Decimal = 0
            Dim QtySixMthProdCatDecimal As Decimal = 0
            Dim QtyTwelveMthProdCatDecimal As Decimal = 0
            Dim QtySixMthLocDecimal As Decimal = 0
            Dim QtyTwelveMthLocDecimal As Decimal = 0
            Dim QtySixMthGttlDecimal As Decimal = 0
            Dim QtyTwelveMthGttlDecimal As Decimal = 0
            '
            Dim LYQtySixMthDecimal As Decimal = 0
            Dim LyQtyTwelveMthDecimal As Decimal = 0
            Dim LYQtySixMthProdCatDecimal As Decimal = 0
            Dim LYQtyTwelveMthProdCatDecimal As Decimal = 0
            Dim LYQtySixMthLocDecimal As Decimal = 0
            Dim LYQtyTwelveMthLocDecimal As Decimal = 0
            Dim LyQtySixMthGttlDecimal As Decimal = 0
            Dim LyQtyTwelveMthGttlDecimal As Decimal = 0
            '
            Dim FirstRecordBoolean As Boolean = True
            Dim ProdCatNameString As String = String.Empty
            Dim ProdCatNameFgString As String = String.Empty
            Dim BeginLocString As String = String.Empty
            Dim EndLocString As String = String.Empty
            Dim LocString As String = String.Empty
            Dim LocFgString As String = String.Empty
            Dim LocSQLString As String = String.Empty
            '
            Dim UnitCostDecimal As Decimal = 0
    
            Dim ExtCostDecimal As Decimal = 0
            Dim ExtCostSalesDecimal As Decimal = 0
            Dim ExtGrossMarginDecimal As Decimal = 0
            '
            Dim ExtCostPCDecimal As Decimal = 0
            Dim ExtCostSalesPCDecimal As Decimal = 0
            Dim ExtGrossMarginPCDecimal As Decimal = 0
            '
            Dim ExtCostLocDecimal As Decimal = 0
            Dim ExtCostSalesLocDecimal As Decimal = 0
            Dim ExtGrossMarginLocDecimal As Decimal = 0
            '
            Dim ExtCostCoDecimal As Decimal = 0
            Dim ExtCostSalesCoDecimal As Decimal = 0
            Dim ExtGrossMarginCoDecimal As Decimal = 0
    
            '
            Dim ExtCostTtlDecimal As Decimal = 0
            Dim ExtCostSalesTtlDecimal As Decimal
            Dim ExtGrossMarginTtlDecimal As Decimal = 0
            Dim DeclareString As String = String.Empty
            Dim c As Integer = 0
            
            '65  to 122
            Dim l As Integer = 65
            Dim P As Integer = 65
            Dim Itm As Integer = 64
            DescString = "Description string"
            LocString = Chr(l)
            ProdCatString = Chr(P)
            ItemNoString = Chr(Itm)
    
            'MacReader = MacCmd.ExecuteReader
            For r = 8 To 300
                With frm
                    .lblRow.Text = "Row: " & CStr(r)
                    .Refresh()
                    Application.DoEvents()
                End With
                Itm += 1
                ItemNoString = GetNextItemno(ItemNoString, Itm)
                If r Mod 100 Then
                    LocString = GetNewLoc(LocString, l)
                End If
                If r Mod 20 Then
                    ProdCatString = GetNewProdCat(ProdCatString, P)
                End If
                QtyTwelveMthDecimal = r
                QtySixMthDecimal = r + 1
                SlsTwelveMthDecimal = r + 2
                SlsSixMthDecimal = r + 3
                LyQtyTwelveMthDecimal = r + 4
                LYQtySixMthDecimal = r + 5
                LySlsTwelveMthDecimal = r + 6
                LYSlsSixMthDecimal = r + 7
    
                If FirstRecordBoolean Then
                    FirstRecordBoolean = False
                    ProdCatFgString = ProdCatString
                    ProdCatNameFgString = ProdCatNameString
                    ExtCostPCDecimal = 0
                    ExtCostSalesPCDecimal = 0
                    'With FrmStatus
                    '    .lblStatus.Text = "Processing Location/Product Category - " & LocFgString & "/" & ProdCatFgString & "     " & CStr(r)
                    '    .Text = PublicVariables.CompanyDBStringP
                    '    .Refresh()
                    '    Application.DoEvents()
                    'End With
                    LocHeader(r, objExcel, WorksheetNameString, LocFgString, LocString, ExtCostLocDecimal, ExtCostSalesLocDecimal, TelerikBoolean)
                Else
                    If LocFgString <> LocString Then
                        ProdCatFooter(r, WorksheetNameString, objExcel, ProdCatFgString, ProdCatNameFgString, QtySixMthProdCatDecimal, QtyTwelveMthProdCatDecimal, _
                                          SlsSixMthProdCatDecimal, SlsTwelveMthProdCatDecimal, LocFgString, LYQtySixMthProdCatDecimal, LYQtyTwelveMthProdCatDecimal,
                                          LYSlsSixMthProdCatDecimal, LYSlsTwelveMthProdCatDecimal, ExtCostPCDecimal, ExtCostSalesPCDecimal, TelerikBoolean)
                        LocFooter(r, objExcel, WorksheetNameString, BeginLocString, EndLocString, BeginYtdDtLong, EndYtdDtLong, BeginMthLong, EndMthLong, BeginYtdLYDtLong, EndYtdDtLYLong,
                                  BeginMthLYLong, EndMthLYLong, LocFgString, ExtCostLocDecimal, ExtCostSalesLocDecimal, TelerikBoolean)
                        LocHeader(r, objExcel, WorksheetNameString, LocFgString, LocString, ExtCostLocDecimal, ExtCostSalesLocDecimal, TelerikBoolean)
                        '
                        ProdCatFgString = ProdCatString
                        ProdCatNameFgString = ProdCatNameString
                        ExtCostPCDecimal = 0
                        ExtCostSalesPCDecimal = 0
                        'With FrmStatus
                        '    .lblStatus.Text = "Processing Location/Product Category - " & LocFgString & "/" & ProdCatFgString & "     " & CStr(r)
                        '    .Refresh()
                        '    Application.DoEvents()
                        'End With
                    Else
                        If ProdCatFgString <> ProdCatString Then
                            ProdCatFooter(r, WorksheetNameString, objExcel, ProdCatFgString, ProdCatNameFgString, QtySixMthProdCatDecimal, QtyTwelveMthProdCatDecimal, _
                                          SlsSixMthProdCatDecimal, SlsTwelveMthProdCatDecimal, LocFgString, LYQtySixMthProdCatDecimal, LYQtyTwelveMthProdCatDecimal,
                                          LYSlsSixMthProdCatDecimal, LYSlsTwelveMthProdCatDecimal, ExtCostPCDecimal, ExtCostSalesPCDecimal, TelerikBoolean)
                            ProdCatFgString = ProdCatString
                            ProdCatNameFgString = ProdCatNameString
                            ExtCostPCDecimal = 0
                            ExtCostSalesPCDecimal = 0
                            'With FrmStatus
                            '    .lblStatus.Text = "Processing Location/Product Category - " & LocFgString & "/" & ProdCatFgString & "     " & CStr(r)
                            '    .Refresh()
                            '    Application.DoEvents()
                            'End With
                        End If
                    End If
    
                End If
                '
    
                '
                If Not PublicVariables.ProdCatSummaryBooleanP Then
    
                    If System.Math.Abs(QtySixMthDecimal) + System.Math.Abs(QtyTwelveMthDecimal) + System.Math.Abs(LYQtySixMthDecimal) + System.Math.Abs(LyQtyTwelveMthDecimal) <= 0 Then
                        'Do nothing
                        'This eliminates 0 rows
                    Else
                        If Not TelerikBoolean Then
                            objExcel.Worksheets(WorksheetNameString).Cells(r, 1).Value = ProdCatString
                            objExcel.Worksheets(WorksheetNameString).Cells(r, 2).Value = ItemNoString
                            objExcel.Worksheets(WorksheetNameString).Cells(r, 3).Value = DescString
                            objExcel.Worksheets(WorksheetNameString).Cells(r, 4).Value = QtySixMthDecimal
                            objExcel.Worksheets(WorksheetNameString).Cells(r, 6).Value = SlsSixMthDecimal
                            If QtySixMthDecimal <> 0 Then
                                objExcel.Worksheets(WorksheetNameString).Cells(r, 7).Value = SlsSixMthDecimal / QtySixMthDecimal
                            Else
                                objExcel.Worksheets(WorksheetNameString).Cells(r, 7).Value = ""
                            End If
                            objExcel.Worksheets(WorksheetNameString).Cells(r, 10).Value = QtyTwelveMthDecimal
                            objExcel.Worksheets(WorksheetNameString).Cells(r, 12).Value = SlsTwelveMthDecimal
                            '
                            objExcel.Worksheets(WorksheetNameString).Cells(r, 5).Value = LYQtySixMthDecimal
                            objExcel.Worksheets(WorksheetNameString).Cells(r, 8).Value = LYSlsSixMthDecimal
                            objExcel.Worksheets(WorksheetNameString).Cells(r, 11).Value = LyQtyTwelveMthDecimal
                            If QtyTwelveMthDecimal <> 0 Then
                                objExcel.Worksheets(WorksheetNameString).Cells(r, 13).Value = SlsTwelveMthDecimal / QtyTwelveMthDecimal
                            Else
                                objExcel.Worksheets(WorksheetNameString).Cells(r, 13).Value = ""
                            End If
                            objExcel.Worksheets(WorksheetNameString).Cells(r, 14).Value = LySlsTwelveMthDecimal
                            If LySlsTwelveMthDecimal <> 0 Then
                                objExcel.Worksheets(WorksheetNameString).Cells(r, 15).Value = (SlsTwelveMthDecimal - LySlsTwelveMthDecimal) / LySlsTwelveMthDecimal
                            Else
                                objExcel.Worksheets(WorksheetNameString).Cells(r, 15).Value = "-"
                            End If
                            '
    
                        End If
    
                        '
    
                        'r += 1
                    End If
                Else
                    UnitCostDecimal = r * 0.1 'UnitCost(CoCmd, CoReader, ItemNoString, FYInteger, PublicVariables.CompanyLetterStringP, LocString)
                    ExtCostDecimal = QtyTwelveMthDecimal * UnitCostDecimal
                    ExtCostSalesDecimal = SlsTwelveMthDecimal
                    ExtGrossMarginDecimal = ExtCostSalesDecimal - ExtCostDecimal
                    If UnitCostDecimal <> 0 Then
    
                    End If
                    'total
                    '
                    'With the margins recorded this way, the GM may be understated becuase they are inflated by items without costs.
                    ExtCostLocDecimal = ExtCostLocDecimal + ExtCostDecimal
                    ExtCostSalesLocDecimal = ExtCostSalesLocDecimal + ExtCostSalesDecimal
                    '
                    ExtCostPCDecimal = ExtCostPCDecimal + ExtCostDecimal
                    ExtCostSalesPCDecimal = ExtCostSalesPCDecimal + ExtCostSalesDecimal
                    '
                    ExtCostCoDecimal = ExtCostCoDecimal + ExtCostDecimal
                    ExtCostSalesCoDecimal = ExtCostSalesCoDecimal + ExtCostSalesDecimal
                    '
                    ExtCostTtlDecimal = ExtCostTtlDecimal + ExtCostDecimal
                    ExtCostSalesTtlDecimal = ExtCostSalesTtlDecimal + ExtCostSalesDecimal
                End If
                '
                QtySixMthProdCatDecimal = QtySixMthProdCatDecimal + QtySixMthDecimal
                QtyTwelveMthProdCatDecimal = QtyTwelveMthProdCatDecimal + QtyTwelveMthDecimal
                SlsSixMthProdCatDecimal = SlsSixMthProdCatDecimal + SlsSixMthDecimal
                SlsTwelveMthProdCatDecimal = SlsTwelveMthProdCatDecimal + SlsTwelveMthDecimal
                '
                LYQtySixMthProdCatDecimal = LYQtySixMthProdCatDecimal + LYQtySixMthDecimal
                LYQtyTwelveMthProdCatDecimal = LYQtyTwelveMthProdCatDecimal + LyQtyTwelveMthDecimal
                LYSlsSixMthProdCatDecimal = LYSlsSixMthProdCatDecimal + LYSlsSixMthDecimal
                LYSlsTwelveMthProdCatDecimal = LYSlsTwelveMthProdCatDecimal + LySlsTwelveMthDecimal
    
                'QtySixMthLocDecimal = QtySixMthLocDecimal + QtySixMthDecimal
                'QtyTwelveMthLocDecimal = QtyTwelveMthLocDecimal + QtyTwelveMthDecimal
                'SlsSixMthLocDecimal = SlsSixMthLocDecimal + SlsSixMthDecimal
                'SlsTwelveMthLocDecimal = SlsTwelveMthLocDecimal + SlsTwelveMthDecimal
                '
                QtySixMthGttlDecimal = QtySixMthGttlDecimal + QtySixMthDecimal
                QtyTwelveMthGttlDecimal = QtyTwelveMthGttlDecimal + QtyTwelveMthDecimal
                SlsSixMthGttlDecimal = SlsSixMthGttlDecimal + SlsSixMthDecimal
                SlsTwelveMthGttlDecimal = SlsTwelveMthGttlDecimal + SlsTwelveMthDecimal
                '
                LyQtySixMthGttlDecimal = LyQtySixMthGttlDecimal + LYQtySixMthDecimal
                LyQtyTwelveMthGttlDecimal = LyQtyTwelveMthGttlDecimal + LyQtyTwelveMthDecimal
                LySlsSixMthGttlDecimal = LySlsSixMthGttlDecimal + LYSlsSixMthDecimal
                LySlsTwelveMthGttlDecimal = LySlsTwelveMthGttlDecimal + LySlsTwelveMthDecimal
    
            Next
            
            '
            If Not FirstRecordBoolean Then
                ProdCatFooter(r, WorksheetNameString, objExcel, ProdCatFgString, ProdCatNameFgString, QtySixMthProdCatDecimal, QtyTwelveMthProdCatDecimal, _
                                      SlsSixMthProdCatDecimal, SlsTwelveMthProdCatDecimal, LocFgString, LYQtySixMthProdCatDecimal, LYQtyTwelveMthProdCatDecimal, _
                                      LYSlsSixMthProdCatDecimal, LYSlsTwelveMthProdCatDecimal, ExtCostPCDecimal, ExtCostSalesPCDecimal, TelerikBoolean)
                r += 2
                LocFooter(r, objExcel, WorksheetNameString, BeginLocString, EndLocString, BeginYtdDtLong, EndYtdDtLong, BeginMthLong, EndMthLong, BeginYtdLYDtLong,
                          EndYtdDtLYLong, BeginMthLYLong, EndMthLYLong, LocFgString, ExtCostLocDecimal, ExtCostSalesLocDecimal, TelerikBoolean)
                r += 2
                If Not TelerikBoolean Then
                    objExcel.Worksheets(WorksheetNameString).Cells(r, 1).Value = "Grand Total: "
                    objExcel.Worksheets(WorksheetNameString).Cells(r, 4).Value = QtySixMthGttlDecimal
                    objExcel.Worksheets(WorksheetNameString).Cells(r, 6).Value = SlsSixMthGttlDecimal
                    'If Not PublicVariables.ProdCatSummaryBooleanP Then
                    '    If QtySixMthGttlDecimal <> 0 Then
                    '        objExcel.Worksheets(WorksheetNameString).Cells(r, 7).Value = SlsSixMthGttlDecimal / QtySixMthGttlDecimal
                    '    Else
    
                    '    End If
                    'End If
    
                    objExcel.Worksheets(WorksheetNameString).Cells(r, 10).Value = QtyTwelveMthGttlDecimal
                    objExcel.Worksheets(WorksheetNameString).Cells(r, 12).Value = SlsTwelveMthGttlDecimal
                    'If Not PublicVariables.ProdCatSummaryBooleanP Then
                    '    If QtyTwelveMthGttlDecimal <> 0 Then
                    '        objExcel.Worksheets(WorksheetNameString).Cells(r, 13).Value = SlsTwelveMthGttlDecimal / QtyTwelveMthGttlDecimal
                    '    Else
    
                    '    End If
                    'End If
    
                    '
                    objExcel.Worksheets(WorksheetNameString).Cells(r, 5).Value = LyQtySixMthGttlDecimal
                    objExcel.Worksheets(WorksheetNameString).Cells(r, 8).Value = LySlsSixMthGttlDecimal
                    objExcel.Worksheets(WorksheetNameString).Cells(r, 11).Value = LyQtyTwelveMthGttlDecimal
    
                    objExcel.Worksheets(WorksheetNameString).Cells(r, 14).Value = LySlsTwelveMthGttlDecimal
                    If LySlsTwelveMthGttlDecimal <> 0 Then
                        objExcel.Worksheets(WorksheetNameString).Cells(r, 15).Value = (SlsTwelveMthGttlDecimal - LySlsTwelveMthGttlDecimal) / LySlsTwelveMthGttlDecimal
                    Else
                        objExcel.Worksheets(WorksheetNameString).Cells(r, 15).Value = "-"
                    End If
                    '
                    '' ''objExcel.Worksheets(WorksheetNameString).Cells(r, 17).Value = ExtCostTtlDecimal
                    '' ''objExcel.Worksheets(WorksheetNameString).Cells(r, 18).Value = (ExtCostSalesTtlDecimal - ExtCostTtlDecimal)
                    '' ''If ExtCostSalesTtlDecimal <> 0 Then
                    '' ''    objExcel.Worksheets(WorksheetNameString).Cells(r, 19).Value = (ExtCostSalesTtlDecimal - ExtCostTtlDecimal) / ExtCostSalesTtlDecimal
                    '' ''End If
    
    
                    objExcel.Worksheets(WorksheetNameString).Cells(r, 1).font.bold = True
                    objExcel.Worksheets(WorksheetNameString).Cells(r, 4).font.bold = True
                    objExcel.Worksheets(WorksheetNameString).Cells(r, 5).font.bold = True
                    objExcel.Worksheets(WorksheetNameString).Cells(r, 6).font.bold = True
                    objExcel.Worksheets(WorksheetNameString).Cells(r, 8).font.bold = True
                    '
                    objExcel.Worksheets(WorksheetNameString).Cells(r, 10).font.bold = True
                    objExcel.Worksheets(WorksheetNameString).Cells(r, 11).font.bold = True
                    objExcel.Worksheets(WorksheetNameString).Cells(r, 12).font.bold = True
                    objExcel.Worksheets(WorksheetNameString).Cells(r, 14).font.bold = True
                    objExcel.Worksheets(WorksheetNameString).Cells(r, 15).font.bold = True
                    objExcel.Worksheets(WorksheetNameString).Cells(r, 17).font.bold = True
                    objExcel.Worksheets(WorksheetNameString).Cells(r, 18).font.bold = True
                    objExcel.Worksheets(WorksheetNameString).Cells(r, 19).font.bold = True
    
                Else
                    
                    '
                End If
    
    
            End If
            
            'Catch ex As Exception
            '    Dim z As String = GetExceptionInfo(ex)
            '    z = z & vbCrLf & vbCrLf & ParmString & vbCrLf & vbCrLf & DeclareString
            '    Using frmZ As New FrmError(z)
            '        frmZ.ShowDialog()
            '    End Using
            'End Try
        End Sub
        Private Function GetNextItemno(ByVal ItemNoString As String, ByRef Itm As Integer) As String
            Dim a As String = Mid(ItemNoString, Len(ItemNoString), 1)
            Dim b As String = String.Empty
            Itm += 1
            If Len(ItemNoString) > 1 Then
                b = Mid(ItemNoString, 1, Len(ItemNoString) - 1)
            End If
            If Itm > 122 Then
                GetNextItemno = ItemNoString + "A"
                Itm = 65
            Else
                GetNextItemno = b & Chr(Itm)
            End If
    
        End Function
        Private Function GetNewLoc(ByVal LocString As String, ByRef l As Integer) As String
            Dim a As String = Mid(LocString, Len(LocString), 1)
            Dim b As String = String.Empty
            l += 1
            If Len(LocString) > 1 Then
                b = Mid(LocString, 1, Len(LocString) - 1)
            End If
            If l > 122 Then
                GetNewLoc = LocString + "A"
                l = 65
            Else
                GetNewLoc = b & Chr(l)
            End If
        End Function
        Private Function GetNewProdCat(ByVal ProdCatString As String, ByRef P As Integer) As String
            Dim a As String = Mid(ProdCatString, Len(ProdCatString), 1)
            Dim b As String = String.Empty
            P += 1
            If Len(ProdCatString) > 1 Then
                b = Mid(ProdCatString, 1, Len(ProdCatString) - 1)
            End If
            If P > 122 Then
                GetNewProdCat = ProdCatString + "A"
                P = 65
            Else
                GetNewProdCat = b & Chr(P)
            End If
        End Function
        Private Sub LocHeader(ByRef r As Long, ByRef objExcel As Object, ByVal WorksheetNameString As String, ByRef LocFgString As String, ByVal LocString As String,
                                  ByRef ExtCostLocDecimal As Decimal, ByRef ExtCostSalesLocDecimal As Decimal, ByVal TelerikBoolean As Boolean)
            'Try
            If Not TelerikBoolean Then
                objExcel.Worksheets(WorksheetNameString).Cells(r, 1).Value = "Begin Location: " & LocString
                objExcel.Worksheets(WorksheetNameString).Cells(r, 1).font.bold = True
            Else
                'SS.SetCellBold(worksheet, r, 0, True)
                'SS.WriteCellValue(worksheet, r, 1 - 1, "Begin Location: " & LocString)
            End If
    
            LocFgString = LocString
            ExtCostLocDecimal = 0
            ExtCostSalesLocDecimal = 0
    
            'Catch ex As Exception
            '    Dim z As String = GetExceptionInfo(ex)
            '    'z = z & vbCrLf & vbCrLf & ParmString & vbCrLf & vbCrLf & DeclareString
            '    Using frmZ As New FrmError(z)
            '        frmZ.ShowDialog()
            '    End Using
            'End Try
        End Sub
        Private Sub ProdCatFooter(ByRef r As Long, ByVal WorksheetNameString As String, ByRef objExcel As Object, ByVal ProdCatFgString As String, ByVal ProdCatNameFgString As String, _
                                  ByRef QtySixMthProdCatDecimal As Decimal, ByRef QtyTwelveMthProdCatDecimal As Decimal, ByRef SlsSixMthProdCatDecimal As Decimal, _
                                  ByRef SlsTwelveMthProdCatDecimal As Decimal, ByVal LocFgString As String, ByRef LYQtySixMthProdCatDecimal As Decimal, ByRef LYQtyTwelveMthProdCatDecimal As Decimal, _
                                      ByRef LYSlsSixMthProdCatDecimal As Decimal, ByRef LYSlsTwelveMthProdCatDecimal As Decimal,
                                      ByVal ExtCostPCDecimal As Decimal, ByVal ExtCostSalesPCDecimal As Decimal, ByVal TelerikBoolean As Boolean)
            Dim BeginCellAddressString As String = String.Empty
            Dim EndCellAddressString As String = String.Empty
            'Try
            If Not TelerikBoolean Then
                objExcel.Worksheets(WorksheetNameString).Cells(r, 1).Value = "** Total: " & LocFgString & " - " & ProdCatFgString & "/" & ProdCatNameFgString
                objExcel.Worksheets(WorksheetNameString).Cells(r, 4).Value = QtySixMthProdCatDecimal
                objExcel.Worksheets(WorksheetNameString).Cells(r, 6).Value = SlsSixMthProdCatDecimal
                objExcel.Worksheets(WorksheetNameString).Cells(r, 10).Value = QtyTwelveMthProdCatDecimal
                objExcel.Worksheets(WorksheetNameString).Cells(r, 12).Value = SlsTwelveMthProdCatDecimal
                '
                objExcel.Worksheets(WorksheetNameString).Cells(r, 5).Value = LYQtySixMthProdCatDecimal
                objExcel.Worksheets(WorksheetNameString).Cells(r, 8).Value = LYSlsSixMthProdCatDecimal
                objExcel.Worksheets(WorksheetNameString).Cells(r, 11).Value = LYQtyTwelveMthProdCatDecimal
                objExcel.Worksheets(WorksheetNameString).Cells(r, 14).Value = LYSlsTwelveMthProdCatDecimal
                '
                If LYSlsTwelveMthProdCatDecimal <> 0 Then
                    objExcel.Worksheets(WorksheetNameString).Cells(r, 15).Value = (SlsTwelveMthProdCatDecimal - LYSlsTwelveMthProdCatDecimal) / LYSlsTwelveMthProdCatDecimal
                Else
                    objExcel.Worksheets(WorksheetNameString).Cells(r, 15).Value = "-"
                End If
            Else
    
            End If
    
            '
            If Not TelerikBoolean Then
                If PublicVariables.ProdCatSummaryBooleanP Then
    
                Else
                    BeginCellAddressString = objExcel.Worksheets(WorksheetNameString).Cells(r, 1).address(False, False)
                    EndCellAddressString = objExcel.Worksheets(WorksheetNameString).Cells(r, 15).address(False, False)
                    objExcel.Worksheets(WorksheetNameString).range(BeginCellAddressString, EndCellAddressString).select()
                    With objExcel.application.selection.interior
                        .Pattern = 1
                        .PatternColorIndex = -4105
                        .ThemeColor = 4
                        .TintAndShade = 0.799981688894314
                        .PatternTintAndShade = 0
                    End With
                End If
                '
                If PublicVariables.ProdCatSummaryBooleanP Then
    
                Else
                    objExcel.Worksheets(WorksheetNameString).Cells(r, 1).Font.bold = True
                    objExcel.Worksheets(WorksheetNameString).Cells(r, 4).Font.bold = True
                    objExcel.Worksheets(WorksheetNameString).Cells(r, 5).Font.bold = True
                    objExcel.Worksheets(WorksheetNameString).Cells(r, 6).Font.bold = True
                    objExcel.Worksheets(WorksheetNameString).Cells(r, 8).Font.bold = True
                    '
                    objExcel.Worksheets(WorksheetNameString).Cells(r, 10).Font.bold = True
                    objExcel.Worksheets(WorksheetNameString).Cells(r, 11).Font.bold = True
                    objExcel.Worksheets(WorksheetNameString).Cells(r, 12).Font.bold = True
                    objExcel.Worksheets(WorksheetNameString).Cells(r, 14).Font.bold = True
                    objExcel.Worksheets(WorksheetNameString).Cells(r, 15).Font.bold = True
                    objExcel.Worksheets(WorksheetNameString).Cells(r, 17).Font.bold = True
                    objExcel.Worksheets(WorksheetNameString).Cells(r, 18).Font.bold = True
                    objExcel.Worksheets(WorksheetNameString).Cells(r, 19).Font.bold = True
                End If
    
            End If
    
            'objExcel.Worksheets(WorksheetNameString).range("A8").select()
            '
    
            '
            QtySixMthProdCatDecimal = 0
            QtyTwelveMthProdCatDecimal = 0
            SlsSixMthProdCatDecimal = 0
            SlsTwelveMthProdCatDecimal = 0
            '
            LYQtySixMthProdCatDecimal = 0
            LYQtyTwelveMthProdCatDecimal = 0
            LYSlsSixMthProdCatDecimal = 0
            LYSlsTwelveMthProdCatDecimal = 0
            '
    
            'Catch ex As Exception
            '    Dim z As String = GetExceptionInfo(ex)
            '    'z = z & vbCrLf & vbCrLf & ParmString & vbCrLf & vbCrLf & DeclareString
            '    Using frmZ As New FrmError(z)
            '        frmZ.ShowDialog()
            '    End Using
            'End Try
    
        End Sub
        Private Sub LocFooter(ByRef r As Long, ByRef objExcel As Object, ByVal WorksheetNameString As String, ByVal BeginLocString As String, ByVal EndLocString As String, ByVal BeginYtdDtLong As Long, _
                              ByVal EndYtdDtLong As Long, ByVal BeginMthLong As Long, ByVal EndMthLong As Long, ByVal BeginYtdLYDtLong As Long, ByVal EndYtdDtLYLong As Long, ByVal BeginMthLYLong As Long, _
                              ByVal EndMthLYLong As Long, ByVal LocFgString As String, ByRef ExtCostLocDecimal As Decimal, ByRef ExtCostSalesLocDecimal As Decimal,
                              ByVal TelerikBoolean As Boolean)
    
            Dim MacConn As New System.Data.SqlClient.SqlConnection
            Dim MacCmd As New System.Data.SqlClient.SqlCommand
            Dim MacReader As System.Data.SqlClient.SqlDataReader = Nothing
            Dim ParmString As String = String.Empty
            Dim NoOfRecordsUpdatedInteger As Integer = 0
            Dim MacConnString As String = String.Empty
            Dim SlsSixMthDecimal As Decimal = 0
            Dim SlsTwelveMthDecimal As Decimal = 0
            Dim QtySixMthDecimal As Decimal = 0
            Dim QtyTwelveMthDecimal As Decimal = 0
            '
            Dim LYSlsSixMthDecimal As Decimal = 0
            Dim LYSlsTwelveMthDecimal As Decimal = 0
            Dim LYQtySixMthDecimal As Decimal = 0
            Dim LYQtyTwelveMthDecimal As Decimal = 0
            '
            Dim BeginCellAddressString As String = String.Empty
            Dim EndCellAddressString As String = String.Empty
            Dim DeclareString As String = String.Empty
            '
            '
            If Not TelerikBoolean Then
                objExcel.Worksheets(WorksheetNameString).Cells(r, 1).Value = "End of Location: - " & LocFgString
                objExcel.Worksheets(WorksheetNameString).Cells(r, 4).Value = QtySixMthDecimal
                objExcel.Worksheets(WorksheetNameString).Cells(r, 6).Value = SlsSixMthDecimal
                objExcel.Worksheets(WorksheetNameString).Cells(r, 10).Value = QtyTwelveMthDecimal
                objExcel.Worksheets(WorksheetNameString).Cells(r, 12).Value = SlsTwelveMthDecimal
                '
                objExcel.Worksheets(WorksheetNameString).Cells(r, 5).Value = LYQtySixMthDecimal
                objExcel.Worksheets(WorksheetNameString).Cells(r, 8).Value = LYSlsSixMthDecimal
                objExcel.Worksheets(WorksheetNameString).Cells(r, 11).Value = LYQtyTwelveMthDecimal
                objExcel.Worksheets(WorksheetNameString).Cells(r, 14).Value = LYSlsTwelveMthDecimal
                If LYSlsTwelveMthDecimal <> 0 Then
                    objExcel.Worksheets(WorksheetNameString).Cells(r, 15).Value = (SlsTwelveMthDecimal - LYSlsTwelveMthDecimal) / LYSlsTwelveMthDecimal
                Else
                    objExcel.Worksheets(WorksheetNameString).Cells(r, 15).Value = "-"
                End If
                '' ''If ExtCostLocDecimal <> 0 Then
                '' ''    objExcel.Worksheets(WorksheetNameString).Cells(r, 17).Value = ExtCostLocDecimal
                '' ''    objExcel.Worksheets(WorksheetNameString).Cells(r, 18).Value = ExtCostSalesLocDecimal - ExtCostLocDecimal
    
                '' ''    If ExtCostSalesLocDecimal <> 0 Then
                '' ''        objExcel.Worksheets(WorksheetNameString).Cells(r, 19).Value = (ExtCostSalesLocDecimal - ExtCostLocDecimal) / ExtCostSalesLocDecimal
                '' ''    End If
                '' ''    '
                '' ''End If
    
                '
    
                objExcel.Worksheets(WorksheetNameString).Cells(r, 1).font.bold = True
                objExcel.Worksheets(WorksheetNameString).Cells(r, 4).font.bold = True
                objExcel.Worksheets(WorksheetNameString).Cells(r, 5).font.bold = True
                objExcel.Worksheets(WorksheetNameString).Cells(r, 6).font.bold = True
                objExcel.Worksheets(WorksheetNameString).Cells(r, 8).font.bold = True
                objExcel.Worksheets(WorksheetNameString).Cells(r, 10).font.bold = True
                objExcel.Worksheets(WorksheetNameString).Cells(r, 11).font.bold = True
                objExcel.Worksheets(WorksheetNameString).Cells(r, 12).font.bold = True
                objExcel.Worksheets(WorksheetNameString).Cells(r, 14).font.bold = True
                objExcel.Worksheets(WorksheetNameString).Cells(r, 15).font.bold = True
                objExcel.Worksheets(WorksheetNameString).Cells(r, 17).font.bold = True
                objExcel.Worksheets(WorksheetNameString).Cells(r, 18).font.bold = True
                objExcel.Worksheets(WorksheetNameString).Cells(r, 19).font.bold = True
    
                BeginCellAddressString = objExcel.Worksheets(WorksheetNameString).Cells(r, 1).address(False, False)
                EndCellAddressString = objExcel.Worksheets(WorksheetNameString).Cells(r, 15).address(False, False)
                objExcel.Worksheets(WorksheetNameString).range(BeginCellAddressString, EndCellAddressString).select()
                With objExcel.application.selection.interior
                    .Pattern = 1
                    .PatternColorIndex = -4105
                    .ThemeColor = 4
                    .TintAndShade = 0.799981688894314
                    .PatternTintAndShade = 0
                End With
                'objExcel.Worksheets(WorksheetNameString).range("A8").select()
            Else
                'FormatRow(worksheet, r)
                ''
    
                'For i = 0 To 15
                '    Dim solidPatternFill As New PatternFill(PatternType.Solid, System.Windows.Media.Color.FromRgb(197, 217, 241), System.Windows.Media.Colors.Transparent)
                '    worksheet.Cells(r, i).SetFill(solidPatternFill)
                '    SS.SetCellBold(worksheet, r, i, True)
                'Next
                'SS.WriteCellValue(worksheet, r, 1 - 1, "End of Location: - " & LocFgString)
                'SS.WriteCellValue(worksheet, r, 4 - 1, QtySixMthDecimal)
                'SS.WriteCellValue(worksheet, r, 6 - 1, SlsSixMthDecimal)
    
                'SS.WriteCellValue(worksheet, r, 10 - 1, QtyTwelveMthDecimal)
                'SS.WriteCellValue(worksheet, r, 12 - 1, SlsTwelveMthDecimal)
                ''
                'SS.WriteCellValue(worksheet, r, 5 - 1, LYQtySixMthDecimal)
                'SS.WriteCellValue(worksheet, r, 8 - 1, LYSlsSixMthDecimal)
                'SS.WriteCellValue(worksheet, r, 11 - 1, LYQtyTwelveMthDecimal)
                'SS.WriteCellValue(worksheet, r, 14 - 1, LYSlsTwelveMthDecimal)
    
                'If LYSlsTwelveMthDecimal <> 0 Then
                '    SS.WriteCellValue(worksheet, r, 15 - 1, (SlsTwelveMthDecimal - LYSlsTwelveMthDecimal) / LYSlsTwelveMthDecimal)
                'Else
                '    SS.WriteCellValue(worksheet, r, 15 - 1, "-")
                'End If
    
            End If
    
        End Sub
    End Module
    

    Thursday, April 19, 2018 8:36 PM
  • Hi mpdillon99,

    Currently, I am not available with 1200 rows of sample data to make a test with this code.

    But I try to check the code and find that, In your code you are doing same kind of operations on multiple columns, cells.

    Like below.

    You can see that on many places in your code.

    Instead of performing the same kind of operations for every single columns or cell multiple times, You can try to perform it on range. So it can be done with in single execution.

    So Excel not need to execute same kind of operations again and again.

    Which can help you to reduce the execution time.

    Other then that you can refer link below may help you to get some more information regarding how to improve performance in Excel VBA.

    Excel VBA Performance Coding Best Practices

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, April 20, 2018 1:32 AM
    Moderator