Transferring value of variables to Sub to another Sub

teranprasanna
Lounger
Posts: 27
Joined: 24 Jun 2020, 01:39

Transferring value of variables to Sub to another Sub

Post by teranprasanna »

Hi All,

Can anyone help me for this.

I have two integer values in one sub and need to transfer values of these variables to another sub.

Thank you!

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Transferring value of variables to Sub to another Sub

Post by HansV »

One option is to declare the variables as Public at the top of a module. You can then use the variables anywhere in the same VBA project (document, workbook, presentation, ...)

Another option is to pass the values as arguments to the other sub.
Best wishes,
Hans

User avatar
ChrisGreaves
PlutoniumLounger
Posts: 15498
Joined: 24 Jan 2010, 23:23
Location: brings.slot.perky

Re: Transferring value of variables to Sub to another Sub

Post by ChrisGreaves »

teranprasanna wrote:
21 Jul 2021, 15:10
I have two integer values in one sub and need to transfer values of these variables to another sub.
Hello Teran(?)

Two other methods come to mind:-
(1) Preserve the data from the first subroutine in a document or workbook variable. The data is stored within the document/workbook file, but is visible only to the programmer (you), and is not visible to the user. Save the document or workbook and the data from the first subroutine is saved too. Later, load the document or workbook and have the second subroutine retrieve the "hidden" data and get to work.
(2) Write the data from the first subroutine in an INI file or other text file, and have the second subroutine retrieve the data from the INI/TXT file.

The first method works across user boundaries, as long as you can share the document/workbook file with another user.
The second method works across user boundaries, but you would need to share the INI/TXT file as well.

Can you give us a good idea of what you are trying to achieve with the shared data? Is the application a private application run only by you and only on your machine, or is it an application that has to work/communicate right across a corporation or business?
Cheers
Chris
An expensive day out: Wallet and Grimace

teranprasanna
Lounger
Posts: 27
Joined: 24 Jun 2020, 01:39

Re: Transferring value of variables to Sub to another Sub

Post by teranprasanna »

ChrisGreaves wrote:
21 Jul 2021, 16:56
teranprasanna wrote:
21 Jul 2021, 15:10
I have two integer values in one sub and need to transfer values of these variables to another sub.
Hello Teran(?)

Two other methods come to mind:-
(1) Preserve the data from the first subroutine in a document or workbook variable. The data is stored within the document/workbook file, but is visible only to the programmer (you), and is not visible to the user. Save the document or workbook and the data from the first subroutine is saved too. Later, load the document or workbook and have the second subroutine retrieve the "hidden" data and get to work.
(2) Write the data from the first subroutine in an INI file or other text file, and have the second subroutine retrieve the data from the INI/TXT file.

The first method works across user boundaries, as long as you can share the document/workbook file with another user.
The second method works across user boundaries, but you would need to share the INI/TXT file as well.

Can you give us a good idea of what you are trying to achieve with the shared data? Is the application a private application run only by you and only on your machine, or is it an application that has to work/communicate right across a corporation or business?
Cheers
Chris
Thanks Chris.

I'm creating an add-in and user should not aware these variables. I can transfer one variable value to other Sub. But, I'm not sure how to transfer multiple variable values from one sub to other.

teranprasanna
Lounger
Posts: 27
Joined: 24 Jun 2020, 01:39

Re: Transferring value of variables to Sub to another Sub

Post by teranprasanna »

HansV wrote:
21 Jul 2021, 15:14
One option is to declare the variables as Public at the top of a module. You can then use the variables anywhere in the same VBA project (document, workbook, presentation, ...)

Another option is to pass the values as arguments to the other sub.
Thanks Hans.

I can transfer one variable value to other Sub. But, I'm not sure how to transfer multiple variable values from one sub to other. Appreciate your support.

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Transferring value of variables to Sub to another Sub

Post by HansV »

Option 1:

Code: Select all

Option Explicit

Public Variable1 As Long
Public Variable2 As String

Sub Macro1()
    Variable1 = 37
    Variable2 = "Eileen"
    Call Macro2
End Sub

Sub Macro2()
    MsgBox "Variable1 is " & Variable1
    MsgBox "Variable2 is " & Variable2
End Sub
Option 2:

Code: Select all

Option Explicit

Sub Macro1()
    Dim Variable1 As Long
    Dim Variable2 As String
    Variable1 = 37
    Variable2 = "Eileen"
    Call Procedure2(Variable1, Variable2)
End Sub

Sub Procedure2(Var1 As Long, Var2 As String)
    MsgBox "Var1 is " & Var1
    MsgBox "Var2 is " & Var2
End Sub
Best wishes,
Hans

LisaGreen
5StarLounger
Posts: 964
Joined: 08 Nov 2012, 17:54

Re: Transferring value of variables to Sub to another Sub

Post by LisaGreen »

Hi,

Not sure if this will help but ..
You could also try storing the variables somewhere else and then reading them in to the other sub. You could do this by creating read and write code procedures.

Amongst other possible places to store values are..
INI files
TXT files
Some other format designed by you possibly encrypted
The registry
Some other programs data files like ACCESS or EXCEL

You can read a whole section of an INI file in one go to aSSIGN DATA TO VARIABLES FOR EXAMPLE.

hth
Lisa

jstevens
GoldLounger
Posts: 2618
Joined: 26 Jan 2010, 16:31
Location: Southern California

Re: Transferring value of variables to Sub to another Sub

Post by jstevens »

Here is a link to an INI solution.

I have tested it and it works with one minor change in the code. Replace "Application.CurrentProject.Path" with "Application.ActiveWorkbook.Path" (omit the quote marks).

Code is provided by Daniel Pineault.
Regards,
John

CData
3StarLounger
Posts: 308
Joined: 24 Dec 2015, 16:41

Re: Transferring value of variables to Sub to another Sub

Post by CData »

Let me add a technique that may not work in this exact situation but is very useful, and very simple, for Access oriented developers:
- put/write your variables to textboxes on a form that is always opened

I have come to appreciate the value of a 'Start' screen that is always opened to hold buttons (i.e. switchboard style) and then serve this additional role of holding textboxes that can be used to transfer variable values.

Besides being very simple i.e. low code - one can temporarily make the text boxes visible in order really ease debug & testing - - then make not visible for production release.

User avatar
ChrisGreaves
PlutoniumLounger
Posts: 15498
Joined: 24 Jan 2010, 23:23
Location: brings.slot.perky

Re: Transferring value of variables to Sub to another Sub

Post by ChrisGreaves »

CData wrote:
25 Jul 2021, 15:46
... one can temporarily make the text boxes visible in order really ease debug & testing - - then make not visible for production release.
:clapping: :clapping: :clapping:

This can be achieved, surely, with any VBA application that supports a UserForm (MyUserForm.frm) certainly for Word and Excel.

To the best of my knowledge such user forms can be loaded at the start of the application, rendered invisible, and used for data storage (labels and the like) for the duration of the user macro.
Thanks
Chris
An expensive day out: Wallet and Grimace

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Transferring value of variables to Sub to another Sub

Post by HansV »

In an Access database, you can use a (hidden) table to store values for shared use.
Recent versions of Access also have a TempVars Object that stores TempVar objects.
Best wishes,
Hans