Andrei Smolin

Creating a fast Excel add-in (C#, VB.NET). Reading and updating cells.

I will start with a citation: “I am reading a sheet from an Excel File… I am pooling (with a While loop) the entire worksheet to process each cell … The problem is that it is VERY SLOW because the file is huge… Is there a faster way to read the information?”

This is a known problem: doing things cell by cell in Excel is a slow operation. The Excel object model provides two ways to speed up the code.

Updating cells

First off, you can set ScreenUpdating = false before modifying the cells and return it back to true after the changes are made. This prevents Excel from drawing the modified value of EVERY cell. Instead, Excel repaints all cells after you set ScreenUpdating = true.

Secondly, you can assign an array to the Range.Value property; assuming that the range of cells and the array of values have corresponding dimensions.

The ways discussed above were tested in the template[d] code below. Note that the code sample starts with calling the function IsEditing() described in How to check programmatically if the user is editing an Excel cell.

C#:

if (IsEditing()) {
    System.Media.SystemSounds.Beep.Play();
    return;
}
string[,] arr = InitializeData();
DateTime startTime = DateTime.Now;
%%method%%(arr);
DateTime stopTime = DateTime.Now;
TimeSpan elapsedTime = stopTime - startTime;
System.Diagnostics.Debug.WriteLine(elapsedTime.TotalMilliseconds.ToString());

VB.NET:

If IsEditing() Then
    System.Media.SystemSounds.Beep.Play()
    Return
End If
Dim arr As String(,) = InitializeData()
Dim startTime As DateTime = DateTime.Now
%%method%%(arr)
Dim stopTime As DateTime = DateTime.Now
Dim elapsedTime As TimeSpan = stopTime - startTime
System.Diagnostics.Debug.WriteLine(elapsedTime.TotalMilliseconds.ToString())

The results are:

Method Elapsed time (in milliseconds)
Cell by cell (see Slowest) ~ 56,000
Cell by cell & ScreenUpdating (see Slow) ~ 51,000
All cels at once (see Fast) ~ 600

If you update a single range, the ScreenUpdating trickery will not get the Fast method to work faster. If, however, you update several ranges, wrapping the update process with ScreenUpdating=false and ScreenUpdating=true is a good idea: the more ranges you process in a go, the more speed you’ll gain.

Reading cells

If the range contains many cells, Range.Value returns an object type that you can cast to the array of the corresponding type. To check if Range.Value returns an array, you use the Type.IsArray property, for instance range.GetType().IsArray.

Updating cells. The methods.

C#:

private void Slowest(string[,] arr) {
    Excel._Worksheet sheet = ExcelApp.ActiveSheet as Excel._Worksheet;
    Excel.Range sheetCells = sheet.Cells;
    Excel.Range cellFirst = sheetCells[1, 1] as Excel.Range;
    Excel.Range cellLast = sheetCells[MaxRows, MaxColumns] as Excel.Range;
    Excel.Range theRange = sheet.get_Range(cellFirst, cellLast);
    for (int iRow = 1; iRow < = MaxRows; iRow++) {
        for (int iColumn = 1; iColumn <= MaxColumns; iColumn++) {
            (theRange.Cells[iRow, iColumn] as Excel.Range).set_Value(Type.Missing,arr[iRow, iColumn]);
        }
    }
}

VB.NET:

Private Sub Slowest(arr As String(,))
    Dim sheet As Excel._Worksheet = TryCast(ExcelApp.ActiveSheet, Excel._Worksheet)
    Dim sheetCells As Excel.Range = sheet.Cells
    Dim cellFirst As Excel.Range = TryCast(sheetCells(1, 1), Excel.Range)
    Dim cellLast As Excel.Range = TryCast(sheetCells(MaxRows, MaxColumns), Excel.Range)
    Dim theRange As Excel.Range = sheet.Range(cellFirst, cellLast)
    For iRow As Integer = 0 To MaxRows - 1
        For iColumn As Integer = 0 To MaxColumns - 1
            TryCast(theRange.Cells(iRow + 1, iColumn + 1), Excel.Range).Value = _
                arr(iRow, iColumn)
        Next
    Next
End Sub

C#:

private void Slow(string[,] arr) {
    ExcelApp.ScreenUpdating = false;
    Slowest(arr);
    ExcelApp.ScreenUpdating = true;
}

VB.NET:

Private Sub Slow(arr As String(,))
    ExcelApp.ScreenUpdating = False
    Slowest(arr)
    ExcelApp.ScreenUpdating = True
End Sub

C#:

private void Fast(string[,] arr) {
    Excel._Worksheet sheet = ExcelApp.ActiveSheet as Excel._Worksheet;
    Excel.Range sheetCells = sheet.Cells;
    Excel.Range cellFirst = sheetCells[1, 1] as Excel.Range;
    Excel.Range cellLast = sheetCells[MaxRows, MaxColumns] as Excel.Range;
    Excel.Range theRange = sheet.get_Range(cellFirst, cellLast);
    theRange.set_Value(Type.Missing, arr);
}

VB.NET:

Private Sub Fast(arr As String(,))
    Dim sheet As Excel._Worksheet = TryCast(ExcelApp.ActiveSheet, Excel._Worksheet)
    Dim sheetCells As Excel.Range = sheet.Cells
    Dim cellFirst As Excel.Range = TryCast(sheetCells(1, 1), Excel.Range)
    Dim cellLast As Excel.Range = TryCast(sheetCells(MaxRows, MaxColumns), Excel.Range)
    Dim theRange As Excel.Range = sheet.Range(cellFirst, cellLast)
    theRange.Value = arr
End Sub

Good luck!

UPD: Added the VB.NET version of the code as per suggestion. Thank you, Henri!

14 Comments

  • https://secure.gravatar.com/avatar/371e4b037974623acab3a397d0e0bf2a?s=32&d=https%3A%2F%2Fsecure.gravatar.com%2Favatar%2Fad516503a11cd5ca435acc9bb6523536%3Fs%3D32&r=G Marc Bernier says:

    set_Value and get_Value do not exist in Excel 2000, true?

  • https://secure.gravatar.com/avatar/29957f26ad2d8ba527fd9cc8cfa7b2e0?s=32&d=https%3A%2F%2Fsecure.gravatar.com%2Favatar%2Fad516503a11cd5ca435acc9bb6523536%3Fs%3D32&r=G Andrei Smolin (Add-in Express Team) says:

    With the Excel 2000 interop, you use oldValue = Range.Value; and Range.Value = newValue;

  • https://secure.gravatar.com/avatar/93eb74ac9eb7096d06adc32281b16429?s=32&d=https%3A%2F%2Fsecure.gravatar.com%2Favatar%2Fad516503a11cd5ca435acc9bb6523536%3Fs%3D32&r=G Phil Stow says:

    How can you use the same technique but with a named ranged in the sheet. I want to be able to read the range to an array, amend it and then repopulate the named range with the contents of the array?

  • https://secure.gravatar.com/avatar/29957f26ad2d8ba527fd9cc8cfa7b2e0?s=32&d=https%3A%2F%2Fsecure.gravatar.com%2Favatar%2Fad516503a11cd5ca435acc9bb6523536%3Fs%3D32&r=G Andrei Smolin (Add-in Express Team) says:

    Hello Phil,

    The Excel.Name object provides the RefersToRange property, see http://msdn.microsoft.com/en-us/library/office/ff834918.aspx. Then you use the range as shown above.

  • https://secure.gravatar.com/avatar/1e3f10fa8401ffe83bf3ff828a103664?s=32&d=https%3A%2F%2Fsecure.gravatar.com%2Favatar%2Fad516503a11cd5ca435acc9bb6523536%3Fs%3D32&r=G Jorge Pizarro says:

    Thanks a lot. Very useful!!

  • https://secure.gravatar.com/avatar/889ece7822858d9f7c007d6b87ac54c5?s=32&d=https%3A%2F%2Fsecure.gravatar.com%2Favatar%2Fad516503a11cd5ca435acc9bb6523536%3Fs%3D32&r=G Keith says:

    theRange.Value = arr

    Is that backwards?

    You pass in a double array and then set range to the array?

    Dim xlAppTest As New Excel.Application
    Dim xlWorkbookIn As Excel.Workbook = xlAppTest.Workbooks.Open(“C:\Users\LOGIN\Desktop\test.xls”)
    Dim xlSheet As Excel._Worksheet = TryCast(xlAppTest.ActiveSheet, Excel._Worksheet)
    Dim xlRange As Excel.Range = xlSheet.Cells
    Dim xlRangeFull As Excel.Range = xlRange.Range(TryCast(xlRange(1, 1), Excel.Range), TryCast(xlRange(50, 30), Excel.Range))
    Dim sArray As String(,)
    sArray = xlRange.Value

    I’m getting an insuficient memory error.

  • https://secure.gravatar.com/avatar/29957f26ad2d8ba527fd9cc8cfa7b2e0?s=32&d=https%3A%2F%2Fsecure.gravatar.com%2Favatar%2Fad516503a11cd5ca435acc9bb6523536%3Fs%3D32&r=G Andrei Smolin (Add-in Express Team) says:

    Hello Keith,

    sArray = xlRangeFull.Value, not sArray = xlRange.Value

  • https://secure.gravatar.com/avatar/cd2f9012a4d64675540ddb701d163565?s=32&d=https%3A%2F%2Fsecure.gravatar.com%2Favatar%2Fad516503a11cd5ca435acc9bb6523536%3Fs%3D32&r=G Willem says:

    Hi,

    I use this technique on a VSTO add-in. But all values are now strings. So if I have a DateTime in the array, it is placed as General type in Excel. Changing the cell to ShortDate does not make it a date untill I enter the cell (F2) and press Enter.

    Same happens with numbers. (number stored as text)

    Is there a solution for this?

  • https://secure.gravatar.com/avatar/29957f26ad2d8ba527fd9cc8cfa7b2e0?s=32&d=https%3A%2F%2Fsecure.gravatar.com%2Favatar%2Fad516503a11cd5ca435acc9bb6523536%3Fs%3D32&r=G Andrei Smolin (Add-in Express Team) says:

    Hello Willem,

    You can record a VBA macro while formatting a cell to DateTime. Then use this approach in your code.

  • https://secure.gravatar.com/avatar/3b10bc2c568fa406303ec0b15e690525?s=32&d=https%3A%2F%2Fsecure.gravatar.com%2Favatar%2Fad516503a11cd5ca435acc9bb6523536%3Fs%3D32&r=G Mukesh Kumar says:

    Hi,
    I like you approach by inserting the whole array in one shot rather than cell by cell. I also want to insert the comments in one shot by using array. Can you please help me how I can do this? I did this by using rngToolTip.AddComment(sComment); where sComment is an string array and rngToolTip is the range from A1:A3000.
    Thanks,
    Mukesh

  • https://secure.gravatar.com/avatar/29957f26ad2d8ba527fd9cc8cfa7b2e0?s=32&d=https%3A%2F%2Fsecure.gravatar.com%2Favatar%2Fad516503a11cd5ca435acc9bb6523536%3Fs%3D32&r=G Andrei Smolin (Add-in Express Team) says:

    Hello Mukesh,

    Regarding getting/setting cell values, that approach is built in Excel. There’s no equivalent approach when dealing with formulas, comments and other aspects of a range of cells.

  • https://secure.gravatar.com/avatar/05b2bd9dda84f03a71b58ef5156b7bd0?s=32&d=https%3A%2F%2Fsecure.gravatar.com%2Favatar%2Fad516503a11cd5ca435acc9bb6523536%3Fs%3D32&r=G Jorge Di says:

    Hello Andrei, I hope you are well.

    I am working with Add-in Express to do an Excel Add-In and every code that I write takes like 10 seconds to run when I click the corresponding button in Microsoft Excel.

    Not all of the codes use cell ranges, so I don´t know if the solution is assigning an array to the Range.Value property.

    Is there any way to speed this up?

    If I write my code on VBA and run it directly from excel it runs instantly… this is why I am struggling.

    Thank you! Have a great day!

  • https://secure.gravatar.com/avatar/05b2bd9dda84f03a71b58ef5156b7bd0?s=32&d=https%3A%2F%2Fsecure.gravatar.com%2Favatar%2Fad516503a11cd5ca435acc9bb6523536%3Fs%3D32&r=G Jorge Di says:

    I have an idea…

    I have been reading about the problem and there seems to be a way to call a VBA code through VSTO… Is this possible?

    I already have all my VBA code written it would be wonderful if I could call the code with the push of the buttons.

  • https://secure.gravatar.com/avatar/29957f26ad2d8ba527fd9cc8cfa7b2e0?s=32&d=https%3A%2F%2Fsecure.gravatar.com%2Favatar%2Fad516503a11cd5ca435acc9bb6523536%3Fs%3D32&r=G Andrei Smolin (Add-in Express Team) says:

    Hello Jorge,

    You call a VBA macro using ExcelApp.Run(), see https://msdn.microsoft.com/en-us/library/office/ff197132%28v=office.15%29.aspx. As to the difference, I suppose this may relate to some problem in your non-VBA code.

Post a comment

Have any questions? Ask us right now!