XL 2016 VBA - OLEObjects.add fait perdre les valeurs des variables globales de tous les Modules

[Dudu2]

Bonjour,

J'avais déjà remarqué ce phénomène étrange et excessivement gênant lorsqu'on veut partager des variables entres plusieurs fonctions.
Si on déclare et valorise des variables au niveau d'un ou plusieurs Modules et que dans un des Modules on créé une ComboBox de feuille Active X, toutes ces valeurs sont perdues !

La création de la ComboBox dézingue toutes les variables Modules de TOUS les Modules !

Je cherche un contournement de ce problème car même si on peut s'en sortir avec un Application.OnTime Now après la création de la ComboBox, cette solution ne permet pas de passer des paramètres reçus qui sont précisément les valeurs à placer en variables Module. Et puis de toutes façons, comme tous les Modules sont affectés, c'est irrattrapable !

 

[Dudu2]

sur le net on ne trouve rien sur cette erreur

Si il y en a pas mal des reports de ce problème.
Dont ça chez MS.
Je vais regarder tout ça e espérant trouver une solution.

 

[Dudu2]

C'est un bug et je pense qu'il n'y a rien à faire.
Il faut passer sur un simple Control dont j'ai enregistré la création avec ActiveSheet.DropDowns.Add.
Maintenant, je suis totalement ignorant des propriétés et méthodes de ce truc.

Edit: Bon, c'est à peu près pareil.

Public Const NomComboBox = "MaComboBox"

Sub CréationComboBox()
    Dim Top As Double, Left As Double, Width As Double, Height As Double
    Dim CbB As Object
    
    On Error Resume Next
    'ActiveSheet.Shapes(NomComboBox).Delete
    ActiveSheet.DropDowns(NomComboBox).Delete
    On Error GoTo 0
    
    'Cadre la ComboBox sur la cellule active
    With ActiveCell
        Top = .Top
        Left = .Left
        Width = .Width + 16
        Height = .Height * 1.1
    End With
    
    'Création de la ComboBox Active X
    Set CbB = ActiveSheet.DropDowns.Add(Left, Top, Width, Height)
    CbB.Name = NomComboBox
    CbB.AddItem "Item 1"
    CbB.AddItem "Item 2"
    CbB.AddItem "Item 3"
    CbB.OnAction = "ActionComboBox"
End Sub

Sub ActionComboBox()
    Dim CbB As Object
    
    Set CbB = ActiveSheet.DropDowns(NomComboBox)
    MsgBox CbB.Value
End Sub

Mais je ne vois pas comment on peut pas y gérer des évènements.
Sauf à y associer une Macro qui serait l'équivalent d'un Change(). C'est très limité.

 

[patricktoulon]

Bonjour @mapomme
ben non je suis pas méchant
je dis simplement que cette erreur, je ne l'ai absolument jamais vu ou remarqué et c'est @Dudu2 qui me la fait découvrir
tout le monde sait bien que c'est ce genre d’énigme qui me passionne
ça n'a rien de péjoratif quand je veux l’être(méchant) ça se voit
bon je fait pas dans la dentelle c'est sur

 

[patricktoulon]

@Dudu2 ou passe par 2 names

 

[Dudu2]

ben non je suis pas méchant

T'inquiète c'est une grosse vanne

@Dudu2 ou passe par 2 names

@patricktoulon, quel est ce message énigmatique ?

Je crois que je vais passer par une ComboBox dans un UserForm dans lequel j'aurai retiré le Caption pour coller au mieux à la cellule. C'est dans la perspective de remplacer une liste de validation des données par une ComboBox, ce qui n'est pas trivial. J'ai le code, mais c'est la Combo que je dois adapter après la découverte de ce bug.

 

[patricktoulon]

re
ben au lieu d'avoir 2 variable public tu aura 2 names (du même nom pourquoi pas ) dont tu valorisera le referto
les names ne seront pas effacés
en effet je n'avais pas trouvé tout a l'heure peut être ma recherche n’était pas assez pertinente au niveau de l'intitulé
bon ben c'est plus une enigme alors c'est bien fait exprès selon ce que j'ai lu (it's not a technical bug)sur les versions antérieurs a 2019
il faudra me dire la raison de cette idée saugrenue

 

[Dudu2]

Ah ok des Names du Gestionnaire de noms. C'est une alternative intéressante.
J'avais pensé au Dictionary aussi.

Le truc c'est que le Module de code que je prévois de faire avec cette ComboBox s'intègre avec d'autres modules applicatifs et d'autre modules utilitaires qui ont leurs propres variables Module Private ou Public. Donc ça a beaucoup trop d'impact.

 

[patricktoulon]

Attention @Dudu2 si tu passe par un userform (ajouté dynamiquement) il va te falloir cocher l’accès approuvé dans tes paramètres
et si ce fichier est amené a être utilisé sur plus sieurs pc il n'est pas dit que tout les utilisateurs soient d'accords

et oui tu touche aux vbcomponents module

 

[Dudu2]

il va te falloir cocher l’accès approuvé dans tes paramètre

Tu as raison. Mais je fais faire en sorte que l'utilitaire que je vise à corriger comporte le Module et le UserForm. Dommage d'alourdir mais c'est la seule option qui tient la route pour remplacer l'OLEObject que désormais je n'utiliserai plus.

 

[mapomme]

Bonjour à tous,

Et en créant la Combobox à l'ouverture du classeur ?
Code dans module de ThisWorkbook et le module spécifique ComboBox.
(pas pu vérifier l’existence ou non du bogue puisque je travaille avec 365).

Suivant ce qu'on fait, on pourrait voir si c'est la création de la combobox ou son remplissage qui vide les variables publiques.

 

[patricktoulon]

re
@mapomme
la combobox reste invisible chez moi
pas de message d'erreur

 

[patricktoulon]

re
1er point
et si au lieu d'une combobox tu supprimais /ajoutais une liste de validation dynamiquement
tu aurais le même effet
c'est a dire une liste déroulante placé sur la cellule active
là tu n'aurais plus de soucis
tu tu fait un bloc (IF) dans le change (meme si tu t'en sert pour autre chose )
une variable pour l'activecell au moment de l'ajout de la LV qui est déterminé
et utilisation de intersect dans un bloc IF fermé pour strictement ce intersect
et voila plus de soucis la valeur c'est la target ( variable )
en gros le change de la feuille devient le change de la liste déroulante(tout du moins dans son bloc IF fermé)

2°point
a tu essayé avec une combobox formulaire et non activX en la liant a la cellule active bien sur pour bénéficier du change de worksheet

 

[mapomme]

la combobox reste invisible chez moi
pas de message d'erreur

Allez, je laisse tomber. On ne va faire le boulot de Krosoft en déboguant en fonction chacunes des versions de MS-Office - surtout que Krosoft a arrêté les développements et corrections de MSOfice 2010. Tu as d'ailleurs proposé des pistes alternatives.

 

[Dudu2]

si au lieu d'une combobox tu supprimais /ajoutais une liste de validation dynamiquement
tu aurais le même effet

Non car d'une part dans une ComboBox tu peux mettre tout type de Font et puis j'ai absolument besoin d'une ComboBox pour y faire de la saisie filtrée (genre tu tapes des caractères et la liste se recompose dynamiquement en fonction).

Voici un code qui positionne une ComboBox de UserForm sur une cellule.

Bien sûr le UserForm possède des dimensions (Width & Height) minimales et on ne peut pas toujours matcher les dimensions d'une petite cellule.
Et puis le positionnement sur une cellule est une vraie galère non déterministe selon où est la cellule dans l'écran (Gauche, Droite, Haut, Bas) et selon le Zoom. J'ai appliqué un ratio venu de Pluton pour corriger les variations en largeur et en hauteur (dans mon environnement en tous cas).

J'ai fait du mieux que j'ai pu, pas toujours 100% calé selon le Zoom mais c'est acceptable.

 

[Dudu2]

Fichier corrigé pour les déclarations de l'API en VBA7.