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

 

[Jeffrey Mahoney]

First thought: if you use transpose you can get a 1D array from a range
MyArr = WorksheetFunction.Transpose(Sheet5.Range("A1:A13"))

 

[Jeffrey Mahoney]

Then you should be able to join those with the native VBA Excel function JOIN

 

[andrewmrichards]

First thought: if you use transpose you can get a 1D array from a range
MyArr = WorksheetFunction.Transpose(Sheet5.Range("A1:A13"))

Thanks Jeffrey. I looked at that as an early draft of this, but the problem comes in that I want to be able to apply this to ranges which may run to thousands or 10s of thousands of rows, and I believe there's a limit of 255 cells for the Transpose function.

 

[Fluff]

I think that is the character limit per cell, you can use Transpose on ranges upto (at least) 50,000 rows.

 

[Jeffrey Mahoney]

It does not have that limit.

 

[andrewmrichards]

How strange.... I must have read that somewhere.... Or did I dream it?

I can have a go with that to create the original array - but as per my original post, the issue is not with creating the original array, nor with converting that to a string with Join, but in outputting the final array back to Excel.

If anyone can suggest why that might be causing me problems, I'd be grateful.

Andrew

 

[andrewmrichards]

Okay - so taking the transpose function as my starting point, I've adjusted my code as below, transposing to read into the array, then transposing back out again.:

Sub TestMy2dSplit()

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

      Dim i As Integer

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

20    MyString = Join(MyArr, Chr(187))

' Do work here

30    MyNewArr = Split(MyString, Chr(187))

60    Sheet5.Range("L1", Sheet5.Range("L1").Cells(UBound(MyNewArr) + 1)).Value = WorksheetFunction.Transpose(MyNewArr())


End Sub

This does now work... so many thanks for the suggestion.

But I'm still intrigued by why my original array would not work when it appeared identical to one which would...

Thanks again
Andrew

 

[Fluff]

It's because having converted the 2d array into a 1d array, you are then turning it back to a 2d array with columns on this line ReDim OutputArr(UBound(MyArr) + 1, 1)and you are then filling the 2nd column, so the first column is empty.

 

[andrewmrichards]

It's because having converted the 2d array into a 1d array, you are then turning it back to a 2d array with columns on this line ReDim OutputArr(UBound(MyArr) + 1, 1)and you are then filling the 2nd column, so the first column is empty.

Thanks for your help. I've solved the issue by using the Transpose function as suggested by Jeffrey, so I've marked that as the solution.

However, I'm still not getting exactly why the original code didn't work. I agree it's to do with my going from a 2d array to a 1d array and then back to a 2d array, but using my test loop (as per my original post) I can see that the structure and content of my newly created 2d array is exactly the same as the original array populated by reading the content out of the cells.

I'll perhaps re-visit on another day when I can come at it with a clear head (and more coffee).

Thanks for your help.