Get multiple cell properties from Excel in one call?

Question

I need to get the background property ( Range.Interior.Color ) of several thousand cells. Passing through each cell individually is very slow due to COM-Interop restrictions.

Is it possible to get cell properties that are not .Text , .Value or .Value2 , from a Range containing more than one cell in one call?

Answer 1

You can do this, but it’s not trivial, so I’m not sure if it’s worth the fuss trying to simulate this behavior.

Basically, you need to create a macro in Excel that does the job, and then just return the results after the macro completes. This basically emulates the behavior of Value . I'm not quite sure why MS decided not to implement all the properties in Range to behave the same way, wierd.

To do this, you need to refer to the Microsoft Visual Basic for Application Extensibility 5.3 COM library. This gives you the tools you need to dynamically build and add macros to an Excel workbook.

The first step is to create a method that adds the module and the desired macro to the workbook you are interacting with (you can also open an empty workbook and create a macro there if you use this function on different sheets that you use opening and closing).

The following example adds a macro that returns an array with all the colors of the cells in the specified range. It is written in VBA, I do not have VS with me, but the C # version should be quite simple for the port.

 Sub AddCode() Dim wb As Workbook Dim xPro As VBIDE.VBProject Dim xCom As VBIDE.VBComponent Dim xMod As VBIDE.CodeModule Set wb = ActiveWorkbook With wb Set xPro = .VBProject Set xCom = xPro.VBComponents.Add(vbext_ct_StdModule) Set xMod = xCom.CodeModule With xMod .AddFromString "Public Function GetInteriorColors(r As Range) As Variant" & vbCrLf & _ " Dim colors() As Long" & vbCrLf & _ " Dim row As Integer" & vbCrLf & _ " Dim column As Integer" & vbCrLf & _ " ReDim colors(r.Rows.Count - 1, r.Columns.Count - 1)" & vbCrLf & _ " For row = 1 To r.Rows.Count" & vbCrLf & _ " For column = 1 To r.Columns.Count" & vbCrLf & _ " colors(row - 1, column - 1) = r.Cells(row, column).Interior.Color" & vbCrLf & _ " Next" & vbCrLf & _ " Next" & vbCrLf & _ " GetInteriorColors = colors" & vbCrLf & _ "End Function" End With End With End Sub 

Something noticeable; Sometimes I get a weird error when executing AddFromString . The macro code is added correctly to the module, but sometimes I get an error message; swallowing it does not seem harmful, but if you have the same problem, I would look at it.

Now that you have the macro, returning the result is easy (again, written in VBA):

 Public Function GetColors(r As Range) As Long() //Note the absolute path to the macro; this is probably needed if the macro is in a different workbook. GetColors = Application.Run(ActiveWorkbook.Name & "!GetInteriorColors", r) End Function 

Answer 2

I would try the following (written in VBA, but can be converted to C #):

 Public Sub GetColors() Dim ewsTarget As Worksheet: Set ewsTarget = ActiveWorkbook.Worksheets(1) ewsTarget.Copy , ewsTarget.Parent.Worksheets(ewsTarget.Parent.Worksheets.Count) Dim ewsCopy As Worksheet: Set ewsCopy = ewsTarget.Parent.Worksheets(ewsTarget.Parent.Worksheets.Count) ewsCopy.UsedRange.ClearContents ewsCopy.UsedRange.Columns.EntireColumn.ColumnWidth = 0.5 ewsCopy.UsedRange.Rows.EntireRow.RowHeight = 5# ewsCopy.UsedRange.CopyPicture xlScreen, xlBitmap ewsCopy.Delete End Sub 

This code places the bitmap on the clipboard. This bitmap is created from a copy of the worksheet, however, the contents of the cells are deleted, so you will see only the background of the cell, moreover, the rows and columns have the same height and length. Your C # program can then get this bitmap and get individual pixels, given that the location of the cells can be easily calculated, since the rows and columns have the same height and width.

I know this is only a workaround, but I don't think that was the best solution. This is the only way (you can’t write, read only) and it’s difficult to extend to other properties (perhaps borders and fonts).