Conserver valeur variable publique

[Chalafraiz]

Bonjour,

Je reviens vers vous parce que je rencontre un obstacle bête et méchant.

Je travaille sur une optimisation de tableur. C'est un fichier de budgets sur une plage d'années ; plusieurs onglets contiennent ces années pour la comptabilité, et il est nécessaire de les modifier à chaque fois que l'on créé un nouveau fichier budget (en copier-coller).

Pour pallier à ce problème, j'ai créé deux cellules nommées, Plage_Budget et Annee1 qui sont paramétrées à chaque lancement du workbook comme tel :

Option Explicit
Private Sub Workbook_Open()
' Déclaration des variables pour l'extraction de la plage de budget
Dim Plage As String
Dim Esp As Integer
Dim length As Double

' Extraction de la plage d'années du nom du tableur
Repname = ActiveWorkbook.Name
Esp = InStrRev(Repname, " ")
length = Len(Repname)
Plage = Left(Repname, (length - Esp + 1))

' Vérifie si la cellule Plage_Budget contient la bonne plage
If Sheets("Parametrage").Range("Plage_Budget") <> Plage Then
' Modification de la plage
Sheets("Parametrage").Range("Plage_Budget") = Plage

' Extraction de la première année de la plage
Sheets("Parametrage").Range("Annee1") = Left(Plage, 4)

' Renommer les feuilles contenant des plages
Sheet37.Name = Plage & " Summary 1"
Sheet12.Name = Plage & " Summary 2"
Sheet25.Name = Plage & " Summary 3"

' Activation de la page d'accueil
Sheets("Accueil").Activate
End If
End Sub

Le tableur commencera toujours par 20XX - 20XX.
Repname est une variable déclarée publique en Module 1 (bien sur en dehors de toute procédure).

Lorsque je lance test2() (Module 1), Repname a bien conservé sa valeur.
Quand je lance test3() (Module 2), puis que je relance test2(), alors la Msgbox n'affiche plus rien : RepName a perdu sa valeur.

Comment faire pour la conserver ?
Dois-je obligatoirement réécrire Repname = ActiveWorkbook.name au début de la macro test3(), qui nécessite cette variable ?

Pour info, la macro test3() ouvre plusieurs autres Workbook, il est donc essentiel que je puisse garder le nom du premier Workbook pour pouvoir switcher entre les tableurs.

Merci d'avance pour vos lumières !

 

[Dranreb]

Re : Conserver valeur variable publique

Bonjour
Je n'ai retenu de vos explications confuses que la dernière phrase.
Il n'est pas nécessaire de connaitre le nom du classeurs qui porte la macro: l'expression Workbook qui le représente est toujours ThisWorkbook. De plus il est inutile de l'utiliser pour cibler ses feuilles: les noms des objets Worksheet qui y correspondent, tels qu'ils figurent dans la rubrique "Miscrosoft Excel Objets" du projet, à gauche du nom Excel de la feuille rappelé entre parenthèses, sont toujours aussi connus.

 

[Chalafraiz]

Re : Conserver valeur variable publique

Bonjour,

J'ai modifié mon message mais ce n'est pas évident à expliquer...

J'ai essayé de reproduire un exemple de fichier. Le lien du Shell ne fonctionne pas mais c'est pour l'exemple, sinon c'est un .bat que j'ouvre et qui fonctionne bien dans mon fichier budget.

(Vous allez peut être me dire que c'est parce que le lien ne fonctionne pas que l'on perd la valeur de Repname, mais dans mon fichier de travail, le lien fonctionne (le .bat se lance) et je perds quand même la valeur de Repname)

Dranreb : vous me dites donc que :
Workbook(Repname).sheets(Outsheet).cells(i,10) = Workbook(Exactname).sheets("Forecast").cells(roww, coll)

peut être remplacé par :
sheets(Outsheet).cells(i,10) = sheets("Forecast").cells(roww, coll)
?

 

[Dranreb]

Re : Conserver valeur variable publique

Non je pense plus généralement que vous n'avez pas du tout besoin de Repname.
Si c'est pour l'utiliser dans une expression Workbooks(Repname), cette expression serait avantageusement remplacée par ThisWorkbook. Je ne vois ni codename ni nom de feuille Outsheet dans votre classeur, mais par exemple simplement Feuil2 pourrait remplacer Workbooks(Repname).sheets("Summary 1") même si c'est un autre classeur qui est actif.
Si votre Outsheet est une variable qui contient un nom Excel de feuille et non un nom VBA d'objet Worksheet, il faut ThisWorkbook.Workhseets(Outsheet).
Pour le classeur extérieur, si vous avez beaucoups de cellules à récupérer, n'hésitez pas à déclarer des variables de type Workbook, Worksheet et Range pour ne pas répéter et recalculer constamment les expressions qui les définissent.
Il vaut toujours mieux noter les objets eux même dans des variables de leurs types que leurs noms dans des String. J'ai déjà vu (quelle ânerie !) Workbooks(ThisWorkbook.Name).Activate au lieu de tout simplement ThisWorkbook.Activate

 

[Chalafraiz]

Re : Conserver valeur variable publique

Repname me sert dans un premier temps pour sortir le nom du tableur afin de donner sa valeur à la cellule "Plage_Budget".

Le code suivant est un exemple de ce que je peux trouver dans mon fichier initial (que je n'ai pas le droit de partager). Je vais quand même vous copier un extrait pour vous montrer dans quoi je me sers de Repname, et vous me direz si effectivement dans ce cas il serait possible de négliger le nom des workbook (attention, ce code n'a pas encore été optimisé et il est assez "moche").

' #### Proj Type #####
' activate the correct sheet (C'est le Workbook ouvert via la macro)
Workbooks(exactname).Activate
Sheets("forecast").Select
'get the project type
Range(Cells(4, 2), Cells(4, 2)).Select
Selection.Copy
' move to report and paste data (c'est le workbook de travail)
Windows(Repname).Activate
Sheets("Eng Resource").Select
Range(Cells(ii + 3, 3), Cells(ii + 3, 3)).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

A savoir que exactname varie (on ouvre plusieurs tableurs un par un), tandis que Repname est toujours le même, à savoir le tableur d'où on lance la macro.


Cette macro est assez lourde toujours un peu codée comme ça, et pour info sur les 41 variables utilisées dedans, seules 7 sont déclarées... Et le tout est en anglais. Autant dire que je me régale d'avance...

 

[Dranreb]

Re : Conserver valeur variable publique

Vous n'avez pas besoin de Select et d'Activate pour ça. Repérez le nom VBA de l'objet Worksheet correspondant à la feuille "Eng Resource" du classeur qui porte la macro et faites une affectation genre :

FeuilX.Cells(ii + 3).Value = Workbooks(exactname).Worksheets("forecast").[B4].Value

À savoir que si le classeur dont le nom est dans excactname vient d'être ouvert, normalement il est actif, alors vous devriez pouvoir remplacer Workbooks(exactname) par ActiveWorkbook

 

[Chalafraiz]

Re : Conserver valeur variable publique

Je me doutais que je pouvais faire une simplification, mais de la à la faire en une ligne... Chapeau, et merci !

Je pensais que ne pas citer le tableur Repname pouvait causer une erreur (ce qui doit être le cas quand on active le workbook exactname), mais du coup, en gardant Repname actif cela ne gêne pas, très bien.

Edit : après votre modification, du coup je doute... Mais alors, s'il n'est pas nécessaire d'écrire workbook(Repname).sheets("Eng Resource"), pourquoi est-il nécessaire d'écrire Workbook(exactname).sheets("forecast") ?

A moins que les deux puissent être enlevés...

 

[Dranreb]

Re : Conserver valeur variable publique

Parce que les objets Worksheet d'un autre projet VBA que celui qui porte la macro ne lui sont pas connus.
Préférez la collection Worksheets à la Sheets. Cette dernière est une collection d'objets banalisés pouvant comporter aussi bien des Chart que des Worksheet, d'où forcément un peu plus de liaisons tardives.
Les noms d'objets Worksheet du projet VBA (on les appelle aussi parfois les CodeName bien que ceci soit en réalité une propriété String qui reproduit ce nom) sont reconnus uniquement pour les feuilles du classeur qui porte la macro, quel que soit le classeur actif.

 

[Chalafraiz]

Re : Conserver valeur variable publique

D'accord.

Merci pour ces précisions.

Sinon, par rapport au problème principal de perte de la valeur d'une variable publique, comment faire pour pallier à ce problème ? Qu'est-ce qui justifie que la valeur de la variable soit effacée suite au lancement d'une procédure qui ne l'utilise pas ?

Je vous demande cela afin d'éviter de rencontrer le même problème avec une autre variable.

 

[Dranreb]

Re : Conserver valeur variable publique

Une variable Public n'est qu'un espace de mémoire, donc volatile. Une réinitialisation du projet VBA suite à un plantage suffit à l'effacer.

 

[Chalafraiz]

Re : Conserver valeur variable publique

Donc, même si je la définie à chaque lancement du Workbook, elle se supprimera après le lancement d'une autre procédure ?

Pourtant, la macro test2() la conserve bien. C'est au lancement de test3() qu'elle disparaît...

Comment faire dans ce cas pour conserver les valeurs de variables ?


Par contre, si je déclare une constante, sa valeur n'est pas perdue ?

 

[Dranreb]

Re : Conserver valeur variable publique

Mettez un espion sur votre variable en précisant arrêt si la valeur change pour voir à quel moment sa valeur disparaît et examinez la pile. Pour évitez de la perdre lors d'un plantage le mieux c'est d'écrire une fonction qui la calcule dans une variable Private si elle n'est pas initialisée ou qui la reproduit simplement si elle l'est. Marche aussi pour un objet, testé Is Nothing.

 

[Docmarti]

Re : Conserver valeur variable publique

Bonjour.

Une action telle que la suivante provoque également une réinitialisation du projet VBA et des variables.

Public Sub Button_Duplication()

    ActiveSheet.Shapes("CommandButton1").Select
    Selection.Copy
    Range("B5").Select
    ActiveSheet.Paste
     
End Sub

Docmarti

 

[Chalafraiz]

Re : Conserver valeur variable publique

Cela ne viendrait pas du fait que la procédure dans laquelle je définis Repname est privée, "Private" ?

 

[MJ13]

Re : Conserver valeur variable publique

Bonjour à tous

Il n'y a pas de secret .

Pour garder en dur dans un classeur une variable, il faut soit la définir dans les noms (names) ou la mettre sur une feuille. On peut aussi la mettre dans une constante en VBA, mais il ne faudra pas qu'elle change.