Can we use strings from previous macros in same module


Can we use this string in other macros? Sub Get_Values() Dim username As String End ---------------------------------------------------- Sub FetchNames() I mean here... like Path = String End Sub




You can use variables across macros if you make the variable a public one. Do that by placing it above all macros in the module and then writing it like this:

Public username as String

username is the variable name.


Indeed, for practical purposes, the use of a publicly declared variable, as Don suggests, is likely to be most efficient, although for an item like user name a constant would probably be more suitable because you can assign the value in the same line of code as you declare it.

Option Explicit
Private Const PathName As String = "C:\My Documents\"

Place the declaration at the very top of the code module, just below the Option statement and before any procedures. If you declare it as Private it will be available to all procedures in the same module. Declare it as Public to be able to refer to it from anywhere in the same project.

However, the gist of your question seems to drive at the use of a function. Install the code below in a normal code module and run the sub 'MySub'.

Sub MySub()
    Dim i As Long
    For i = 0 To 2
        MsgBox "The file name is:" & vbCr & _
               MyValues(i) & "\My workbook.xlsx"
    Next i
End Sub
Function MyValues(Idx As Long) As String
    ' the Split function returns a 0-based array
    MyValues = Split("C:\My Documents,D:\Your Documents,C:\His Documents", ",")(Idx)
End Function

The function 'MyValues' can return one of 3 values, depending upon which argument it is called with. The simple call is like

Debug.Print MyValues(2)
which would print "C:\His Documents" in the Immediate Window. In 'MySub' this result is integrated into a message.

My intention is to demonstrate the use of a function as compared to a constant or variable. A function can execute a calculation and return the result of it. 'MySub's idea could be realised using constants but much more code would be required to do so.


Answer the Question

You must create an account to use the forum. Create an Account or Login