Microsoft 365 Variable pour plusieurs macros

[iliess]

Bonjour
J'ai trois macros qui partagent les mêmes variables. Est-ce qu'il y a un moyen de ne pas répéter ce bloc de variables ?
exemple

sub macro1
Dim WsTvaDue As Workbook
Dim ShTVA9 As Worksheet, TCD As Worksheet
Dim Arr As Variant
Dim rg As Range
Dim lastRowTCD As Long, lastRowTva As Long, NbrColTCD As Long, NbrColDestin As Long
Dim Pv_Table As PivotTable
Dim Pv_Field As PivotField
Dim Fltr_KW As String
Set WsTvaDue = ActiveWorkbook
Set TCD = WsTvaDue.Worksheets("ANALYSETCD")
Set ShTVA9 = WsTvaDue.Worksheets("TVA9")
Set Pv_Table = TCD.PivotTables("TvaDue")
Set Pv_Field = Pv_Table.PivotFields("TVA-TYPE")
Pv_Table.RefreshTable
Pv_Field.ClearAllFilters
Pv_Field.CurrentPage = "TVA9%"
ShTVA9.Range("A4:Z2000").ClearContents
lastRowTCD = TCD.Cells(TCD.Rows.Count, 1).End(xlUp).Row
NbrColTCD = TCD.Cells(4, TCD.Columns.Count).End(xlToLeft).Column - 1
NbrColDestin = TCD.Cells(4, TCD.Columns.Count).End(xlToLeft).Column
Set rg = TCD.Range(TCD.Cells(4, 1), TCD.Cells(lastRowTCD - 1, NbrColTCD))
Arr = rg.Value

'macro'
end sub

sub macro2
Dim WsTvaDue As Workbook
Dim ShTVA9 As Worksheet, TCD As Worksheet
Dim Arr As Variant
Dim rg As Range
Dim lastRowTCD As Long, lastRowTva As Long, NbrColTCD As Long, NbrColDestin As Long
Dim Pv_Table As PivotTable
Dim Pv_Field As PivotField
Dim Fltr_KW As String
Set WsTvaDue = ActiveWorkbook
Set TCD = WsTvaDue.Worksheets("ANALYSETCD")
Set ShTVA9 = WsTvaDue.Worksheets("TVA9")
Set Pv_Table = TCD.PivotTables("TvaDue")
Set Pv_Field = Pv_Table.PivotFields("TVA-TYPE")
Pv_Table.RefreshTable
Pv_Field.ClearAllFilters
Pv_Field.CurrentPage = "TVA9%"
ShTVA9.Range("A4:Z2000").ClearContents
lastRowTCD = TCD.Cells(TCD.Rows.Count, 1).End(xlUp).Row
NbrColTCD = TCD.Cells(4, TCD.Columns.Count).End(xlToLeft).Column - 1
NbrColDestin = TCD.Cells(4, TCD.Columns.Count).End(xlToLeft).Column
Set rg = TCD.Range(TCD.Cells(4, 1), TCD.Cells(lastRowTCD - 1, NbrColTCD))
Arr = rg.Value

'macro
end sub

Salutation

 

[sylvanu]

Bonsoir Iliess,
Il vous faut déclarer ces variables en public, elles seront ainsi visible par tous en lecture et en écriture;
Par ex :

Public WsTvaDue As Workbook
Public ShTVA9 As Worksheet, TCD As Worksheet
Public Arr As Variant
Public rg As Range
Public lastRowTCD As Long, lastRowTva As Long, NbrColTCD As Long, NbrColDestin As Long
Public Pv_Table As PivotTable
Public Pv_Field As PivotField
Public Fltr_KW As String
Sub macro1()
Set WsTvaDue = ActiveWorkbook
Set TCD = WsTvaDue.Worksheets("ANALYSETCD")
Set ShTVA9 = WsTvaDue.Worksheets("TVA9")
Set Pv_Table = TCD.PivotTables("TvaDue")
Set Pv_Field = Pv_Table.PivotFields("TVA-TYPE")
Pv_Table.RefreshTable
Pv_Field.ClearAllFilters
Pv_Field.CurrentPage = "TVA9%"
ShTVA9.Range("A4:Z2000").ClearContents
lastRowTCD = TCD.Cells(TCD.Rows.Count, 1).End(xlUp).Row
NbrColTCD = TCD.Cells(4, TCD.Columns.Count).End(xlToLeft).Column - 1
NbrColDestin = TCD.Cells(4, TCD.Columns.Count).End(xlToLeft).Column
Set rg = TCD.Range(TCD.Cells(4, 1), TCD.Cells(lastRowTCD - 1, NbrColTCD))
Arr = rg.Value
'macro'
End Sub

Ces variables ne doivent être déclarées qu'une seule fois, et en dehors des sub.
La syntaxe de déclaration est la même qu'avec un "dim".

Déclaration de variables (VBA)

Référence de langage VBA pour Office

learn.microsoft.com

 

[Dranreb]

Bonsoir.
Si les trois macros sont dans le même module et qu'elles seules elle les utilisent, elles peuvent aussi être déclarées Private. L'essentiel c'est qu'elles soient globale, c'est à dire déclarées en tête du module, avant toute procédure.

 

[iliess]

Merci beaucoup pour votre réponse