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

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