How to concatenate string of numbers in VBA

Question

[Updated] Thank you all for your guidance. I have modified the code as below. But the macro did not run, nor generated any errors. I did not want to double quote each item in the variable "columns" either. Thank you very much for any helps. Thanks!

My code is as below:

I would like to generate 35 random columns index if the sample size is 80, and if the sample size is 50 then generate 25 and if the sample size is 32 then generate 20 and if the sample size is 20 then generate 15. And then created a average and sd columns for that.

    `
Sub randomize1()
Dim wb As Workbook
Dim average As Range
Dim sd As Range
Dim columns As String
Dim values As Variant
Set wb = ActiveWorkbook

With wb
For i = 2 To 1730
Set average = Worksheets("CT").Range("CY2:CY1730")
Set sd = Worksheets("CT").Range("CZ2:CZ1730")

If WorksheetFunction.CountA(".Cells(i,11):.Cells(i,91)") = 80 Then
columns = "17 21 31 32 2 18 22 7 9 20 23 6 27 10 26 8 29 3 1 13 5 24 35 15 28 11 25 14 16 4 12 34 19 30 33"
values = Strings.Split(columns, " ")
.Cells(i, average).Value = Application.average(Range(i, columns).Offset(, 10).Select)
.Cells(i, sd).Value = Application.WorksheetFunction.StDev(Range(i, columns).Offset(, 10).Select)
ElseIf WorksheetFunction.CountA(".Cells(i,11):.Cells(i,91)") = 50 Then
columns = "1 22 17 5 18 8 20 9 10 6 25 14 13 7 2 3 19 16 4 12 15 11 24 23 21"
 values = Strings.Split(columns, " ")
.Cells(i, average).Value = Application.average(Range(i, columns).Offset(, 10).Select)
.Cells(i, sd).Value = Application.WorksheetFunction.StDev(Range(i, columns).Offset(, 10).Select)
ElseIf WorksheetFunction.CountA(".Cells(i,11):.Cells(i,91)") = 32 Then
columns = "14 2 3 16 19 11 20 1 13 18 6 9 17 8 4 5 10 15 12 7"
values = Strings.Split(columns, " ")
.Cells(i, average).Value = Application.average(Range(i, columns).Offset(, 10).Select)
.Cells(i, sd).Value = Application.WorksheetFunction.StDev(Range(i, columns).Offset(, 10).Select)
ElseIf WorksheetFunction.CountA(".Cells(i,11):.Cells(i,91)") = 20 Then
columns = "13 8 7 2 1 12 11 6 14 15 3 10 4 5 9"
values = Strings.Split(columns, " ")
.Cells(i, average).Value = Application.average(Range(i, columns).Offset(, 10).Select)
.Cells(i, sd).Value = Application.WorksheetFunction.StDev(Range(i, columns).Offset(, 10).Select)
End If
Next i
End With
End Sub

``

Answer 1

Dim columns As String
columns = (17 21 31 32 2 18 22 7 9 20 23 6 27 10 26 8 29 3 1 13 5 24 35 15 28 11 25 14 16 4 12 34 19 30 33)`

The expression (17 21 31 ...) isn't a String literal. In VBA (as in many other languages), string literals are delimited with " double quotes, not parentheses.

The compile error is because the expression (integerLiteral integerLiteral integerLiteral ...) can't be evaluated, it means nothing to the compiler. I'm guessing it's choking at the 21 token, especting a , list separator or a ) closing parenthesis, since columns = (17) would be a valid expression.

This would compile:

Dim columns As String
columns = "17 21 31 32 2 18 22 7 9 20 23 6 27 10 26 8 29 3 1 13 5 24 35 15 28 11 25 14 16 4 12 34 19 30 33"

...but then, it's not an array of values, it's just a string. There's a Split function in the VBA.Strings module that you can use to split a string into an array, given a separator - like, a space character:

Dim values As Variant
values = Strings.Split(columns, " ")

That gives you an array of Variant/String items that you can later iterate and convert to Long values if you need to.

in R, I can use c(1,2,3) if I am going to define an object,column index as (1,2,3)

That statement is extremely unclear and hard to understand for an audience of VBA experts - how does (1,2,3) map to object, column index? Is 1 an "object"? Is 2,3 a column index? Or did you mean object, column, index? Doesn't make much sense, even to someone that knows what a value tuple is. Sounds like R lets you define value tuples inline. That's cool, but there's no concept of a value tuple in VBA, and no inline objects.

If you need an object, you add a class module to your project, and define it - at a bare minimum, using public fields to define its public interface:

'Class1
Option Explicit
Public Value As Long
Public Column As Long
Public Index As Long

Then you can create new instances of this class using New: Set foo = New Class1, and then assign and read its properties, invoke its methods: Debug.Print foo.Index, foo.Column, foo.Value.

---

About this:

columns = (num & num& ... &num)

Note that some tokens have multiple syntactical purposes; & is one such token. When surrounded with spaces as in num & num, it's the string concatenation operator.

But without a leading space, as in num&, it's a type hint character that tells the compiler num is a Long integer, hence the syntax error; num& num is just as illegal as (42 17).

Answer 2

In VBA, everything between quotes "" is a String.

If you simply need to create a string containing those numbers, you can do this:

Dim columns As String
columns = "17 21 31 32 2 18 22 7 9 20 23 6 27 10 26 8 29 3 1 13 5 24 35 15 28 11 25 14 16 4 12 34 19 30 33"

If you need to process those numbers individually, you need an array, which you can initialize following multiple ways.

Classic Way

Declare your Array with a fixed size and then assingn it the values you want:

Dim myArray(1 to 34) As String 'Goes from 1 to 34
myArray(1) = 17  'First element
myArray(2) = 21  'Second element
'etc...

---

Use Array() Command

You can use the useful command Array() to initialize an Array with an inline command:

Dim myArray() As Variant
myArray = Array("17", "21", "31", "32", "2")

Note that your variable must be Variant to use this solution.

Hope this helps.