View Full Version : Sub or Function not defined compile error help
jcutler
09-08-2018, 01:58 AM
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
09-08-2018, 11:38 AM
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
09-08-2018, 05:43 PM
So it's not possible to pass an argument to a Private Sub? Thank you
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
09-09-2018, 06:32 AM
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
09-09-2018, 06:50 AM
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
09-10-2018, 01:53 AM
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
09-10-2018, 02:40 AM
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
09-10-2018, 06:13 AM
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
09-10-2018, 06:51 AM
Property procedures are (IMO) straightforward and easy to maintain. :)
jcutler
09-10-2018, 03:48 PM
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
09-10-2018, 08:04 PM
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.