Discussion :

[mécano41]

Bonjour à tous,

J'ai fait un petit fichier de ce qui me pose problème. Il s'agit de l'effacement d'un module puis de sa recréation avec insertion du code d'une fonction (pour déclencher l'opération, modifier par ex. l'un des chiffres d'un nombre de la formule).

Tout fonctionne sauf que, après création, on se retrouve systématiquement dans le VBE à la fin de la fonction que l'on vient d'écrire, ceci même en mettant des instructions pour effectuer d'autres opération, se positionner dans une feuille...

C'est d'autant plus bizarre que, le même fichier enregistré en .xls fonctionne normalement sous XL2003 ou 2003 chez moi et chez d'autres personnes...

Quelqu'un aurait-il une explication? Une instruction à ajouter sous XL2010?

Merci d'avance

Cordialement

[MattChess]

Bonjour mécano41,

Voici quelques éléments de réponse à tester sous différentes versions d'Excel.

Comme j'ai dû refaire Replace(), cela veut dire que je n'ai pas pu tester sous Excel 2010.
On pourra remplacer Replace97() par Replace() qui est initialement proposé.

Dans cette maquette, on a préféré utiliser le VBE menu "Tools" > "Reference"
et cocher la bibliothèque [x] Microsoft Visual Basic for Applications Extensibility
afin de déclarer :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

 Dim vbCmpMod As VBIDE.VBComponent

Quand tout fonctionnera, on pourra éventuellement revenir au code d'origine :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

 Dim NouvMod As Object

Pour éviter l'effacement et la recréation du module "ModFonction", on a simplifié en effaçant ses lignes pour insérer le patch.
Car il faut identifier si c'est la suppression et/ou l'ajout d'un module ou l'insertion de lignes de code VBA qui provoquent des comportements différents selon les versions d'Excel.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
Option Explicit
' VBE menu "Tools" > "Reference"
' [x] Microsoft Visual Basic for Applications Extensibility
Public Const rowFormula = 2, colFormula = 2
Public Const nameModule = "ModFonction"
Public Const strSubAfter = "ToDoAfterPatch"
 
Sub PatchModFonction()
Const delayBeforeContinue = 1 'seconde
Dim vbCmpMod As VBIDE.VBComponent
Dim strCode As String, arrCode As Variant, varLine As Variant
 
    On Error Resume Next
    Set vbCmpMod = ThisWorkbook.VBProject.VBComponents(nameModule)
    On Error GoTo 0
    If vbCmpMod Is Nothing Then Stop: End ' nameModule not found
    Application.Calculation = xlCalculationManual
    arrCode = Array( _
        "Option Explicit ' Patched on " + Format(Date) + " " + Format(Time), _
        "", _
        "Function EcartEnergie(Energie As Single, D As Single, e As Single)", _
        "     EcartEnergie=" & Replace97(Cells(rowFormula, colFormula), ",", "."), _
        "End Function")
    strCode = "" ' Sniffer of formula converted in VBA function
    For Each varLine In arrCode: strCode = strCode + varLine + vbCrLf: Next
    With vbCmpMod.CodeModule
        .DeleteLines 1, .CountOfLines
        .InsertLines 1, strCode
    End With
    CloseAllCodeWindows
    Application.OnTime Now + TimeSerial(0, 0, delayBeforeContinue), strSubAfter, , True
End Sub
 
Function Replace97(ByVal strWhere As String, ByVal strWhat As String, ByVal strBy As String) As String
Dim indChar As Integer, lenWhat As Integer, lenBy As Integer, strResult As String
 
    strResult = strWhere: lenWhat = Len(strWhat): lenBy = Len(strBy)
    indChar = InStr(strResult, strWhat)
    While indChar > 0
        If indChar > 1 Then
            strResult = Left(strResult, indChar - 1) + strBy + Mid(strResult, indChar + lenWhat)
        Else
            strResult = strBy + Mid(strResult, indChar + lenWhat)
        End If
        indChar = InStr(indChar + lenBy, strResult, strWhat)
    Wend
    Replace97 = strResult
End Function
 
Sub CloseAllCodeWindows()
Dim nbrWindow As Integer
 
    On Error Resume Next
    With Application.VBE.ActiveCodePane.Collection
        nbrWindow = .Count
        If Err.Number <> 0 Then nbrWindow = 0
 
        While nbrWindow > 0
            .Item(1).Window.Close
            If Err.Number <> 0 Then nbrWindow = 0
            nbrWindow = nbrWindow - 1
        Wend
    End With
    On Error GoTo 0
End Sub
 
Sub ToDoAfterPatch()
    Application.ScreenUpdating = False
    Debug.Print "The patch of " + nameModule + " is done."
    Debug.Print "You can access classically to the sheet."
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
End Sub

Tout fonctionne sauf que, après création, on se retrouve systématiquement dans le VBE à la fin de la fonction que l'on vient d'écrire

Testez si CloseAllCodeWindows() a une influence ou non. Dans ce dernier cas, supprimez l'appel.

ceci même en mettant des instructions pour effectuer d'autres opération, se positionner dans une feuille...

Si le "bug" (*) persiste, reportez dans ToDoAfterPatch() les actions à faire après le patch. En guise d'exemple, on a basculé temporairement le flag Application.ScreenUpdating. Cette procédure est lancée une seconde après le patch (tempo à régler). Le but essentiel est de sortir de l'appel de PatchModFonction() et de son contexte particulier d'auto-modification de code VBA.

Usage

Dans la fenêtre d'Exécution immédiate (Ctlr+G) du VBE, copier-coller et valider par ENTER :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

 PatchModFonction

Une seconde après, le message suivant s'affiche dans la fenêtre d'Exécution immédiate :
The patch of ModFonction is done.
You can access classically to the sheet.

Dans la fenêtre projet du VBE, cliquez sur le module ModFonction :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
Option Explicit ' Patched on 07/28/2011
 
Function EcartEnergie(Energie As Single, D As Single, e As Single)
     EcartEnergie = (0.613 - 0.003 * D) * Energie + 0.8931 * D - 8.1166 - e
End Function

Le patch est daté.

(*) Avertissement : toute autre utilisation qu'une mise à jour officielle de ModFonction n'est pas recommandée.
___________

Si la discussion est résolue, vous pouvez cliquer sur le bouton

En bas de ce message s'il vous a apporté des éléments de réponse pertinents, pensez également à voter en cliquant sur le bouton vert ci-dessous.

[mécano41]

Bonjour MAttChess,

Merci beaucoup pour cette explication très détaillée. Je vais essayer d'analyser cela (pas sûr que j'arrive à suivre!). J'ai pensé également à ne modifier que les lignes sans recréer le module mais je n'avais pas trouvé les mots-clefs nécessaires ; je venais de trouver mais dans l'aide, la syntaxe donnée pour effacer est .DeleteLines (1, .CountOfLines) et ça bloquait... Maintenant que j'ai vu la bonne syntaxe sans parenthèses .DeleteLines 1, .CountOfLines dans ton code, cette simple sub fonctionne :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
Sub AjoutModuleEtCode()
Dim Code As String
Dim LigneSuiv As Long
Code = "Function EcartEnergie(Energie as single, D as single, e as single) " & vbCrLf
Code = Code & "     EcartEnergie=" & Replace(Range("B2").Value, ",", ".") & vbCrLf
Code = Code & "End Function"
With ThisWorkbook.VBProject.VBComponents("ModFonction").CodeModule
    .DeleteLines 1, .CountOfLines
    LigneSuiv = .CountOfLines + 1
    .InsertLines LigneSuiv, Code
End With
End Sub

Mais je vais quand même essayer de comprendre ce que tu as fait et qui est probablement nécessaire pour des cas plus compliqués que le mien (s'il y a plusieurs procédures dans le module par ex.)

Encore merci.

Cordialement