Sub or Function not defined compile error help

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.

Code:

---

Call SecondCode(True, "Arial", 22)

---

The code below was written in module.

Code:

---

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

Code:

---

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.

Code:

---

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.

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

Hi ,
you can if its in the same module

Code:

---

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

---

Quote:

---

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.:confused:

Thank You

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

In a standard module

Code:

---

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

Code:

---

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

---

Quote:

---

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

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.

Quote:

---

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

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

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.

Code:

---

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:

Code:

---

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

---

Code:

---

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.

Quote:

---

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 --

Code:

---

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

Code:

---

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

Code:

---

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

Code:

---

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

---