Excel VBA – How to Add Dynamic Array Formula

dynamic-arraysexcelexcel-formulavba

I am adding a formula to a worksheet via VBA which should be:

=UNIQUE(IF(TableA[ColumnA]=A1,TableA[ColumnB],""))

This utilises the new SPILL feature in Excel to give me a list of column B values where the related value in column A matches what is in cell A. I'm also applying the UNIQUE function to remove any multiple blank ("") results.

This works perfectly if I manually type the formula into Excel, however in using VBA to add the formula, Excel is adding @ symbols within the formula, and causing it to show #VALUE!.

The VBA line being used to add the formula is:

=Cells(x,y).Formula = "=UNIQUE(IF(TableA[ColumnA]=A1,TableA[ColumnB],""""))"

The resulting output in Excel is:

=@UNIQUE(IF(TableA[@[ColumnA]]=A1,TableA[ColumnB],""))

What is going on, and what have I missed?

Thanks in advance!

Best Answer

Good question, and I looked it up...

_{In short:}

Use =Cells(x,y).Formula2 instead of =Cells(x,y).Formula

_{Explaination:}

The @ that shows is called the implicit intersection operator. From MS docs:

Implicit intersection logic reduces many values to a single value. Excel did this to force a formula to return a single value, since a cell could only contain a single value. If your formula was returning a single value, then implicit intersection did nothing (even though it was technically being done in the background).

But why does it appear in your newer Excel O365? Well, Range.Formula uses IIE (implicit intersection) thus adding the @ to basically undo your dynamic array functionality. UNIQUE is a new dynamic array function. So, to write this out in code, you should use the Range.Formula2 property (or Range.Formula2R1C1 if you use R1C1 notation). These properties use AE (array evaluation) and is now the default.

Here is an informative doc from MS on the subject which explains the difference between Formula and Formula2 in more detail.
If you want to know more about the implicit intersection operator then have a look at this
I answered another question earlier on that involved implicit intersection with an example on how that actually works here if one finds it interesting.

Related Solutions

VBA Excel – How to Dynamically Create Dynamic Arrays

The compile error you are getting is caused by a duplicate declaration in the current scope.

In other words: this means you are declaring more than one variable with the same name.

Adding an Option Explicit statement on top of you modules requires you to declare each variable you use. It's very helpful when you receive this error because you can quickly scan your code for duplicate declaration of the highlighted line Dim <variable_name>

This is a sample demonstrating why you are getting the error:

Option Explicit

Sub Main()

    Dim c As Worksheet
    For Each c In Sheets
        Dim c As Long   ' you are going to get an error in here because
                        ' a variable named: c, is already declared within the sub
                        ' you can't have two variables named: c.
        For c = 1 To ws.Range("A" & Rows.Count).End(xlUp).Row
            ' some code
        Next c
    Next

End Sub

There is no easy work around your problem. We would have been able to provide a better solution to your problem if you better explain what you are trying to achieve.

There is a workaround to achieve what you want but I wouldn't recommend doing it this way if you are unsure of you are actually doing ;). The below code will create a new module in your current VBA project. While iterating over the array with the animal names it will be writing new lines to Module2 so after the execution your module two will be

enter image description here

In order for this code to work you have to add references to Microsoft Visual Basic for Applications Extensibility 5.3". You can do that by selectingTools>>References` in the VBE window.

Also, this requires you to Trust Access to VBA Project Object Model. Go to Excel Settings >> Trust Centre >> Macros >> tick Trust Access To VBA Project Object Model.

enter image description here

Run the sample code.

Option Explicit

' this VBA project requires
' 1 - references to Microsoft Visual Basic For Applications Extensibility 5.3
'     add it via Tools > References
'
' 2 - trust access to VBA project object model
'     In spreadsheet view go to Excel(application options) >> Trust Centre >> Macro Settings
'     tick the Trust Access to VBA project object model

Sub mymacro()
    Dim names
    names = Array("cat_code", "dog_code", "eagle_code")
    Dim c As Variant
    AddAModule
    For Each c In names
        ' dynamically create arrays
        WriteToModule CStr(c)
    Next
    CloseModule
End Sub


Private Sub AddAModule()
    Dim VBProj As VBIDE.VBProject
    Dim VBComp As VBIDE.vbComponent
    Dim CodeMod As VBIDE.CodeModule

    Set VBProj = ThisWorkbook.VBProject
    Set VBComp = VBProj.VBComponents.Add(vbext_ct_StdModule)
    Set CodeMod = VBComp.CodeModule

    With CodeMod
        .DeleteLines 1, .CountOfLines
        .InsertLines 1, "Public Sub DynamicallyCreatedArrays()"
        .InsertLines 2, "    ' code for the sub"
    End With
End Sub

Private Sub WriteToModule(arrayName As String)
    With ActiveWorkbook.VBProject.VBComponents("Module2").CodeModule
        .InsertLines .CountOfLines + 2, "    Dim " & arrayName & " as Variant"
    End With
End Sub

Private Sub CloseModule()
    With ActiveWorkbook.VBProject.VBComponents("Module2").CodeModule
        .InsertLines .CountOfLines + 2, "End Sub"
    End With
End Sub

Best Answer

Related Solutions

VBA Excel – How to Dynamically Create Dynamic Arrays

Related Question