using public or global to declare variable and get the value to use on other module

[bigme]

Hello,
can someone explain how to get or use a variable value?
using public or global to declare the variable only give you access to use the variable on other module but not the value in it, what i need is to use the value on other module, i hope someone can help me with this, thank you.

regards,
bigMe

 

[Lutfor Rahman Shimanto]

Hello,
can someone explain how to get or use a variable value?
using public or global to declare the variable only give you access to use the variable on other module but not the value in it, what i need is to use the value on other module, i hope someone can help me with this, thank you.

regards,
bigMe

Hello BigMe

Thanks for posting your queries with such clarity. You are absolutely right about the global variable. The public or global variable can be accessed with other modules. However, we can not directly use the value inside the global variable.

To overcome the situation, we can create a procedure which will initialize the value of the public variable. Later, we can call that procedure within other modules.

Module 1 VBA Code:

Public MyGlobalVariable As Integer

Sub InitializeGlobalVariable()
    
    MyGlobalVariable = 42

End Sub

Module 2 VBA Code:

Sub AnotherModuleProcedure()
    
    Call Module1.InitializeGlobalVariable
    MsgBox "The value of MyGlobalVariable is: " & MyGlobalVariable

End Sub

OUTPUT:

​

Hopefully, the idea will help you in reaching your goal. Good luck!

Regards
Lutfor Rahman Shimanto

 

[bigme]

dear Lutfor,
thank you very much , it's work....
but can you help me, if i want the value using input method and i already try it, but when i use to other module it's always ask me to input again, how to keep the value based on the first input only, so when i run the next module the value still using the first value, thank you.

regards,
bigMe

 

[Lutfor Rahman Shimanto]

dear Lutfor,
thank you very much , it's work....
but can you help me, if i want the value using input method and i already try it, but when i use to other module it's always ask me to input again, how to keep the value based on the first input only, so when i run the next module the value still using the first value, thank you.

regards,
bigMe

Dear BigMe

Thanks for your queries. You want the value of Global Variable using the input method, but when you use other modules, it always asks me to input again.

I am happy to share the idea of keeping the value based only on the first input so that when you run the next module, Throughout this idea, you have to initialize the Global Variable only once. After the initialization, you can use the variable's value for further code blocks.

Module 1 VBA Code:

Public MyGlobalVariable As Integer

Sub InitializeGlobalVariable()
    MyGlobalVariable = InputBox("Input th Global Variable")
End Sub

Module 2 VBA Code:

Sub AnotherModuleProcedure()
    
    If MyGlobalVariable = 0 Then
        Call Module1.InitializeGlobalVariable
    End If

    MsgBox "The value of MyGlobalVariable is: " & MyGlobalVariable.

End Sub

Notes: When you declare a global variable in VBA, its value is reset every time you close and reopen the Excel workbook.

Hopefully, the idea will help you in reaching your goal. Good luck!

Regards
Lutfor Rahman Shimanto