View Full Version : Solved: Best method for passing a variable from one code to another code
jaminben
02-25-2012, 06:27 AM
Hello,
What's the best method to pass a variable from one piece of code to another?
Currently I do it using a global variable I've setup in one particular module but I'm not sure if this is the best way to go about it.
So for example it goes like:
1st Module
Sub Test_1()
VariableName = "Test"
End Sub
Global Variables Module
Public VariableName As String
2nd Module
Sub Test_2()
If VariableName = "Test" Then
Debug.Print "VariableName: " & VariableName
End If
End Sub
Thanks
Ben
Paul_Hossler
02-25-2012, 07:53 AM
1. No such thing as a dumb question
2. I've always found that global variables can make it very hard to debug a problem since it's hard to trace where they might have gotten changed or are used. I try to structure my modules so that there's "One Path In, One Patrh Out'
3. You can / should modularize and pass parameter(s) to a 'general purpose function or sub
The online help has a much more extensive explaination, but just a quick example of the concepts for passing and returning parameters:
Option Explicit
Sub Main()
Dim s As String
Dim i As Long
i = 100
MsgBox LowerFunction(i, 50)
MsgBox LowerFunction(15, 10)
i = 5
MsgBox LowerFunction(i, 1000 * i)
'ByRef allows lower sub to change inputs for upper sub
s = "asdfasdfasdf"
Call LowerSubByRef(s)
MsgBox "s in main = " & s
'ByRef does not allows lower sub to change inputs for upper sub
s = "asdfasdfasdf"
Call LowerSubByVal(s)
MsgBox "s in main = " & s
End Sub
Function LowerFunction(num1 As Long, num2 As Long) As Double
LowerFunction = (num1 * num2) / 1.5
End Function
Sub LowerSubByRef(ByRef s1 As String)
s1 = UCase(s1)
MsgBox "s1 in LowerSubByRef = " & s1
End Sub
Sub LowerSubByVal(ByVal s1 As String)
s1 = UCase(s1)
MsgBox "s1 in LowerSubByVal = " & s1
End Sub
Paul
jaminben
02-25-2012, 08:11 AM
Thanks for the swift reply once again Paul :thumb
I still seem to have issues when using "Call Module Name" and passing a value / parameter back and forth. What I've tried to do in my code is create one method to achive something and then re-use that method in multiple places to save re-writing the same code over and over.
I think I'll checkout the online help more throughly.
Cheers
Ben
Paul_Hossler
02-25-2012, 01:47 PM
Post a specific example if you have questions, and I'm sure people here will help
Paul
jaminben
02-26-2012, 05:13 AM
Ok, I'm getting in real muddle with this so below is a simple example of one of the things I'd like to do.
I have a standard Yes/No confirm action message box which I'd like to use in various places.
This is one of the actions that I need to be confirmed.
Sub Clear_Defect_Sheet()
Application.ScreenUpdating = False
Range("A6:L199").Select
Selection.ClearContents
Range("A6").Select
Application.ScreenUpdating = True
End Sub
Here's my Yes/No MsgBox.
Sub Yes_No_Confirm_Action()
MSG1 = MsgBox("Are you sure you want to delete all entries?", vbYesNo, "Confirm Action")
If MSG1 = vbYes Then
Debug.Print "Yes Clicked"
Else
Debug.Print "No Clicked"
End If
End Sub
Currently the Yes/No message is hardcoded within the MSG1 message but I'd like to be able to specify the message from the Clear_Defect_Sheet and then return the value to the Sub Clear_Defect_Sheet as to tell it to continue or not.
Once I can specify what message to display in the box before the box opens and what actions are returned I can then use the same message box for multiple situations.
Hopefully when I can see how to pass these values from one piece of code to another in this simple example I may understand how it should work elsewhere... thats the theory anyway :think:
Thanks
Ben
Bob Phillips
02-26-2012, 05:39 AM
Sub Clear_Defect_Sheet()
If Yes_No_Confirm_Action("Are you sure you want to delete all entries?") = vbYes Then
Application.ScreenUpdating = False
Range("A6:L199").ClearContents
Range("A6").Select
Application.ScreenUpdating = True
End If
End Sub
Public Function Yes_No_Confirm_Action(ByVal msg As String) As VbMsgBoxResult
Yes_No_Confirm_Action = MsgBox(msg, vbYesNo, "Confirm Action")
End Function
jaminben
02-26-2012, 05:51 AM
Cool, that works :thumb
But not how I thought it should work... for some reason I was half expecting to have to specify the "msg" part in the Function from the Clear_Defect_Sheet. So something like msg = "Are you sure you want to delete all entries?" but I see thats not how it works.
I've got alot of learning to do :bug:
Thank you xld
jaminben
02-26-2012, 08:28 AM
I'm back again with another piece of code... :doh:
Public Function Clean_Circuit_Reference_For_Phase()
Dim CircuitReference, LastPosition As String
Dim v As Variant
CircuitReference = "1\1\L2"
LastPosition = Len(CircuitReference) - Len(Replace(CircuitReference, "\", ""))
v = Split(CircuitReference, "\")
Phase = v(2)
End Function
Sub Get_Phase()
'How do I get the result "Phase" from the Function
Debug.Print "Phase: " & Phase
End Sub
All help appreciated
Thanks
Ben
Paul_Hossler
02-26-2012, 09:18 AM
Your function should 'return' the answer, etc.
Check the '<------------------- lines below
Option Explicit
Public Function Clean_Circuit_Reference_For_Phase() As Variant '<--------------------
Dim CircuitReference, LastPosition As String
Dim v As Variant
CircuitReference = "1\1\L2"
LastPosition = Len(CircuitReference) - Len(Replace(CircuitReference, "\", ""))
v = Split(CircuitReference, "\")
Clean_Circuit_Reference_For_Phase = v(2) '<-----------------------
End Function
Sub Get_Phase()
'How do I get the result "Phase" from the Function
Debug.Print "Phase: " & Clean_Circuit_Reference_For_Phase '<-----------------------
End Sub
Sub Clear_Defect_Sheet()
If Yes_No_Confirm_Action("Are you sure you want to delete all entries?") = vbYes Then
Application.ScreenUpdating = False
Range("A6:L199").ClearContents
Range("A6").Select
Application.ScreenUpdating = True
End If
End Sub
Public Function Yes_No_Confirm_Action(ByVal msg As String) As VbMsgBoxResult
Yes_No_Confirm_Action = MsgBox(msg, vbYesNo, "Confirm Action") '<----------
End Function
As a note, as Dim-ed below, CircuitReference is a Variant type, even though you have As String at the end
Dim CircuitReference, LastPosition As String
Each needs to be specifically defined if you do not want they typed as Variant. Sometimes it will make a difference, and can get hard to track down. Also explicitly typed variables will perfrom faster, and take less memory
Dim CircuitReference As String, LastPosition As String
Paul
jaminben
02-26-2012, 09:24 AM
Ah, now I get it... and now I feel abit dumb for not seeing it straight away.
Can I ask why you've done it as a variant?
"Public Function Clean_Circuit_Reference_For_Phase() As Variant"
It seems to work with and without the "As Variant".
Many Thanks
Ben
Edit
Thanks for the extra bits of info.
Paul_Hossler
02-26-2012, 10:23 AM
Can I ask why you've done it as a variant?
Mostly because I try to be as consistant as I can, even if it's not strictly required.
Sometimes a function might return a String or a Double and I really want it to be a String or Double, so I like to use the 'As String' etc.
Just me :whistle:
Paul
jaminben
02-26-2012, 11:27 AM
Ok, Thanks
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.