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!
Transferring value of variables to Sub to another Sub
-
- Lounger
- Posts: 27
- Joined: 24 Jun 2020, 01:39
-
- Administrator
- Posts: 78524
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Transferring value of variables to Sub to another Sub
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.
Another option is to pass the values as arguments to the other sub.
Best wishes,
Hans
Hans
-
- PlutoniumLounger
- Posts: 15636
- Joined: 24 Jan 2010, 23:23
- Location: brings.slot.perky
Re: Transferring value of variables to Sub to another Sub
Hello Teran(?)teranprasanna wrote: ↑21 Jul 2021, 15:10I have two integer values in one sub and need to transfer values of these variables to another sub.
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
There's nothing heavier than an empty water bottle
-
- Lounger
- Posts: 27
- Joined: 24 Jun 2020, 01:39
Re: Transferring value of variables to Sub to another Sub
Thanks Chris.ChrisGreaves wrote: ↑21 Jul 2021, 16:56Hello Teran(?)teranprasanna wrote: ↑21 Jul 2021, 15:10I have two integer values in one sub and need to transfer values of these variables to another sub.
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
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.
-
- Lounger
- Posts: 27
- Joined: 24 Jun 2020, 01:39
Re: Transferring value of variables to Sub to another 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.
-
- Administrator
- Posts: 78524
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Transferring value of variables to Sub to another Sub
Option 1:
Option 2:
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
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
Hans
-
- 5StarLounger
- Posts: 964
- Joined: 08 Nov 2012, 17:54
Re: Transferring value of variables to Sub to another Sub
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
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
-
- GoldLounger
- Posts: 2631
- Joined: 26 Jan 2010, 16:31
- Location: Southern California
Re: Transferring value of variables to Sub to another Sub
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.
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
John
-
- 3StarLounger
- Posts: 308
- Joined: 24 Dec 2015, 16:41
Re: Transferring value of variables to Sub to another Sub
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.
- 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.
-
- PlutoniumLounger
- Posts: 15636
- Joined: 24 Jan 2010, 23:23
- Location: brings.slot.perky
Re: Transferring value of variables to Sub to another Sub
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
There's nothing heavier than an empty water bottle
-
- Administrator
- Posts: 78524
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Transferring value of variables to Sub to another Sub
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.
Recent versions of Access also have a TempVars Object that stores TempVar objects.
Best wishes,
Hans
Hans