Retrieve all the fonts styles applied(used) for all the cells in an Excel in C# -Vsto RRS feed

  • Question

  • I am new in the .Net world. I am writing an Addin in C#(vsto) for Excel, where I want to read all the fonts styles that are used in every cells in a given the Excel worksheet. The below code works,

                List<string> fontsInSheet = new List<string>();
                Excel.Range usedRange = currentSheet.UsedRange;
                for (int i = 1; i <= usedRange.Rows.Count; i++)
                    for (int j = 1; j <= usedRange.Columns.Count; j++)
                        Range excelCellRange = currentSheet.Cells[i, j];
                        if (!(excelCellRange.Font.Name is DBNull) &&(!fontsInSheet.Contains(excelCellRange.Font.Name)))

    But the nested for loops are unfortunately too slow if Excel sheets are too big. So, ist there better solution available to retrieve used font styles more efficiently. Thanks in advance.

    Saturday, August 8, 2020 10:03 AM

All replies

  • You may try to use the following properties before and return their values back after:

    Sub OptimizeVBA(enable As Boolean)    Application.Calculation = IIf(enable, xlCalculationManual, xlCalculationAutomatic)    Application.EnableEvents = Not(enable)   

    Application.ScreenUpdating = Not(enable)   

    ActiveSheet.DisplayPageBreaks = Not(enable) End Sub

    Read more about these properties and other possible approaches in the GUIDE TO IMPROVING VBA PERFORMANCE. FASTER EXCEL VBA article.

    profile for Eugene Astafiev at Stack Overflow, Q&A for professional and enthusiast programmers

    Saturday, August 8, 2020 4:19 PM