slow cell formatting using vba?

Question

Disclaimer: I am relatively new to vba and macros.

I have written a macro to update value and formatting in some individual cells after reading and parsing a json through http and the process is very slow, so I broke down the code into different portions to see where the bottleneck might be. Turns out the cell updating is the problem, I have the following code:

Sub test()
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False
    Application.EnableCancelKey = False
    t = Timer
        With Range("A1")
            .Font.Italic = True
            .Interior.ColorIndex = 37
            .Value = 3412
        End With
    Debug.Print Timer - t
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
    Application.EnableCancelKey = True
End Sub

the debug print is about 0.3 to 0.5 sec... I have afterwards further wrapped the timer around each of the italic, colorIndex, and value lines and they all turns out take about 0.015 sec each... I have tried searching online how to make the code more efficient, hence the screenupdating toggles as well as no selection, but 0.5 sec still seem a bit slow in updating a cell to me.

please note that I am not whining, I just want to know if I am doing the right thing here. Is there a more efficient way to implement the formatting and value changes that I posted here, or is it just a fact that excel takes this amount of time to update the cell? I am just very curious because the json reading and parsing that I also implemented are significantly faster than this.

Also I have tested this script on at least 3 computers and they all take around the same time so I don't think it's an individual computer problem. And I used excel 2007 and 2010 to test.

Answer 1

I assume you are wanting to format more than a single cell? If so, it will be faster to create a range reference to all the cells requiring the same format (it need not be contiguous), then apply the required format to that range object in one step

Following example demo's creating a range reference, and applying format in one go

Option Explicit

Private Declare Function GetTickCount Lib "kernel32" () As Long

Sub Demo()
    Dim t As Long
    Dim n As Long, i As Long
    Dim m As Long
    Dim ws As Worksheet
    Dim cl As Range
    Dim rSearch As Range
    Dim rResult  As Range

    Set ws = ActiveSheet ' or another sheet...

    Set rSearch = ws.Range("A1:A1000")

    ' note, this is an inefficient loop, can be made much faster
    ' details will depend on the use case
    For Each cl In rSearch
        ' determine if cell is to be formatted
        If cl.Row Mod 2 = 0 Then
            ' add cl to Result range
            If rResult Is Nothing Then
                Set rResult = cl
            Else
                Set rResult = Application.Union(rResult, cl)
            End If
        End If
    Next

    Debug.Print "Result Range includes ", rResult.Cells.Count, "Cells"
    t = GetTickCount
    ' Apply format
    With rResult
        .Font.Italic = True
        .Interior.ColorIndex = 37
        .Value = 3412
    End With
    Debug.Print (GetTickCount - t) / 1000, "seconds"

End Sub