Creating a Heading in Excel with an Array

[arb82585]

Hello Everyone. I apologize if this is a subject that has been addressed already, I did a search and was not able to find a solution to my problem. This may be a fairly elementary question for all of you experts out there but I am not the strongest programmer.

I am attempting to create a title on an Excel sheet by taking some values from an input page. Basically I want to take 5 values or so from the input page and compile these to create a heading. To do this, I was attempting to create an array from the inputs and use a join function to combine them. However, when I do this I get a "Compile Error. Sub or Function not defined". I have outlined this portion of the code below. If you have any suggestions to fix this problem or see where the error is, I would greatly appreciate the assistance.

Dim Title1 As String

Title1_Array(0) = Worksheets("Inputs").Range("B5")
Title1_Array(1) = " X "
Title1_Array(2) = Worksheets("Inputs").Range("B6")
Title1_Array(3) = " "
Title1_Array(4) = Worksheets("Inputs").ComboBox1.Value
Title1_Array(5) = ", "
Title1_Array(6) = Worksheets("Inputs").Range("B2")
Title1_Array(7) = " X "
Title1_Array(8) = Worksheets("Inputs").Range("B3")
Title1 = Join(Title1_Array)

Worksheets("NG Outputs").Range("A2") = Title1

When the error comes up, it is highlighting Title1_Array(0)

Thank you for taking a look.

Alex

 

[Aaron Blood]

The VBE doesn't know that you intended "Title1_Array" to actually be an array. If not dimensioned, the VBE would assume that's supposed to be a function or subroutine and it doesn't see one with that name. Dimension that variable as an array and you should be fine.

Sub test()
    Dim Title1 As String
    Dim Title1_Array(0 To 8) As String
    
    Title1_Array(0) = 1
    Title1_Array(1) = 2
    Title1_Array(2) = 3
    Title1_Array(3) = 4
    Title1_Array(4) = 5
    Title1_Array(5) = 6
    Title1_Array(6) = 7
    Title1_Array(7) = 8
    Title1_Array(8) = 9
    Title1 = Join(Title1_Array)
    
    MsgBox Title1

End Sub

 

[arb82585]

Thank you very much Aaron, that worked perfectly.

 

[WRL]

Hi Alex,

Not sure if this will help... Try to create a string directly, instead of an array.

    Dim Title1 As String
 
    Title1 = Worksheets("Inputs").Range("B5")
    Title1 = Title1 & " X "
    Title1 = Title1 & Worksheets("Inputs").Range("B6")
    Title1 = Title1 & " "
    Title1 = Title1 & Worksheets("Inputs").ComboBox1.Value
    Title1 = Title1 & ", "
    Title1 = Title1 & Worksheets("Inputs").Range("B2")
    Title1 = Title1 & " X "
    Title1 = Title1 & Worksheets("Inputs").Range("B3")

If you want to shorten this code, you can also use:

    With Worksheets("Inputs")
        Title1 = .Range("B5") & " X" & .Range("B6") & " "
        Title1 = Title1 & .ComboBox1.Value & ", " & .Range("B2") & " X" & .Range("B3")
    End With

Regards