none
Idiotic VBA Error when attempting copy a FormulaArray in VBA Code RRS feed

  • Question

  • I'm 99.999% certain the Run-time error '1004'error I am experiencing in the ResetArrayFormula subroutine (which gets called in the CopyFormulaArray subroutine) is due to some stupid bug within VBA (the one of many!) however I thought I would list the code I am using and see if someone can spot what would cause this error. Before attempting to go through the code please note I am using the ResetArrayFormula subroutine to copy numerous formulas within my worksheet and I am ONLY experiencing a Run-time error '1004' for the specific formula below in the CopyFormulaArray subroutine (all other formulaArrays  I am copying work fine, I have not listed the working ones here). Furthermore, if I take the FormulaArray generated in the formulaArrayToCopy string in the CopyFormulaArray subroutine below (I can do this by going to the immediate window within VBA and copy the output generated) and paste the FormulaArray manually in the cell array I am trying to copy to the FormulaArray to, it copies FINE (which why I am fairly certain this some buggy issue within VBA and not my code).                        

    Sub ResetArrayFormula(rangeName As String, rowCount As Integer, formula As String, bufferEntries As Integer, Optional includeBufferInFormula As Boolean = False, Optional copyCurrentFormulaPerRow As Boolean = False)

        If copyCurrentFormulaPerRow Then
            formula = Range(rangeName).Cells(1, 1).formula
        End If

        'formula = Range(rangeName).FormulaArray
        'Range(rangeName).formulaArray = Null
        Range(rangeName).ClearContents

        If includeBufferInFormula Then
            ActiveWorkbook.Names.Item(rangeName).RefersTo = Range(rangeName).Resize(rowCount + bufferEntries)
        Else
            ActiveWorkbook.Names.Item(rangeName).RefersTo = Range(rangeName).Resize(rowCount)
        End If


        If copyCurrentFormulaPerRow Then
            Range(rangeName).Cells(1, 1).formula = formula
            Range(rangeName).Cells(1, 1).Copy
            Range(rangeName).PasteSpecial xlPasteFormulas
        Else
            Range(rangeName).FormulaArray = formula
        End If

        If Not includeBufferInFormula Then
            ActiveWorkbook.Names.Item(rangeName).RefersTo = Range(rangeName).Resize(rowCount + bufferEntries)
        End If
    End Sub

    Sub CopyFormulaArray()
        Dim rowCount As Integer
        Dim formulaArrayToCopy As String

        If [TableAR156].ListObject.DataBodyRange Is Nothing Then
            rowCount = 1
        Else
            rowCount = [TableAR156].ListObject.DataBodyRange.Rows.Count
        End If

        formulaArrayToCopy = "=SUM(rngMarket_Value_Port)*rngMarket_Weight_Benchmark*IF(rngPortfolio=portCUDG,IF(rngShort_Maturity_Date<=portCUDGCufoff,portCUDGCutoffWeightBefore/SUMIFS(rngMarket_Weight_Benchmark,rngShort_Maturity_Date,""<=""&portCUDGCufoff),portCUDGCutoffWeightBefore/SUMIFS(rngMarket_Weight_Benchmark,rngShort_Maturity_Date,"">""&portCUDGCufoff)),1/SUM(rngMarket_Weight_Benchmark))"

        Call ResetArrayFormula("rngMarket_Value_Benchmark", rowCount, formulaArrayToCopy, 50, True)
    End Sub

    In the formulaArrayToCopy string (which contains the formulaArray I am trying to copy) a few things to note

    -rngMarket_Value_Port, rngMarket_Weight_Benchmark , and rngShort_Maturity_Date  are cell arrays that are the same size as rngMarket_Value_Benchmark (i am resizing rngMarket_Value_Benchmark within the ResetArrayToCopyFormula function but I only attempt to copy the formula once it is resized and at that point it is the same size as rngMarket_Value_Port, rngMarket_Weight_Benchmark , and rngShort_Maturity_Date)

    -rngPortfolio, portCUDG, portCUDGCufoff, portCUDGCutoffWeightBefore, portCUDGCutoffWeightBefore are single cells each

    Sunday, March 31, 2019 12:28 AM