Sub or Function not defined compile error help

[jcutler]

I am trying to learn and understand VBA. So I am trying to figure out how to pass a variable through as an argument. I found this example online. It shows how you could pass an argument from a Sub to a Private Sub but when I run it, all I get is a "Compile error: Sub or Function not defined" and I don't understand why I am getting this error. I copied it exactly as they had it.
The following code is what I assume is triggering the error message because "Call SecondCode is what is highlighted.

Call SecondCode(True, "Arial", 22)

The code below was written in module.

Sub FirstCode()

    Dim FormatCell As Integer
    
    FormatCell = ActiveCell.Value
    
    If FormatCell < 20 Then
        
        Call SecondCode(True, "Arial", 22)
    
    End If


End Sub

This second code was written in ThisWorkbook

Option Explicit

Private Sub SecondCode(BoldValue As Boolean, NameValue As String, SizeValue)
    
    With ActiveCell.Font
        .Bold = BoldValue
        .Name = NameValue
        .Size = SizeValue
    End With
    
End Sub

Thank you in advance for any help given.

[david000]

Option Explicit

Remove> "Private" Sub SecondCode(BoldValue As Boolean, NameValue As String, SizeValue)
    
    With ActiveCell.Font
        .Bold = BoldValue
        .Name = NameValue
        .Size = SizeValue
    End With
    
End Sub

Remove Private or copy the code for SecondCode to the same Module as FirstCode.

[jcutler]

So it's not possible to pass an argument to a Private Sub? Thank you

[pike]

Hi ,
you can if its in the same module

Option Explicit
Sub FirstCode()
    Dim FormatCell As Integer
    
    FormatCell = ActiveCell.Value
    
    If FormatCell < 20 Then
        
        Call SecondCode(True, "Arial", 22)
    
    End If

End Sub
Private Sub SecondCode(BoldValue As Boolean, NameValue As String, SizeValue)
    
    With ActiveCell.Font
        .Bold = BoldValue
        .Name = NameValue
        .Size = SizeValue
    End With
    
End Sub

[jcutler]

I am trying to learn and understand VBA.

So what if I have module that needs to gets its value from a variable located in one Private Sub and those values are needed to be passed to another userform's private sub to perform a calculation or formula. Is retrieving those values even possible by doing it this way?
The reason why I am asking this question is because I will soon be working on a project that may have to use this method to retrieve the information needed. I originally thought about using a Public Sub to achieve this but I've heard that this is bad Code practice because it uses up more memory slowing the program down.

Thank You

[Paul_Hossler]

I think the easiest way is to just use a Public variable to hold such things

In a standard module

Option Explicit


Public HoldUserInput As String

Sub drv()
    HoldUserInput = ActiveSheet.Range("A1").Value

    Load UserForm1
    UserForm1.Show
    
    MsgBox HoldUserInput
End Sub

In UserForm1 code module

Option Explicit

Private Sub UserForm_Initialize()
    Me.TextBox1.Value = HoldUserInput
End Sub

Private Sub CommandButton1_Click()
    
    With Me
        
        HoldUserInput = .TextBox1.Value
        .Hide

    End With
    Unload Me
End Sub

[jcutler]

I am trying to learn and understand VBA.

Just thinking ahead.... So many more questions.
1. Is it possible to pass a value stored in a variable located in a Private Sub to other Private Subs?
2. Will that also give a compile error? Haven't tried it yet.
3. Would it be better to convert a Private Sub, like a command button on a Userform, to a Public Sub in order to pass values?

Thank You

[Aflatoon]

1. Yes. The scope of a routine (Public vs Private) has nothing to do with passing arguments. It only affects where you can call it from. (although Application.Run can violate scope rules anyway)
2. No, it won't.
3. Not relevant - see #1.

There is absolutely nothing wrong with public routines. Public variables, on the other hand, should be used as little as possible.

[Paul_Hossler]

Public variables, on the other hand, should be used as little as possible.

Agree, but I've never found a good way to pass information to userforms, 'Good' being defined as a straight-forward easily maintainable way

[Aflatoon]

Property procedures are (IMO) straightforward and easy to maintain.

[jcutler]

I'm still kind of new to this site. So I'm not sure what the forum rules are because the code below goes with topic we were discussing in regards to passing values. If I am wrong, please let me know and I'll re-post in another thread. Thank you.
Here's why I asked how to pass values. In the code below the variable sPrdCde needs to be passed to other forms and a module from this form. This is also why I mentioned about passing a value with the command button.
The code below appears first.

Private Sub cmdbtnDone_Click()    
    'Declare variables
    Dim r As Range, r1 As Range
            
    'Sets variables to a range
    Set r = Range(RefEdit1)
    Set r1 = r(1)
    
    wbName = ActiveWorkbook.Name
    
    Load Chattemfrm
    Chattemfrm.cmbSDPFLine.Value = ActiveWorkbook.Name
    Chattemfrm.txtbxPrdctNm.Value = r1.Offset(0, -5).Value
    sPrdCde = r1.Offset(0, -6).Value
    Chattemfrm.txtBxLtNum.Value = r1.Offset(0, -3).Value
    Chattemfrm.txtBxShopNumber.Value = r1.Offset(0, 1).Value
    Chattemfrm.txtbxVndrLtNu.Value = r1.Offset(0, -2).Value
    Chattemfrm.txtbxdz = Me.txtbxRangeTotal.Value
    Chattemfrm.cmbPrdCde.Value = sPrdCde & " " & "(" & Chattemfrm.txtbxPrdctNm & ")"
    Call ReName
    Unload Me
    Workbooks(wbName).Activate
    ActiveWorkbook.Close
    Call Test
    'Chattemfrm.Show
    
End Sub

The code below appears second and is from a module also this is where I declared sPrdCde.

Also, I'm not sure why the declared Public variables in the code below won't pass to frmAddProduct and for what ever reason I had to declare the sPrdCde again

Module:

Option ExplicitPublic lDz As Long, lCs As Long
Public sUOM As String, lStckNum As String
Sub Test()


Dim ws_count As Integer, i As Integer, FinalRow As Integer, x As Integer
Dim sPrdCde As String


lDz = 0
lCs = 0
sUOM = " "
lStckNum = ""


ws_count = ActiveWorkbook.Worksheets.Count
    For i = 4 To ws_count
        Worksheets(i).Activate
        FinalRow = Cells(Rows.Count, 2).End(xlUp).Row
        For x = 1 To FinalRow
            Cells(x, 2).Select
            If Cells(x, 2).Value = sPrdCde Then
               lDz = Cells(x, 4)
               lCs = Cells(x, 5)
               sUOM = Cells(x, 6)
               Chattemfrm.txtbxStckNum.Value = Cells(x, 7)
            End If
        Next x
    Next i
    If lDz = 0 Or lCs = 0 Or sUOM = "" Or Chattemfrm.txtbxStckNum.Value = "" Then
        Call ErrorTrap
    End If
    Chattemfrm.Show
End Sub

Private Sub UserForm_Initialize()    
    Dim sPrdCde As String
    
    txtbxPrdctCde.Value = sPrdCde
    txtbxDescription.Value = Chattemfrm.txtbxPrdctNm.Value
    If lDz = 0 Then
        txtbxDzPrCs.Enabled = True
    Else
        txtbxDzPrCs.Value = lDz
        txtbxDzPrCs.Enabled = False
    End If
    
    
    If lCs = 0 Then
        txtbxCsPerPal.Enabled = True
    Else
        txtbxCsPerPal.Value = lCs
        txtbxCsPerPal.Enabled = False
    End If
    
    
    If lStckNum = "" Then
        frmAddProduct.txtbxStckNum.Enabled = True
    Else
        frmAddProduct.txtbxStckNum = Chattemfrm.txtbxStckNum.Value
        frmAddProduct.txtbxStckNum.Enabled = False
    End If


    If sUOM = " " Then
        frmAddProduct.optDz.Enabled = True
        frmAddProduct.optEa.Enabled = True
    ElseIf sUOM = "DZ" Then
        Me.optDz.Value = True
        Me.optDz.Enabled = False
        Me.optEa.Enabled = False
    Else
        Me.optEa.Value = True
        Me.optDz.Enabled = False
        Me.optEa.Enabled = False
    End If
    
End Sub

Please let me know if you have any questions. Thank you all for your gracious help on what I am to assume will be a "pulling out my hair" project.

[Paul_Hossler]

I'm still kind of new to this site. So I'm not sure what the forum rules are because the code below goes with topic we were discussing in regards to passing values. If I am wrong, please let me know and I'll re-post in another thread. Thank you.

You're fine

This is just the very bare bones of an example


In a standard module --

Option Explicit


Public sPrdCde As String

Sub test()
    
    sPrdCde = Worksheets("Sheet1").Range("A2")
    
    MsgBox sPrdCde
    
    Load ChattemFrm
    
    ChattemFrm.Show
    
    MsgBox sPrdCde

End Sub

In the User Form code module

Option Explicit

Private Sub UserForm_Initialize()
    Me.txtPrdCde.Value = sPrdCde
End Sub

Private Sub CommandButton1_Click()
    sPrdCde = Me.txtPrdCde.Value
    Me.Hide
    Unload Me
End Sub

As Aflatoon said, there are other ways to pass the information, but they can be a little tricky at first to get things in the correct order, so IMHO public variables might be easier for you

But in case you're interested, here's another way to do it


In the Userform code module

Option Explicit

Private Sub CommandButton1_Click()
    Me.Hide
End Sub

Property Let PrdCde(s As String)
    Me.txtPrdCde.Value = s
End Property

Property Get PrdCde() As String
    PrdCde = Me.txtPrdCde.Value
End Property

In Standard module

Option Explicit
Sub test()
    
    MsgBox Worksheets("Sheet1").Range("A2")
    
    Load ChattemFrm
    
    ChattemFrm.PrdCde = Worksheets("Sheet1").Range("A2")
    ChattemFrm.Show
    Worksheets("Sheet1").Range("A2") = ChattemFrm.PrdCde
    
    MsgBox Worksheets("Sheet1").Range("A2")


    Unload ChattemFrm

End Sub