.net Excel Range Address limitation

Question

I want to define multiple areas in one single Excel.Range object. The purpose is to colorize multiple different areas by setting one single Range. This should save time using the Excel interop, which is very slow in such operations. The problem is, that I get an error (HRESULT: 0x800A03EC) when I try to put a "big" address line into the Range. Could somebody tell me if there is a limitation using Excel interop and does anybody have a solution for colorizing lots of areas at once / in a fast manner?

The "big" address line in the example is just to show you where the problem is. I know it does not make a lot of sense to put A1:A2 multiple times into the address.

Dim objExcelApp As New Excel.Application

objExcelApp.Visible = True

Dim objExcelWorkbooks As Excel.Workbooks = objExcelApp.Workbooks

Dim objExcelWB As Excel.Workbook = objExcelWorkbooks.Add

Dim objExcelWS As Excel.Worksheet = objExcelWB.Worksheets(1)

Dim rng As Excel.Range
rng = objExcelWS.Range("A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2")

Are you using an XLS or XLSX? XLS (excel 2003) will only work with up to 64,000 rows. You could have an 2003 worksheet inside a XLSX worksheet (2007+) and have issue due to 2003 not supporting over 64000 rows (and 256 columns). — jdweng, Dec 13 '19 at 19:47
Thanks for your reply, it is with excel 2010 and xlsx format. — Bastian, Dec 13 '19 at 20:08
Instead of multiple "A1:A2", could you replace that with something more representative of how your chosen cells are spaced? Someone may have ideas based on the patterns. — AJD, Dec 13 '19 at 20:47
https://stackoverflow.com/q/7099770/9101981 might have some useful input. — AJD, Dec 13 '19 at 20:49
It looks like `Range("A1:A2")` is fine, but `Range("A1:A2;A1:A2")` is not. Your example fails not because of the number of ranges, rather the duplication. Well, probably both, but the duplication first. It takes focus away from the actual problem. — djv, Dec 13 '19 at 21:39
You are hitting the Excel 255 character limit for a formula. Also, AFAIK, you should be using a comma (**,**) as the separator character instead of a semicolon (**;**). — TnTinMn, Dec 13 '19 at 22:44
Can't you simply break up the long text range into smaller units and then `Union()` them? Such as is being done here: `rng = objExcelApp.Union(rng, objExcelWS.Range(.........))` — K.Dᴀᴠɪs, Dec 13 '19 at 23:51

AJD · Answer 1 · 2019-12-13T21:17:13.787

0

The most likely cause of your issue is that you are trying to create an array of values from a complex multi-area range.

(Edit: I have removed the extra information based on VBA, not VB.Net, which is not relevant to the Question).

The following StackOverflow Q&A also addresses other causes and solutions: HRESULT: 0x800A03EC on Worksheet.range

edited Dec 13 '19 at 21:17

answered Dec 13 '19 at 20:58

AJD

2,400
2
12
22

Bastian · Accepted Answer · 2019-12-14T11:38:42.170

I found a really fast possibilty in filling a multi-area range with more content in using the Union function in combination with the approach of splitting the address into chunks of 255 char strings. This function does the job very well. This code is with semicolon, because its a country specific seperator it seems (comma is not working in my case, maybe you can modify it). Thanks @K.Dᴀᴠɪs for the hint:

Private Function CombineAddressToRange(ByVal Address As String, ByVal objExcelWorksheet As Excel.Worksheet, ByVal objExcelApp As Excel.Application) As Excel.Range

        Dim SplitAddress As String()
        Dim TempAddress As String = ""
        Dim FinalRange As Excel.Range

        SplitAddress = Address.Split(";")

        'Initialize Range
        FinalRange = objExcelWorksheet.Range(SplitAddress(0))

        If UBound(SplitAddress) >= 1 Then
            For i = 1 To UBound(SplitAddress)

                If Len(TempAddress) + 1 + Len(SplitAddress(i)) > 255 Then
                    FinalRange = objExcelApp.Union(FinalRange, objExcelWorksheet.Range(TempAddress))
                    TempAddress = SplitAddress(i)
                Else
                    If TempAddress = "" Then
                        TempAddress = SplitAddress(i)
                    Else
                        TempAddress = TempAddress & ";" & SplitAddress(i)
                    End If

                End If

            Next

            If TempAddress <> "" Then
                FinalRange = objExcelApp.Union(FinalRange, objExcelWorksheet.Range(TempAddress))
            End If

        End If

        Return FinalRange

    End Function

.net Excel Range Address limitation

2 Answers2