VBA UDF Variant/Integer and Variant/String Arrays are printing only the first value to output cells

Question

The following works great (thanks to gracious assistance by this community!)

    Function RangeToArrayToRange(inputRange as Range) As Variant
            Dim inputArray As Variant
            inputArray = inputRange
            RangeToArrayToRange = inputArray
    End Function

This function will copy an input range to an output perfectly. However, when I do some operations on the inputArray, the arrays look perfect but in Excel, only the first value of the array prints to all the cells. In this example, I'm parsing out a number from some input strings.

Input Range:

ABC=1X:2Y 
ABCD=10X:20Y
ABCDE=100X:200Y

Code:

    Function RangeToArrayToRange(inputRange As Range) As Variant

        Dim inputHeight As Integer
        inputHeight = inputRange.Count

        Dim inputArray As Variant
        inputArray = inputRange

        Dim strippedArray() As Variant
        ReDim strippedArray(1 To inputHeight)

        Dim currentInput As String
        Dim currentInputAsInt As Integer
        Dim i As Integer

        For i = 1 To inputHeight

            currentInput = inputArray(i, 1)
            currentInput = Right(currentInput, (Len(currentInput) - Application.WorksheetFunction.Find("=", currentInput))) 
            'splits out everything left of the "="
            currentInput = Right(currentInput, (Len(currentInput) - Application.WorksheetFunction.Find(":", currentInput)))
            'splits out everything to the right of the ":"
            currentInput = Left(currentInput, Len(currentInput) - 1) 
            'split out the letter to allow int casting
            currentInputAsInt = CInt(currentInput)
            'cast to int
            strippedArray(i) = currentInputAsInt
            'saved

        Next i

        RangeToArrayToRange = strippedArray
    End Function

Expected output:

1
10
100

Actual output:

1
1
1

Running through with the debugger, strippedArray contains the Variant/Integer values 1,10,100 at the locations strippedArray(1)/(2)/(3) respectively. The issue is that the range that I array enter in Excel only contains strippedArray(1) as far as I can tell.

Thank you!

So you are saying that the array is correctly filled in but only the first value prints to all cells? But where is the code where you print? I only see a function that returns an array... — html_programmer

markblandford markblandford · Accepted Answer · 2012-06-28T14:46:44

Your strippedArray array, needs to be two dimensional if you are outputting back into an Excel worksheet / range (I've made the assumption you are running this as an array formula). Make the following changes:

ReDim strippedArray(1 To inputHeight, 1 To 1)
...
strippedArray(i, 1) = currentInputAsInt

VBA UDF Variant/Integer and Variant/String Arrays are printing only the first value to output cells

1 Answers