Results 1 to 4 of 4

Code works fine when stepping through code but Excel freezes if I run it without stepping

Threaded View

OldManExcellor Code works fine when stepping... 01-15-2016, 09:02 AM
LJMetzger Re: Code works fine when... 01-15-2016, 10:30 AM
OldManExcellor Re: Code works fine when... 01-19-2016, 10:04 AM
LJMetzger Re: Code works fine when... 01-19-2016, 10:39 AM
  1. #1
    Forum Contributor
    Join Date
    05-30-2011
    Location
    Sweden
    MS-Off Ver
    Excel 2013
    Posts
    107

    Code works fine when stepping through code but Excel freezes if I run it without stepping

    The following macro Main_FinExport runs fine when I start the procedure of with Stop and then step through the code with Shift+F8. But when I just run it, Excel crashes every time. Any ideas for how I can fix this?

    Option Explicit
    
    Sub Main_FinExport()
        Stop
        If bProduction = True Then On Error GoTo ErrHandler
    
        Application.ScreenUpdating = False
        Application.Calculation = xlCalculationManual
        
        Dim wbkSource As Workbook
        Set wbkSource = Workbooks.Open(wFinExport.Range("FinMasterPath"), False, True)
        
        Dim pvtSource As PivotTable
        Set pvtSource = wbkSource.Sheets("Fin").PivotTables(1)
        
        Dim wbkTarget As Workbook
        Set wbkTarget = Workbooks.Add
    
        Dim wksTarget As Worksheet
        Set wksTarget = wbkTarget.Sheets.Add
        wksTarget.Name = "Fin export"
        
        Dim wksTargetOriginal As Worksheet
        Set wksTargetOriginal = wbkTarget.Sheets.Add
        wksTargetOriginal.Name = "Fin export original"
            
        Dim sCurrentMonth As String
        sCurrentMonth = GetCurrentMonth(wbkSource)
    
        Call CopyValues(pvtSource, wksTarget, wksTargetOriginal)
        Call HighlightCurrentMonth(sCurrentMonth, wksTarget, wksTargetOriginal)
        Call WriteFormulas(sCurrentMonth, wksTarget)
        Call HighlightChanges(wksTarget, wksTargetOriginal)
        Call FreezePanes(wksTarget)
        Call DeleteDefaultSheetsAndActivateFirst(wbkTarget)
        wksTargetOriginal.Visible = xlSheetHidden
        
        Call LogMacro(wbkSource.Sheets("MacroLog"), Now, "Export Fin", "")
        
        'Save and close target file
        Dim sFileName As String
        sFileName = wFinExport.Range("ExportFolder").Value & "Fin playground " & Replace(Now, ":", "-")
        Application.DisplayAlerts = False
        wbkTarget.Close SaveChanges:=True, Filename:=sFileName
        Application.DisplayAlerts = True
        'Close Fin file
        wbkSource.Close SaveChanges:=False
        
        Application.ScreenUpdating = True
        Application.Calculation = xlCalculationAutomatic
        
        MsgBox ("Export of Fin report has been completed.")
    Exit Sub
    ErrHandler:
        MsgBox ("An error happened. Do not save the files.")
    End Sub
    
    Sub CopyValues(pvtSource As PivotTable, wksTarget As Worksheet, wksTargetOriginal As Worksheet)
        ' One row at a time to not run out of memory
        Dim r As Range
        
        wksTarget.Activate
        For Each r In pvtSource.TableRange1.Rows
            r.Copy wksTarget.Cells(r.Row - pvtSource.TableRange1.Row + 1, 1)
        Next
        
        wksTargetOriginal.Activate
        For Each r In pvtSource.TableRange1.Rows
            r.Copy wksTargetOriginal.Cells(r.Row - pvtSource.TableRange1.Row + 1, 1)
        Next
        
    End Sub
    
    Function GetCurrentMonth(wbkSource As Workbook) As String
    
        Dim caches As Excel.SlicerCaches
        Set caches = wbkSource.SlicerCaches
    
        Dim cache As Excel.SlicerCache
        Set cache = wbkSource.SlicerCaches("Slicer_CurrentMonthName")
        
        Dim sCurrentMonthPowerString As String
        sCurrentMonthPowerString = cache.VisibleSlicerItemsList(1)
        
        Dim sCurrentMonth As String
        sCurrentMonth = GetPivotItemFromPowerPivot(sCurrentMonthPowerString)
        
        GetCurrentMonth = sCurrentMonth
    
    End Function
    
    Sub HighlightCurrentMonth(sCurrentMonth As String, wksTarget As Worksheet, wksTargetOriginal As Worksheet)
        Dim lCurrentMonthCol As Long
        lCurrentMonthCol = wksTarget.UsedRange.Find(What:=sCurrentMonth, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False).Column
        
        wksTarget.Columns(lCurrentMonthCol).Interior.Color = RGB(122, 122, 122)
        
        Dim lMonthNameRow As Long
        lMonthNameRow = wksTarget.UsedRange.Find(What:="MonthName", LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False).Row
    
        wksTarget.Cells(lMonthNameRow, lCurrentMonthCol) = "Current Month"
        wksTarget.Cells(lMonthNameRow, LastColumn(wksTarget)) = "Last Forecast Month"
        
        wksTargetOriginal.Cells(lMonthNameRow, lCurrentMonthCol) = "Current Month"
        wksTargetOriginal.Cells(lMonthNameRow, LastColumn(wksTargetOriginal)) = "Last Forecast Month"
    
    End Sub
    
    Sub WriteFormulas(sCurrentMonth As String, wks As Worksheet)
        Dim lCurrentMonthCol As Long
        lCurrentMonthCol = wks.UsedRange.Find(What:=sCurrentMonth, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False).Column
        Dim lFirstMonthCol As Long
        lFirstMonthCol = wks.UsedRange.Find(What:="MonthName", LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False).Column
        
        ' There must be historical month for the inventory formula to work
        If lCurrentMonthCol = lFirstMonthCol Then
            MsgBox ("Please filter to show at least one actual month in the Fin power pivot report and then run the Fin export again. This is required for the inventory formulas to work.")
        End If
        
        Dim lCustomerCol As Long
        lCustomerCol = wks.UsedRange.Find(What:="Customer", LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False).Column
            
        Dim lLastCol As Long
        lLastCol = LastColumn(wks)
        
        Dim lLastRow As Long
        lLastRow = LastRow(wks)
        
        Dim c As Long
        Dim r As Long
        Dim lLastPurchaseRow As Long
        Dim lLastSalesRow As Long
        
        For r = 1 To lLastRow
            ' Save row number
            If wks.Cells(r, lCustomerCol) = "Purchase" Then
                lLastPurchaseRow = r
            ElseIf wks.Cells(r, lCustomerCol) = "Sales" Then
                lLastSalesRow = r
            End If
            
            'Write formula
            If wks.Cells(r, lCustomerCol) = "Sales" Then
                'Test if there are any sales company rows
                If lLastPurchaseRow < r - 1 Then
                    wks.Range(wks.Cells(r, lCurrentMonthCol), wks.Cells(r, lLastCol)).Formula = "=SUM(" & wks.Cells(lLastPurchaseRow + 1, lCurrentMonthCol).Address(RowAbsolute:=False, ColumnAbsolute:=False) & ":" & wks.Cells(r - 1, lCurrentMonthCol).Address(RowAbsolute:=False, ColumnAbsolute:=False) & ")"
                Else
                    wks.Range(wks.Cells(r, lCurrentMonthCol), wks.Cells(r, lLastCol)).Formula = "=SUM(0)"
                End If
            ElseIf wks.Cells(r, lCustomerCol) = "Inventory" Then
                wks.Range(wks.Cells(r, lCurrentMonthCol), wks.Cells(r, lLastCol)).Formula = "=" & wks.Cells(r, lCurrentMonthCol - 1).Address(RowAbsolute:=False, ColumnAbsolute:=False) & "+" & wks.Cells(lLastPurchaseRow, lCurrentMonthCol).Address(RowAbsolute:=False, ColumnAbsolute:=False) & "-" & wks.Cells(lLastSalesRow, lCurrentMonthCol).Address(RowAbsolute:=False, ColumnAbsolute:=False)
            End If
        Next
    End Sub
    
    Sub HighlightChanges(wksTarget As Worksheet, wksOriginal As Worksheet)
        Dim lFirstRow As Long
        Dim lLastRow As Long
        Dim lFirstCol As Long
        Dim lLastCol As Long
        Dim r As Range
        Dim vTempValue As Variant
        
        lFirstRow = 1
        lLastRow = LastRow(wksTarget)
        lFirstCol = 1
        lLastCol = LastColumn(wksTarget)
    
        Set r = wksTarget.Range(wksTarget.Cells(lFirstRow, lFirstCol), wksTarget.Cells(lLastRow, lLastCol))
            
        With r
            r.Parent.Parent.Activate 'Se comment on row below
            r.Parent.Activate 'Se comment on row below
            .Select 'Necissary when working with formatconditions in VBA and using relative references because of bug in Excel 2007 and earlier
            .FormatConditions.Delete
    
            vTempValue = wksTarget.Cells(1, 1).Value 'Put formula into cell because of localization bug in conditional formatting formula1
            wksTarget.Cells(1, 1).Formula = "=" & wksTarget.Cells(1, 1).Address(RowAbsolute:=False, ColumnAbsolute:=False) & "<>" & "'" & wksOriginal.Name & "'!" & wksOriginal.Cells(1, 1).Address(RowAbsolute:=False, ColumnAbsolute:=False)
            
            .FormatConditions.Add Type:=xlExpression, Formula1:=wksTarget.Cells(1, 1).FormulaLocal
            
            .FormatConditions(.FormatConditions.Count).Interior.Color = RGB(255, 165, 0)
            wksTarget.Cells(1, 1) = vTempValue 'restore
                
        End With
    
    
    End Sub
    
    Sub FreezePanes(wksTarget As Worksheet)
        
        Dim lHeaderCol As Long
        lHeaderCol = wksTarget.UsedRange.Find(What:="Customer", LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False).Column
        Dim lHeaderRow As Long
        lHeaderRow = wksTarget.UsedRange.Find(What:="Customer", LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False).Row
        
        ActiveWindow.ScrollRow = 1
        ActiveWindow.ScrollColumn = 1
        ActiveWindow.FreezePanes = False
        wksTarget.Parent.Activate
        wksTarget.Cells(lHeaderRow + 1, lHeaderCol + 1).Activate
        ActiveWindow.FreezePanes = True
    
    End Sub
    Last edited by OldManExcellor; 01-15-2016 at 09:11 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Stepping through code produces different result than running code
    By Sc0tt1e in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-19-2015, 08:27 AM
  2. Replies: 1
    Last Post: 03-31-2015, 03:01 PM
  3. [SOLVED] Excel-to-eMail Code Works Only When Stepping Through
    By EnigmaMatter in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-26-2014, 11:19 PM
  4. VBA code behaves differently while stepping?
    By mhni in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-10-2009, 09:01 AM
  5. [SOLVED] Stepping through Code
    By ben in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 02-08-2006, 05:50 PM
  6. How to get value of variable when single stepping through code?
    By Chet Shannon in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-22-2005, 05:55 PM
  7. Code works when stepping through, but cuts out when run
    By Reenen in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-21-2005, 07:47 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1