Passing variables between modules

[StOsCZ]

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]

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]

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]

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]

You don't have to scroll if you use the VBEditor's UI correctly.

[Paul_Hossler]

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

[StOsCZ]

...

Extremely helpfull. Thanks a lot !

[SamT]

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]

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.