Stocker une valeur entre deux exécution de macro

Le 09/01/2023 à 10:31

Bonjour,

J'ai une macro toute simple qui modifie la largeur de la colonne sélectionnée.

Elle fonctionne ainsi :

1er clic : largeur = 0.5

2ème clic : largeur = 8

3ème clic : largeur = 35

4ème clic : largeur = 8

Loop

En clic 2 et 4 il faut savoir qu'elle était la largeur précédente donc pour l'instant, je stocke la largeur et l'adresse de ma cellule dans une feuille cachée pour que tout s'applique comme il faut mais je ne trouve pas ça idéal.

Je constate que plus mon fichier est gros, plus le traitement est long puisque le code vient écrire sur mon fichier (interaction "inutile" avec Excel).

Ma question : est-il possible de stocker une variable entre deux macros sans l'écrire dans une feuille afin d'améliorer la rapidité d'exécution ?

Pour l'instant, je les stocke dans une feuille cachée, mais ça ne me parait pas idéal...

Également, si vous avez des remarques pertinentes pour améliorer mon code je suis également preneur :)

Merci à vous :)

Mon code :

Sub WidthH()

On Error GoTo errorHandler

Dim j As Single
Dim k As String, l As String
Dim x As Long
Dim iCalcul As Integer

iCalcul = Application.Calculation

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
ActiveSheet.DisplayPageBreaks = False

x = Selection.Columns.Count

If x > 100 Then
    Choix = MsgBox("La sélection est supérieure à 100 colonnes, procéder ?", vbOKCancel + vbExclamation, "Attention")
    If Choix = 1 Then
        GoTo Macro
    Else
        Application.Calculation = iCalcul
        Application.EnableEvents = True
        ActiveSheet.DisplayPageBreaks = True
        Exit Sub
    End If
Else
    GoTo Macro
End If

Macro:

Call Test8AToolLogH 'Test la présence de ma feuille cachée

Sheets("8AToolLogHide").Range("C23").Value = Selection(1, 1).Address

With Sheets("8AToolLogHide")
    j = .Range("C21").Value
    k = .Range("C22").Value
    l = .Range("C23").Value

    If k <> l Then
        .Range("C21").Clear
        .Range("C22").Clear
    End If

    j = .Range("C21").Value
    k = .Range("C22").Value

    If Selection.ColumnWidth = 0.44 Then
        .Range("C21").Value = 0.44
        .Range("C22").Value = Selection(1, 1).Address
        Selection.ColumnWidth = 8.11
    ElseIf Selection.ColumnWidth = 35.11 Then
        .Range("C21").Value = 35.11
        .Range("C22").Value = Selection(1, 1).Address
        Selection.ColumnWidth = 8.11
    ElseIf j = 0.44 And Selection.ColumnWidth = 8.11 Then
        .Range("C21").Clear
        .Range("C22").Clear
        Selection.ColumnWidth = 35.11
    ElseIf j = 35.11 And Selection.ColumnWidth = 8.11 Then
        .Range("C21").Clear
        .Range("C22").Clear
        Selection.ColumnWidth = 0.44
    Else
        Selection.ColumnWidth = 0.44
    End If

End With

Application.Calculation = iCalcul
Application.EnableEvents = True
ActiveSheet.DisplayPageBreaks = True

Exit Sub

errorHandler:
errorHandler.Show

Application.Calculation = iCalcul
Application.EnableEvents = True
ActiveSheet.DisplayPageBreaks = True

End Sub
Le 09/01/2023 à 13:18

Bonjour geoffroymax,

Ce n'est pas l'écriture d'une unique valeur dans une feuille qui peut poser se problème de lenteur

Utilisez-vous des évènements "Worksheet_Change" ?

A+

Le 09/01/2023 à 13:22

Bonjour Geoffroymax,

En retour, un exemple réalisé sur une feuille Formats, colonne L pour modification de la largeur de la colonne K.

Faire un double-clic sur n'importe quelle cellule de la colonne L.

A adapter selon feuille et colonnes cibles. Macro inséré au sein du classeur, faire clic-droit sur workbook puis copier-coller

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
'Nom de la feuille ciblée et colonne de double-clic, ici la 12ième colonne
If Sh.Name = "Formats" And Target.Column = 12 Then
Cancel = True
'Incrémentation sur double clic sur colonne L. 
[L1] = IIf([L1] < 4, [L1] + 1, 1)
'Largeur de la colonne
[K1].ColumnWidth = Choose([L1], 0.44, 8.11, 35.11, 8.11)
End If
End Sub
Le 10/01/2023 à 12:07

Bonjour à tous,

BrunoM45 : Non, je n'utilise pas ce type d'évènement. Quand j'écris mes variables dans ma feuille cachée, je fais Sheets("8AToolLogHide").Range("C23").Value = xxx, donc pas de select ou autre. Il me semble que c'est la bonne méthode ? J'écris 3 valeurs dans ma feuille cachée. Au début de la macro, j'appelle une autre macro qui vient tester la présence de ma feuille cachée, le code n'est pas très lourd. Si ça détecte la feuille cachée, ça quitte cette macro de détection, sinon ça la créée, mais cet évènement se produit qu'une fois. Donc je ne pense pas que ça alourdisse mon code ?

Sub Test8AToolLogH()

Dim F As Worksheet

On Error Resume Next
Set F = Sheets("8AToolLogHide")
If Not F Is Nothing Then
    Exit Sub
Else
    'Présentation hors sujet
End If 

End sub

X Cellus : Merci pour le code :) En revanche, je ne maîtrise pas très bien ce type de code. Est-ce possible de remplacer le double clic par un raccourci clavier et de remplacer la colonne 12 par les colonnes de .selection ? Actuellement, j'utilise CTRL+SHIFT+W.

Le 12/01/2023 à 12:24

Bonjour Geoffroymax

Il est bien sur possible de passer par une séquence de touches. Mais préférable que ce soit ALT + w, sauf si déjà assignée.

Les raccourcis Excel utilisent beaucoup les touches CTRL et SHIFT.

Donc voilà, pour l'attribution, celui-ci peut être dans l'événement Workbook_Open puis être annulé dans le code l'événement Workbook_Beforeclose

Application.OnKey "%w", "LargKol"

Cela afin de lancer la macro LargKol ci-dessous. A placer dans un module. Mettre 3 en D1 au départ pour débuter avec 0,44 de largeur.

Sub LargKol()
'La colonne C pouvant prendre une valeur très basse, il est préférable d'agir d'après la colonne D
'Nom de la feuille ciblée et colonne (ici 4) suivant la colonne C
If ActiveSheet.Name = "Formats" And ActiveCell.Column = 4 Then
'Incrémentation sur la séquence de touches couplée ALT w
'Possibilité de choisir une autre cellule que D1
[D1] = IIf([D1] < 4, [D1] + 1, 1)
'Modification de la largeur de la colonne C selon valeur de D1
[C1].ColumnWidth = Choose([D1], 0.44, 8.11, 35.11, 8.11)
End If
End Sub

Ce sera bien la colonne C qui verra sa largeur modifiée. Même en étant sur une cellule de la colonne D

Si malgré tout c'est sur la colonne C que tu veux placer ton clic, change par ActiveCell.Column = 3 then

Le 16/01/2023 à 13:58

X Cellus : Merci ! J'ai gardé ça comme modèle, je n'arrivais pas à stocker la valeur autrement donc tant pis

Rechercher des sujets similaires à "stocker valeur entre deux execution macro"