Reducing size of FormulaArray

[rdilipk]

I use the formula array property of a range to display 2D variant data into individual cells in Excel. So I have:

Sub AdjustRange(rows as Integer, cols as Integer)
Dim r as Range
set r = Range("A3").Resize(rows, cols)
r.FormulaArray = "=SomeWorkSheetFunctionThatReturnsAMatrix()"
End Sub

AdjustRange(4, 4) does what is expected.

With this in mind, assume you only know cell A3 but you have no idea if a formula array sits there or not. All you know is that when that VBA code is called again the range now needs to be modified to accept a bigger matrix. so if I do:

AdjustRange(6, 6)

This too works as expected.

However if the matrix size ends up shorter -- i.e lesser # of rows or columns, the call to FormulaArray fails with "Unable to set FormulaArray on the range".
Is this the right behavior? Any way to get around this?

My Excel/VBA skills are next to non-existent. Any suggestions is appreciated.

 

[Rick Rothstein]

Try adding this line of code before your Set statement...

Range("A3").CurrentArray.ClearContents

 

[rdilipk]

Awesome! That works like a charm. Only thing I need to make sure that CurrentArray is really valid on that cell before calling it.

 

[shg]

    With Range("A3")
        If .HasArray Then .CurrentArray.ClearContents
    End With