Populating a worksheet from an array will not work

[andrewmrichards]

Hi all

I am wanting to write VBA code which will do the following:

1. Take values from a single worksheet column, and read them into an array
2. Convert the array into a string
3. Manipulate the string using RegEx
4. Convert the string back to an array
5. Write the array back to the worksheet column

For the purposes of testing my data is as follows

Data cleansing tools.xlsm
	A
1	Any instance of
2	Cl;Cl.
3	La;La.
4	St;St.
5	Rd;Rd.
6	Dr;Dr.;Drv;Drv.
7	sample
8	Sample
9	Doctor;Dr.
10	Mr.
11	Mrs.
12	Ms.
13	Rev.;Revd.;Revd
Consistency rules

For step 1, I am using the following:

Dim MyArr() As Variant
MyArr = Sheet5.Range("A1:A13")

This works fine.
For step 2, given that MyArr is now a 2-dimensional array (A1 is now in MyArr(1,1), A2 is in MyArr(2,1) and so on), I'm using Nigel Heffernan's "Join2D" function, which I paste below for reference, or can be found at Excellerando.Blogspot.com This also works fine.

For step 3, I manipulate the string as required. No problem here.

For step 4, I then use the following function as a simple way to get the string back into an array of the right shape, given that I will only ever want to read from / write to a single column:

End Sub
Function MySplit2d(InputVar As String, SplitChar As String) As Variant()

      Dim MyArr() As String
      Dim OutputArr() As Variant
      Dim l As Long

10    MyArr = Split(InputVar, SplitChar)

20    ReDim OutputArr(UBound(MyArr) + 1, 1)
30    For l = 1 To UBound(MyArr) + 1
40        OutputArr(l, 1) = MyArr(l - 1)
50    Next l

60    MySplit2d = OutputArr

End Function

This appears to return the array with exactly the right shape and contents.

My testing code is as follows:

Sub TestMy2dSplit()

      Dim MyArr() As Variant, MyNewArr() As Variant
      Dim MyString As String

      Dim i As Integer

10    MyArr = Sheet5.Range("A1:A13")

20    MyString = Join2d(MyArr, Chr(187))
30    MyNewArr = MySplit2d(MyString, Chr(187))

40    For i = LBound(MyArr) To UBound(MyArr)
50        Debug.Print "MyArr(" & i & ") = " & MyArr(i, 1), , "MyNewArr(" & i & ") = " & MyNewArr(i, 1)
60    Next i

70    Sheet5.Range("L1", Sheet5.Range("L1").Cells(UBound(MyNewArr))).Value = MyNewArr()


End Sub

If I change line 70 above to end ... =MyArr() then the range starting in L1 populates with the original data, as expected.
If I look at each element in the MyNewArr() array, it seems to me to be identical to MyArr(). I have tested this with the following loop:

For i = LBound(MyArr) To UBound(MyArr)
    Debug.Print "MyArr(" & i & ") = " & MyArr(i, 1), , "MyNewArr(" & i & ") = " & MyNewArr(i, 1)
Next i

But when I run this code, the range starting in L1 remains completely empty. No errors, no problems, just nothing seems to happen.

Does anyone have any idea of what I'm missing?

Below is the Join2D function as mentioned above.

Many thanks
Andrew

---

Public Function Join2d(ByRef InputArray As Variant, _
                       Optional RowDelimiter As String = vbCr, _
                       Optional FieldDelimiter = vbTab, _
                       Optional SkipBlankRows As Boolean = False _
                       ) As String

' Join up a 2-dimensional array into a string. Works like the standard
'  VBA.Strings.Join, for a 2-dimensional array.
' Note that the default delimiters are those inserted into the string
'  returned by ADODB.Recordset.GetString

On Error Resume Next

' Coding note: we're not doing any string-handling in VBA.Strings -
' allocating, deallocating and (especially!) concatenating are SLOW.
' We're using the VBA Join & Split functions ONLY. The VBA Join,
' Split, & Replace functions are linked directly to fast (by VBA
' standards) functions in the native Windows code. Feel free to
' optimise further by declaring and using the Kernel string functions
' if you want to.

' ** THIS CODE IS IN THE PUBLIC DOMAIN **
'   Nigel Heffernan   Excellerando.Blogspot.com

Dim i As Long
Dim j As Long

Dim i_lBound As Long
Dim i_uBound As Long
Dim j_lBound As Long
Dim j_uBound As Long

Dim arrTemp1() As String
Dim arrTemp2() As String

Dim strBlankRow As String

i_lBound = LBound(InputArray, 1)
i_uBound = UBound(InputArray, 1)

j_lBound = LBound(InputArray, 2)
j_uBound = UBound(InputArray, 2)

ReDim arrTemp1(i_lBound To i_uBound)
ReDim arrTemp2(j_lBound To j_uBound)

For i = i_lBound To i_uBound

    For j = j_lBound To j_uBound
        arrTemp2(j) = InputArray(i, j)
    Next j

    arrTemp1(i) = Join(arrTemp2, FieldDelimiter)

Next i

If SkipBlankRows Then

    If Len(FieldDelimiter) = 1 Then
        strBlankRow = String(j_uBound - j_lBound, FieldDelimiter)
    Else
        For j = j_lBound To j_uBound
            strBlankRow = strBlankRow & FieldDelimiter
        Next j
    End If

    Join2d = Replace(Join(arrTemp1, RowDelimiter), strBlankRow, RowDelimiter, "")
    i = Len(strBlankRow & RowDelimiter)

    If Left(Join2d, i) = strBlankRow & RowDelimiter Then
        Mid$(Join2d, 1, i) = ""
    End If

Else

    Join2d = Join(arrTemp1, RowDelimiter)

End If

Erase arrTemp1

End Function

 

[Fluff]

MyNewArr is a 2d array 0 to 13, 0 to 1, and on this line50 Debug.Print "MyArr(" & i & ") = " & MyArr(i, 1), , "MyNewArr(" & i & ") = " & MyNewArr(i, 1) you are printing the 2nd column of that array, but when you output it to the sheet you are outputting the 1st column. if you use

70    Sheet5.Range("L1", Sheet5.Range("L1").Cells(UBound(MyNewArr))).Resize(, 2).Value = MyNewArr()

you will see that you get the contents of the array in col M

 

[andrewmrichards]

Okay - that makes sense. Thanks for taking the time to come back to me!

Andrew

 

[Fluff]

You're welcome.