I'm very new to coding with VBA. I'm using Excel's VBA in a VERY entry-level engineering computations course, and I've found myself stuck on a problem using loops and arrays. I'll try to be as detailed as I can.

The goal is to use a single function and a "For" or "Do" loop to calculate the squared difference between two sets of 10 values at one time. I figured that a "For-Next" loop would work best. The preexisting sets of data / arrays are in two rows, as shown:

Essentially, we're solving for (A1-B1)^2 in cell A3, (A2-B2)^2 in cell B3, etc.

The code that I was able to work out works but is broken. It will only display the correct function value for the final column's data, since (9-1)^2 = 64, as shown:

Additionally, here is the broken code:

    Option Base 1
    Public Function SqDiff(arrayA As Range, arrayB As Range) As Variant
    Dim ncell As Integer
    Dim i As Integer
    Dim A As Single
    Dim B As Single
    Dim SquareDifference As Single
    For i = 1 To 10 Step 1
        A = arrayA(i)
        B = arrayB(i)
        SquareDifference = (A - B) ^ 2
        SqDiff = SquareDifference
    Next i
    End Function

I'm sure it's a really simple mistake that I didn't catch. Either way, I'm grateful for any input. Thank you!

CodePudding user response：

Since you are using the UDF as an array formula, you need to return SqDiff as an array as well:

Public Function SqDiff(arrayA As Range, arrayB As Range) As Variant
    Dim i As Long
    Dim A As Single
    Dim B As Single
    Dim SquareDifference As Single

    'Make sure that the input ranges are of 1 row size and same amount of cells
    If arrayA.Rows.Count = 1 And arrayB.Rows.Count = 1 And arrayA.Cells.Count = arrayB.Cells.Count Then
        'Assign the ranges' value into an array for faster processing
        Dim arrA As Variant
        arrA = arrayA.Value
        
        Dim arrB As Variant
        arrB = arrayB.Value
        
        'Create a temp array of the same size as the input size, to assign to SqDiff later
        Dim output() As Variant
        ReDim output(1 To 1, 1 To UBound(arrA, 2)) As Variant
        
        For i = 1 To UBound(arrA, 2)
            A = arrA(1, i)
            B = arrB(1, i)
            SquareDifference = (A - B) ^ 2
            output(1, i) = SquareDifference
        Next i
        
        SqDiff = output
    End If
End Function

CodePudding user response：

UDF - Array Formula

• If you don't have Office 365, you need to enter the formula as an array formula
  Ctrl,Shift Enter. You select the range, but enter the formula only into the first cell.
• Note that all three ranges have to be of the same size.

Option Explicit

Public Function SqDiff( _
    ByVal RangeA As Range, _
    ByVal RangeB As Range) _
As Double()

    Dim rCount As Long: rCount = RangeA.Rows.Count
    If rCount <> RangeB.Rows.Count Then Exit Function
    Dim cCount As Long: cCount = RangeA.Columns.Count
    If cCount <> RangeB.Columns.Count Then Exit Function

    Dim aData As Variant, bData As Variant
    If rCount   cCount = 2 Then ' one cell
        ReDim aData(1 To 1, 1 To 1): aData(1, 1) = RangeA.Value
        ReDim bData(1 To 1, 1 To 1): bData(1, 1) = RangeB.Value
    Else ' multiple cells
        aData = RangeA.Value
        bData = RangeB.Value
    End If
    
    Dim Data() As Double: ReDim Data(1 To rCount, 1 To cCount)
    
    Dim r As Long, c As Long
    
    For r = 1 To rCount
        For c = 1 To cCount
            If IsNumeric(aData(r, c)) Then
                If IsNumeric(bData(r, c)) Then
                    ' Choose/modify the operation.
                    Data(r, c) = (aData(r, c) - bData(r, c)) ^ 2
                End If
            End If
        Next c
    Next r
        
    SqDiff = Data

End Function