VBA - Excel - Créer une variable tableau persistante

Version imprimable

22/09/2023, 10h08
Guiom76

VBA - Excel - Créer une variable tableau persistante

Bonjour,

Controleur de gestion, je vous présente probablement un grand classique que j'ai traité avec mes quelques connaissances et que je souhaiterais aujourd'hui rationnaliser.

Input : je reçois un fichier plat tous les mois avec par ex : id_ste, id_mois, id_type (réel ou budget), id_compte, valeur

Aujourd'hui je me branche sur ce fichier avec un tcd, et je vais chercher avec une fonction personnalisée @mafonction la valeur selon les critères renseignés.

Set b = Sheets(NomBase).PivotTables("TCD").GetPivotData(valeur, id_ste, id_mois, id_type, id_compte)

ValeurMafonction = b.Value

mafonction = ValeurMafonction

Voilà les évolutions que je souhaiterais faire :

1- plutot que de créer un tcd, appeler le fichier txt et l'afficher dans un onglet "proprement" => ça c'est ok ;-)

2- à l'ouverture du fichier, initialiser une variable tableau qui lirait cet onglet et stockerait les résultats

3- restituer cette valeur à chaque appel de mafonction(id_site, id_mois, id_type, id_compte)

4- contrainte, si certains champs ne sont pas renseignés faire la somme de valeur (ex : s'il n'y a pas de mois sommer tous les mois)

J'espère avoir été clair dans l'expression du besoin.

Merci par avance de votre aide.

Le top du top serait de pouvoir se passer de l'onglet qui lit le fichier texte et se brancher directement sur ce dernier, mais ça limite la portabilité de mon fichier (le destinataire n'a pas forcément acces au fichier texte)

Bonjour, voici mon idée. Je considère donc que les données du fichier texte se trouvent sur la feuille "NomBase".
Voici une macro qu'il faut appeler à l'ouverture du classeur et qui charge les données dans un tableau:

Code:

1
2
3
4
5
6
Dim Donnees As Variant
 
Sub ChargerDonnees()
    ' Charger les données de la feuille de calcul dans le tableau
    Donnees = Sheets("NomBase").UsedRange.Value
End Sub

Ensuite la fonction pour calculer les totaux:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
Function MaFonction(id_ste As String, id_mois As String, id_type As String, id_compte As String) As Double
    Dim Total As Double
    Dim i As Long
 
    ' Initialiser le total
    Total = 0
 
    ' Parcourir les données et calculer la somme en fonction des critères
    For i = LBound(Donnees, 1) To UBound(Donnees, 1)
        If (id_ste = "" Or Donnees(i, 1) = id_ste) And _
           (id_mois = "" Or Donnees(i, 2) = id_mois) And _
           (id_type = "" Or Donnees(i, 3) = id_type) And _
           (id_compte = "" Or Donnees(i, 4) = id_compte) Then
            Total = Total + Donnees(i, 5)
        End If
    Next i
 
    ' Retourner le total
    MaFonction = Total
End Function

22/09/2023, 12h40
Guiom76

1 pièce(s) jointe(s)

Bonjour Franc,

Quelle fulgurance, je ne m'attendais pas à un retour si rapide, merci infiniment.

J'ai l'impression de recevoir un cadeau de Noël, votre script est probablement le sésame que j'ai toujours rêvé de faire...

Je n'ai pas dû faire les bons copier coller, la fonction renvoie "#valeur".

En pièce jointe le jeu de test.Pièce jointe 644304
22/09/2023, 13h02
Franc

Ou alors je n'ai pas bien compris. En cellule B5 de ta feuille "Resultats", quel est le résultat attendu et par quelle opération est-il obtenu ?
22/09/2023, 13h22
Guiom76

Dans l'exemple (id_ste = 1;mois = 1; type="R"; compte = 601000) = 4

Si le compte n'est pas renseigné (id_ste = 1;mois = 1; type="R"; compte = TOUS) = 15 (4+11)

Si le mois et le compte n'est pas renseigne (id_ste = 1 & id_type="R") = 16 (4+1+11)

Si rien n'est renseigné = 84
26/09/2023, 12h21
Guiom76

Bonjour Franc,

Votre script fonctionne.

Seul hic, ma façon de charger la variable Donnees, j'ai copier coller la sub ChargerDonnees dans WorkbookOpen

Private Sub Workbook_Open()

Donnees = Sheets("NomBase").UsedRange.Value

End Sub

Mais la variable soit n'est pas chargée soit n'est pas persistante...

Tu as bien déclaré Donnees en dehors de la macro, comme ceci ?
Code:

1 2 3 4 5 6 7 Public Donnees As Variant Private Sub Workbook_Open() Donnees = Sheets("NomBase").UsedRange.Value End Sub

26/09/2023, 14h15
Guiom76

1 pièce(s) jointe(s)

Oui...

Pièce jointe 644463

Si je mets Donnees = Sheets("NomBase").UsedRange.Value dans MaFonction ça marche mais c'est balot ça charge à chaque calcul.

Tu ne dois pas mettre Donnees dans MaFonction puisqu'il est chargé dans WorkBook_Open et comme Donnees est déclaré Public il est reconnu par MaFonction.
J'ai modifié de telle sorte que la fonction prend tout en charge. Il ne faut plus rien déclarer dans Workbook_Open et tu mets la fonction dans un module standard.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
Function MaFonction(id_ste As Variant, id_mois As Variant, id_type As Variant, id_compte As Variant) As Variant
    Static Donnees As Variant
 
    ' Vérifier si les données n'ont pas encore été chargées
    If IsEmpty(Donnees) Then
        ' Charger les données depuis la feuille de calcul
        Donnees = Sheets("NomBase").UsedRange.Value
    End If
 
    Dim Total As Double
    Dim i As Long
 
    ' Initialiser le total et l'indicateur de correspondance
    Total = 0
 
    ' Parcourir les données et calculer la somme en fonction des critères
    For i = LBound(Donnees, 1) To UBound(Donnees, 1)
        If (id_ste = "" Or Donnees(i, 1) = id_ste) And _
           (id_mois = "" Or Donnees(i, 2) = id_mois) And _
           (id_type = "" Or Donnees(i, 3) = id_type) And _
           (id_compte = "" Or (id_compte = "TOUS" Or Donnees(i, 4) = id_compte)) Then
            Total = Total + Donnees(i, 5)
        End If
    Next i
 
        ' Retourner le total
        MaFonction = Total
 
End Function

27/09/2023, 09h18
Guiom76

ça marche nickel, 1000 Mercis Franc :D

Bonne journée !