Creating a Heading in Excel with an Array

arb82585

New Member
Joined
Oct 22, 2010
Messages
2
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
 

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

Aaron Blood

Active Member
Joined
Oct 10, 2002
Messages
485
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.


Code:
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
 
Last edited:

WRL

Board Regular
Joined
Apr 8, 2008
Messages
51
Hi Alex,

Not sure if this will help... Try to create a string directly, instead of an array.
Code:
    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:
Code:
    With Worksheets("Inputs")
        Title1 = .Range("B5") & " X" & .Range("B6") & " "
        Title1 = Title1 & .ComboBox1.Value & ", " & .Range("B2") & " X" & .Range("B3")
    End With
Regards
 

Watch MrExcel Video

Forum statistics

Threads
1,095,362
Messages
5,444,022
Members
405,260
Latest member
Khauff

This Week's Hot Topics

Top