Solved: Best method for passing a variable from one code to another code

[jaminben]

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]

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]

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]

Post a specific example if you have questions, and I'm sure people here will help

Paul

[jaminben]

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]

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]

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]

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]

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]

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]

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]

Ok, Thanks