PDA

View Full Version : [SOLVED] Passing variables between modules



StOsCZ
12-08-2020, 04:47 AM
Hi,
I want something like this, and can't figure it out:

Module 1


Dim col5, col6 As Double
col5 = 5
row6=7

Module 2 (same workbook)


Sub something()
Cells(col5,row6) = "something"
End Sub

I have multiple macros, and dont want to have them all in one Module, so they are more organized. I use some collums for specific data across all modules, so if I need to change the position(number) of the collum I don¨t have to search all the modules and change them manualy. I want some "settings" for all the modules.

Plus I won´t be the only user of this in the future, multiple people will use it on their PC

Part of the actual code (with does not work )


MODULE 1

Sub prepocitejData()

Dim dataRadek As Double
dataRadek = dataPrvniRadek


Do
Sheets(nazevListData ).Cells(dataRadek, dSUM).R1C1 = "=RC" + dMN + "*RC" + dJC

dataRadek = dataRadek + 1
Loop Until IsEmpty(Cells(dataRadek, dID ))


End Sub

MODULE 2


Dim dataPrvniRadek As Double
dataPrvniRadek = 2

Dim nazevListData As String
nazevListData = data

Dim dID, dSO, dRO, dSD, dPC, dNP, dMJ, dMN, dJC, dPP, dVV, dTS, dKP, dVA, dSUM As Double
dID = 1
dSO = 2
dRO = 3
dSD = 4
dPC = 5
dNP = 6
dMJ = 7
dMN = 8
dJC = 9
dPP = 10
dVV = 11
dTS = 12
dKP = 13
dVA = 14
dSUM = 19

also, I'm not sure if ...
.R1C1 = "=RC" + dMN + "*RC" + dJC this will work.

Thanks a lot !

snb
12-08-2020, 04:50 AM
I have multiple macros, and dont want to have them all in one Module, so they are more organized.

Should be read as:

I have multiple macros, and dont want to have them all in one Module, so they are less organized.

snb
12-08-2020, 04:52 AM
Sub M_snb()
x=5
y=12
M_snb_001 x,y
End Sub

Sub M_snb_001(a,b)
msgbox a & vblf & b
end Sub

StOsCZ
12-08-2020, 04:53 AM
Should be read as:

I have multiple macros, and dont want to have them all in one Module, so they are less organized.

If I have them all in one module, I have to scroll through them. this way i can have like 3 modules on one screen and just look for what I need.
Are there any, like, technical problems with this? Proccesing times and what not?

Is it really better to have them all in one?

snb
12-08-2020, 07:07 AM
You don't have to scroll if you use the VBEditor's UI correctly.

Paul_Hossler
12-08-2020, 08:06 AM
If I have them all in one module, I have to scroll through them. this way i can have like 3 modules on one screen and just look for what I need.
Are there any, like, technical problems with this? Processing times and what not?

Is it really better to have them all in one?

Usually personal choice, but it does help to keep things organized.

1. IN ...

Dim dID, dSO, dRO, dSD, dPC, dNP, dMJ, dMN, dJC, dPP, dVV, dTS, dKP, dVA, dSUM As Double

... everything except dSum is a variant since there is no explicit As ...

You probably wanted


Dim dID As Double, dSO As Double .....etc


2. What I usually do is ....


I use some columns for specific data across all modules, so if I need to change the position(number) of the column I don't have to search all the modules and change them manually. I want some "settings" for all the modules.


... put these in 'mod_Global' and ...



Option Explicit


Public Const colDate As Long = 1
Public Const colName As Long = 2
Public Const colTitle As Long = 3





then use in any module



Option Explicit


Sub Four()


Call Add("GHI", "JKL", "Big Boss")
Call Add("MNO", "PQR", "Boss")
Call Add("STU", "VWX", "Worker")
End Sub




Sub Add(FN As String, LN As String, T As String)
Dim s3 As String ' scope = Procedure (not 'module' like I said in the the workbook :-)
Dim r As Range

s3 = LN & ", " & FN

'first blank cell after last cell with data in colA
With ActiveSheet
Set r = .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0).EntireRow
End With

With r
.Cells(colDate).Value = Int(Now)
.Cells(colName).Value = s3
.Cells(colTitle).Value = T
End With
End Sub




3. Variable scope and visibility is important so that the most correct is used. The attached WB has some examples if you want to look

Understanding scope and visibility (VBA) | Microsoft Docs (https://docs.microsoft.com/en-us/office/vba/language/concepts/getting-started/understanding-scope-and-visibility)

StOsCZ
12-09-2020, 12:07 AM
...
Extremely helpfull. Thanks a lot !

SamT
12-09-2020, 07:19 AM
Enums are a great way to declare a bunch of Long constants


Public Enum MyConstants
dID = 1
dSO
dRO
dSD
dPC
dNP
dMJ
dMN
dJC
dPP
dVV
dTS
dKP
dVA
dSUM = 19
End Enum
Note that unless a value is specified, Enumerated Constants increment values by 1


Enum IncrementingExample
A1 = 1
A2
A3
B1 = 1
B2
B3
C1 = 1
C2
C3 = -42
End Enum
A3 and B3 = 3. C3 = -42.
IncrementingExample Constants are only available in the one module.

All Public declarations outside of any Procedures are available to all code in any module.

I prefer keeping all code specific to a particular Sheet in that Sheet's Code module

StOsCZ
12-09-2020, 07:36 AM
Enums are a great way to declare a bunch of Long constants

Note that unless a value is specified, Enumerated Constants increment values by 1

A3 and B3 = 3. C3 = -42.
IncrementingExample Constants are only available in the one module.


This is really handy, thanks !



All Public declarations outside of any Procedures are available to all code in any module.

I prefer keeping all code specific to a particular Sheet in that Sheet's Code module

I tried to declare it public

Public sOd As Double
but it didn't work for some reason. The
Const apparently did the trick.