Convert Formulae in Macro to VBA to save time?

[ClimoC]

Hey all

I have a whizzy lil spreadsheet which 'draws' a scheduling grid based on the table in the same sheet.

Now since the table gets edited manually for changes in scheduling, and new rows and inserted, deleted etc all the time - I've redesigned the drawn grid so that instead of all 15,000 cells (500 rows x 30 cols for days of the month) having a long complex formula in it, the formula is now in a macro which does the calculations on the cell only when requested (and therefor, means the drawn grid never has any errors in it).

My problem is that to do this takes 3:07 each time the macro runs. The actual 'drawing' (conditional formatting) takes the 7secs, calculating the cells takes 3minutes.

This is an unacceptable amount of time for a modern computer to take to do calulations. I understand it's not going to get any faster - but if I change the calculations to vba instead of formulae will it run significantly faster?

here's the macro in full (though the calculation part is only the first section as you'll see)

Sub DrawGrid()

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Set TheGrid = Range("AS6:BW500")
    For Each Cell In TheGrid
        Cell.FormulaR1C1 = _
        "=IF(AND(R5C=RC13,R5C<>RC14),""D/L"",IF(AND(R5C>=RC14,R5C<=RC16),RC19,IF(R5C=RC17,""LC"","""")))"
        
Application.Calculation = xlCalculationAutomatic
    Next
    
Application.Goto Reference:="R6C45:R500C75"
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
Set MyGrid = Range("AS6:BW500")
    For Each Cell In MyGrid
        If Cell.Value = "" Then
            Cell.Interior.ColorIndex = 0
            Cell.Font.ColorIndex = 0
        End If
        If Cell.Value = "D/L" Then
            Cell.Interior.ColorIndex = 4
            Cell.Font.ColorIndex = 4
        End If
        If Cell.Value = "LC" Then
            Cell.Interior.ColorIndex = 48
            Cell.Font.ColorIndex = 48
        End If
        If Cell.Value <> "" And Cell.Value <> "D/L" And Cell.Value <> "LC" Then
            Cell.Interior.ColorIndex = 5
            Cell.Font.ColorIndex = 1
        End If
        Next
        
Application.Goto Reference:="R6C45:R500C75"
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
Application.Goto Reference:="R6C45:R500C75"
    With Selection
        .HorizontalAlignment = xlCenter
    End With

Range("AS6").Select
Application.ScreenUpdating = True

        
End Sub

Will changing the If statement into its vba equivalent speed up this process?

If so, what code will I be using???

Cheers
C

 

[ClimoC]

PS - please IGNORE the 'Manual'/'Automatic# Calculation bit. I added that into the code just moments ago to see if it would improve it (it actually made it take 4x as long) and forgot to revert it back to it's original state - which is with Automatic Calculations all the time.

 

[taigovinda]

Try moving xlcalcautomatic outside of the for loop.

You turn off calc before starting the loop. Then you do the loop to put in all the formulas. THEN you should turn calc back on. Right? See if it helps if you just move the line to turn calc back on, underneath of the "next" instead of above it.

 

[taigovinda]

Also you don't need that loop at all. You can assign that formula to the entire range at the same time. In order to record the code to do that: Turn on the macro recorder. Select the whole range. Type in the formula that should go into the (single) active cell. Press control+enter. Stop the recorder. It should be pretty clear what replaces your for loop.

 

[ClimoC]

Wow! Thanks taigovinda!

I don't think I can do your last suggestion - as the formula in each cell is checking different rows and columns.

HOWEVER....

I did as you suggested, moved the automatic calculation bit outside of the next, and VOILA!!! From 3:07 all the way down to 11 seconds!!!!

Brilliant!

So simple!

Cheers mate!

 

[taigovinda]

Glad it helped

...You can, though, eliminate that loop. I'm not a pro, so what I said might not work. But what will is to (use VBA in order to) put the formula in the top cell, then copy and paste that formula to the rest of the cells. That way, you can eliminate the for loop *and* the calc on/off. Just write one formula, copy and paste it to the rest of the cells... I guess this one's working to your satisfaction - but next time, you can definitely record yourself doing that, then insert the recorded code into your macro.

Cheers,
Tai

Edit: Actually, the first way I suggested *does* work. When you select an entire range, proceed to type in a relative reference that is appropriate for the single active cell, and press control+enter, the relative formula is entered correctly for all the cells. Example:

Excel Workbook
	A	B	C
1	1	1	2
2	2	2	4
3	3	3	6
4	4	4	8
Sheet1

Excel 2003

Cell Formulas
Range	Formula
C1	=A1+B1
C2	=A2+B2
C3	=A3+B3
C4	=A4+B4