Excel VBA project- Sub or Function not defined

[rachaelelianna]

Hi everyone! I would really appreciate any help I can get in solving why my code keeps telling me I have a "sub or function not defined" message every time I try to run it. I am trying to enable my start function button and for some reason, it keeps giving me the error and I have double checked all my spelling. The error is supposed to reference my data sheet on the Excel form and all my data is there so I'm not sure if I'm missing something. Here's a screenshot of what is wrong:

 

[RoryA]

Welcome to the forum.

Where did you declare your arrays?

 

[rachaelelianna]

Hi! Im really new to this but I created my arrays up at the General Declarations private sub. Here's my general declarations code:

Option Explicit
Private Sub Declarations()
'Force variable declaration
   

'Create Arrays (closet dividers) for Each data worksheet column- replace ColumnNumber with a name for the content in the
'column- this may be the same as your question content name (unless it is a translation question).
'NOTE: Arrays and variables for whole numbers should be Long NOT Integer

        Dim ID(2 To 163) As String
        Dim Style(2 To 163) As String
        Dim Size(2 To 163) As String
        Dim Silhouettes(2 To 163) As String
        Dim Sleeves(2 To 163) As String
        Dim Vneck(2 To 163) As String
        Dim Picture(2 To 163) As String

'Create a variable (shoe box) for EACH ONE of the arrays above AND a variable for EACH question
'answer that you will save (these may be the same variable in most cases)

        Dim strID As String
        Dim strStyle As String
        Dim strSize As String
        Dim strSilhouettes As String
        Dim strSleeves As String
        Dim strVneck As String
        Dim strPicture As String

'Create additional variables for each Question Control where the content of the question is different (Translation Question)
'from what is actualy in the related column on your data sheet (in other words, your variable below would agree with your label and
'control on your DSS page, while the Array and Variable above agree with your data page.

'Create an additional variable for your ListBox
        Dim strChoices As String

'Create variable for current workbook path
        Dim strPath As String
'Create a variable of tyoe Variant to hold the user's first name that will take any input
        Dim varUserFirstName As Variant
End Sub

 

[RoryA]

If you declare them inside that routine, they are only available to that routine. From the look of it, there is no need for any of those to be inside a routine, especially when that routine doesn’t do anything.

 

[rachaelelianna]

So, this is for my General Declarations. The error I am getting is in my Start button. This is the code for the Start button and the error I get for one of my columns is in the screenshot I attached at my first question

Private Sub cmdStart_Click()
'Load Arrays with Data from Worksheet.
    Dim X As Integer
    For X = 2 To 163
        ID(X) = Worksheets("Data").Range("A" & X).Value                                        
        Style(X) = Worksheets("Data").Range("B" & X).Value
        Size(X) = Worksheets("Data").Range("C" & X).Value
        Silhouettes(X) = Worksheets("Data").Range("D" & X).Value
        Sleeves(X) = Worksheets("Data").Range("E" & X).Value
        Vneck(X) = Worksheets("Data").Range("F" & X).Value
        Picture(X) = Worksheets("Data").Range("G" & X).Value
    Next
'Clear all controls that are used for questions/displaying answers- Replace Question/ Number with the 'name of the control- this may
'be the same as your array name. The following are examples of how to 'clear the different types of controls- you will need to
'add/delete based on the controls on your DSS sheet

'Clear Combo Box(es)
'Note: In the event a combo box has values in it when the workbook is saved, the first command clears that text value. The second
'line clears the options/ choices of the combo box
        cbostyle.Text = ""
        cbostyle.Clear
        cbosize.Text = ""
        cbosize.Clear
        cboSilhouettes.Text = ""
        cboSilhouettes.Clear
        cboSleeves.Text = ""
        cboSleeves.Clear
'To clear option buttons or check boxes, set the values to False
        optVneck = False
        optvnecktwo = False
'Text boxes, image controls, and labels are cleared by putting "" as shown below
        txtDetails.Text = ""
        imgDetails.Picture = LoadPicture("")
        lblFirstName.Caption = ""
        
'Disable all controls that are used for questions or answers by setting the enabled property to False
        cbostyle.Enabled = False
        cbosize.Enabled = False
        cboSilhouettes.Enabled = False
        cboSleeves.Enabled = False
        optVneck.Enabled = False
        optvnecktwo.Enabled = False
        txtDetails.Enabled = False
        imgDetails.Enabled = False
'Enable List Box, clear it, then resize it, then disable it
        lstChoices.Enabled = True
        lstChoices.Clear
        lstChoices.Height = 50
        lstChoices.Width = 75
        lstChoices.Enabled = False
'OPTION A: Setup First Question (if a combobox)
        cbostyle.Enabled = True
'Note: Add a clear command EACH time you AddItems to a combo or list box
        cbostyle.Clear
        cbostyle.AddItem "Romantic"
        cbostyle.AddItem "Casual"
        cbostyle.AddItem = "Simple"
'Get User's First Name- Note: No error checking except for Null/Empty
'Remember to Create the Label on the DSS page!!
        lblFirstName.Enabled = True
        varUserFirstName = InputBox("What is your FIRST name?")
        If varUserFirstName <> "" Then lblFirstName.Caption = ("Hello beautiful" & varUserFirstName & "!")

End Sub

 

[RoryA]

My point is that you do not need a routine that does nothing other than house a bunch of variable declarations (and it is actually the cause of your problems). Those declarations should probably be at the top of your module, outside any routine (assuming they are used by more than one routine).

 

[rachaelelianna]

So, how do I get rid of those routines? I apologize if I am not that knowledgeable about this as I am taking a class for this and we were given a pdf to copy and paste all of this into our code and were not explained on how to do it properly. But I have to make sure my Start function runs but can't get it to work because of this.

 

[RoryA]

The code in post 3 should just be:

Option Explicit
'Force variable declaration
  
'Create Arrays (closet dividers) for Each data worksheet column- replace ColumnNumber with a name for the content in the
'column- this may be the same as your question content name (unless it is a translation question).
'NOTE: Arrays and variables for whole numbers should be Long NOT Integer

        Dim ID(2 To 163) As String
        Dim Style(2 To 163) As String
        Dim Size(2 To 163) As String
        Dim Silhouettes(2 To 163) As String
        Dim Sleeves(2 To 163) As String
        Dim Vneck(2 To 163) As String
        Dim Picture(2 To 163) As String

'Create a variable (shoe box) for EACH ONE of the arrays above AND a variable for EACH question
'answer that you will save (these may be the same variable in most cases)

        Dim strID As String
        Dim strStyle As String
        Dim strSize As String
        Dim strSilhouettes As String
        Dim strSleeves As String
        Dim strVneck As String
        Dim strPicture As String

'Create additional variables for each Question Control where the content of the question is different (Translation Question)
'from what is actualy in the related column on your data sheet (in other words, your variable below would agree with your label and
'control on your DSS page, while the Array and Variable above agree with your data page.

'Create an additional variable for your ListBox
        Dim strChoices As String

'Create variable for current workbook path
        Dim strPath As String
'Create a variable of tyoe Variant to hold the user's first name that will take any input
        Dim varUserFirstName As Variant

 

[rachaelelianna]

Thank you!! I have one more issue: I get this message when I want to press my start button and I'm not sure what the issue is. Here is the full code:

Private Sub cmdStart_Click()
'Load Arrays with Data from Worksheet.
    Dim X As Integer
    For X = 2 To 163
        ID(X) = Worksheets("Data").Range("A" & X).Value
        Style(X) = Worksheets("Data").Range("B" & X).Value
        Size(X) = Worksheets("Data").Range("C" & X).Value
        Silhouettes(X) = Worksheets("Data").Range("D" & X).Value
        Sleeves(X) = Worksheets("Data").Range("E" & X).Value
        Vneck(X) = Worksheets("Data").Range("F" & X).Value
        Picture(X) = Worksheets("Data").Range("G" & X).Value
    Next
'Clear all controls that are used for questions/displaying answers- Replace Question/ Number with the 'name of the control- this may
'be the same as your array name. The following are examples of how to 'clear the different types of controls- you will need to
'add/delete based on the controls on your DSS sheet

'Clear Combo Box(es)
'Note: In the event a combo box has values in it when the workbook is saved, the first command clears that text value. The second
'line clears the options/ choices of the combo box
        cbostyle.Text = ""
        cbostyle.Clear
        cbosize.Text = ""
        cbosize.Clear
        cboSilhouettes.Text = ""
        cboSilhouettes.Clear
        cboSleeves.Text = ""
        cboSleeves.Clear
'To clear option buttons or check boxes, set the values to False
        optVneck = False
        optvnecktwo = False
'Text boxes, image controls, and labels are cleared by putting "" as shown below
        txtDetails.Text = ""
        imgDetails.Picture = LoadPicture("")
        lblFirstName.Caption = ""
        
'Disable all controls that are used for questions or answers by setting the enabled property to False
        cbostyle.Enabled = False
        cbosize.Enabled = False
        cboSilhouettes.Enabled = False
        cboSleeves.Enabled = False
        optVneck.Enabled = False
        optvnecktwo.Enabled = False
        txtDetails.Enabled = False
        imgDetails.Enabled = False
'Enable List Box, clear it, then resize it, then disable it
        lstChoices.Enabled = True
        lstChoices.Clear
        lstChoices.Height = 50
        lstChoices.Width = 75
        lstChoices.Enabled = False
'OPTION A: Setup First Question (if a combobox)
        cbostyle.Enabled = True
'Note: Add a clear command EACH time you AddItems to a combo or list box
        cbostyle.Clear
        cbostyle.AddItem "Romantic"
        cbostyle.AddItem "Casual"
        cbostyle.AddItem = "Simple"
'Get User's First Name- Note: No error checking except for Null/Empty
'Remember to Create the Label on the DSS page!!
        lblFirstName.Enabled = True
        varUserFirstName = InputBox("What is your FIRST name?")
        If varUserFirstName <> "" Then lblFirstName.Caption = ("Hello beautiful" & varUserFirstName & "!")

End Sub

 

[rachaelelianna]

Actually, never mind!! I managed to figure out the issue. Thank you SO much!! You have really helped me more than you could know!!