CODEWITHSUNDEEP
excelvba
0xtuytuy
0xtuytuy

Reputation: 1654

Convert Variable Array to String

I am trying to convert a variable array to a string using vba. I have tried 2 methods but non of them work, they both seem to bloc on at the same point.

    Dim cell As Range
    Dim val As Variant

    For Each cell In Range("packing_list[Code]")
        val = cell.Value
    Next cell

    MsgBox Join(val, "//")

and

    Dim oSh As Worksheet
    Dim CodeRange As Variant

    Set oSh = ActiveSheet
    CodeRange = oSh.Range("packing_list[Code]").Value

    MsgBox Join(CodeRange , "//")

They both error on the MsgBox line. What do I do wrong ?

Thanks

Upvotes: 9

Views: 54139

Answers (5)

OldMan
OldMan

Reputation: 1

Public Function StringNum(Rng As Range) As String
Dim tmpStr As String


tmpStr = ""

For Each cell In Rng

tmpStr = tmpStr & cell.Value & ","

Next

StringNum = Mid(tmpStr, 1, Len(tmpStr) - 1)



End Function

Upvotes: 0

James Taylor
James Taylor

Reputation: 1

I appologize for the first rendition of this post, I just pasted the Function directly from Excel's VBA project window. I did not realize that the formatting would be lost. I also wasn't aware that I could create code fences with backticks ` or tildes ~

I modified the Function to include the table Field name(s) and the contents of the Criteria1 Array when the type is ".Operator = xlFilterValues". Now instead of getting:

[:] AND [:=1]

I get [Surname:=Gedye,=Sole,=Williams] AND [Active:=1]

Public Function AutoFilterCriteria(ByVal WholeTable As Range) As String

On Error Resume Next

If WholeTable.Parent.AutoFilter Is Nothing Then                     ' if no filter is applied
    AutoFilterCriteria = "None"
    On Error GoTo 0
    Exit Function
End If

Dim LongStr As String, FirstOne As Boolean
LongStr = ""
FirstOne = False

Dim iFilt As Integer
For iFilt = 1 To WholeTable.Parent.AutoFilter.Filters.Count         ' loop through each column of the table
    Dim ThisFilt As Filter
    Set ThisFilt = WholeTable.Parent.AutoFilter.Filters(iFilt)      ' look at each filter
    On Error Resume Next
    With ThisFilt
        If .On Then
            If FirstOne Then LongStr = LongStr & " AND "            ' Get column title
'The line below was modified from the original
            LongStr = LongStr & "[" & Range("MainTable[#Headers]")(1, iFilt)
            LongStr = LongStr & WholeTable.Parent.Cells(WholeTable.Row - 1, WholeTable.Column + iFilt - 1).Value & ":"
            On Error GoTo Handle
            If .Operator = xlFilterValues Then                      ' dont really care to enumerate multiples, just show "multiple"
'The line below was modified from the original
                LongStr = LongStr & Join(.Criteria1, ",") & "]"
            ElseIf .Operator = 0 Then
                LongStr = LongStr & .Criteria1 & "]"
            ElseIf .Operator = xlAnd Then
                LongStr = LongStr & .Criteria1 & " AND " & .Criteria2 & "]"
            ElseIf .Operator = xlOr Then
                LongStr = LongStr & .Criteria1 & " OR " & .Criteria2 & "]"
            End If
            On Error GoTo 0
            FirstOne = True
        End If
    End With
Next

AutoFilterCriteria = LongStr
On Error GoTo 0
Exit Function

Handle:
AutoFilterCriteria = "! Error !"
On Error GoTo 0

End Function

Upvotes: 0

brettdj
brettdj

Reputation: 55682

Tranpose can be used to produce a 1D array or strings for an individual column or row.

So for A1:A10 you could used just

MsgBox Join(Application.Transpose([a1:a10]), ",")

to work on a row you need a second Transpose, so for A1:K1

MsgBox Join(Application.Transpose(Application.Transpose([a1:k1])), ",")

Upvotes: 5

Simon Wray
Simon Wray

Reputation: 192

It looks like you think your val and CodeRange variables are Arrays, when in fact they are not. You have declared them as Variants, but not Variant Arrays, which I suspect is you goal. Add brackets to declare a variable as an Array: Dim CodeRange() as Variant

See this: How do I declare an array variable in VBA?

As @Brandon Keck says, Join is expecting an Array.

Upvotes: 0

Branden Keck
Branden Keck

Reputation: 572

The value you are trying to join is not an array of strings. Join is supposed to be used on arrays

Here is the link to the Microsoft instructions: https://msdn.microsoft.com/en-us/library/b65z3h4h%28v=vs.90%29.aspx

Their example is:

Dim TestItem() As String = {"Pickle", "Pineapple", "Papaya"}
Dim TestShoppingList As String = Join(TestItem, ", ")

You code should look something like:

Dim i As Integer
Dim cell As Range
Dim val() As Variant '() indicate it is an array

i = 0
For Each cell In Range("packing_list[Code]")
    ReDim Preserve val(0 to i) As Variant 'must resize array to fit number of items
    val(i) = cell.Value 'i is the position of the item in the array
    i = i + 1 'increment i to move to next position
Next cell

'Now that you have an array of values (i.e. ("String1", "String2", ...) instead of just "String" you can:

MsgBox Join(val, "//")

Upvotes: 13

Related Questions