9

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

Improve this question
3
  • Try: CodeRange = Application.Transpose(oSh.Range("packing_list[Code]").Value) Commented Dec 10, 2015 at 13:16
  • I always used a Collection first, and then converted into an array once the # of elements where known. Commented Dec 10, 2015 at 13:48
  • or just MsgBox Join([Transpose(packing_list[Code])], "//") Commented Jun 1, 2017 at 11:48

5 Answers 5

Reset to default
13

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, "//")
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

so the solution would be to conver the "Value" to a string then add it to the array ?
could i use CStr to conver the value of each cell into a string ?
@Rémi No, your variable is not an array variable it can only store a single value.. I will edit my answer to show you what I mean
@Rémi let me know if this solution works for you ^^^
5

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])), ",")
Improve this answer

1 Comment

Had missed Rory's earlier suggestion on the same lines.
0

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.

Improve this answer

Comments

0

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
Improve this answer

1 Comment

As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center.
0 
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
Improve this answer

1 Comment

Although this code might answer the question, I recommend that you also provide an explanation what your code does and how it solves the problem of the question. Answers with an explanation are usually more helpful and of better quality, and are more likely to attract upvotes.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.