Comment maîtriser VBA avancé pour Excel en 2026

Option Explicit

Private pNom As String
Private pAge As Integer

Public Property Get Nom() As String
    Nom = pNom
End Property

Public Property Let Nom(value As String)
    If Len(value) > 0 Then pNom = value Else Err.Raise 1001, , "Nom obligatoire"
End Property

Public Property Get Age() As Integer
    Age = pAge
End Property

Public Property Let Age(value As Integer)
    If value >= 0 And value <= 150 Then pAge = value Else Err.Raise 1002, , "Âge invalide"
End Property

Public Sub AfficherInfos()
    MsgBox "Nom: " & pNom & vbCrLf & "Âge: " & pAge
End Sub

Private Sub Class_Initialize()
    pAge = 0
End Sub

Option Explicit

Sub TesterClassePersonne()
    Dim personnes As Collection
    Set personnes = New Collection
    
    Dim p1 As ClassePersonne
    Set p1 = New ClassePersonne
    p1.Nom = "Dupont"
    p1.Age = 35
    personnes.Add p1
    
    Dim p2 As ClassePersonne
    Set p2 = New ClassePersonne
    p2.Nom = "Martin"
    p2.Age = 42
    personnes.Add p2
    
    Dim i As Integer
    For i = 1 To personnes.Count
        personnes(i).AfficherInfos
    Next i
    
    Set p1 = Nothing
    Set p2 = Nothing
    Set personnes = Nothing
End Sub

Option Explicit

Public Type ErreurInfo
    Numero As Long
    Description As String
    Source As String
    Ligne As Long
End Type

Private pErreurs As Collection

Public Property Get DerniereErreur() As ErreurInfo
    If pErreurs.Count > 0 Then
        Dim last As ErreurInfo
        last = pErreurs(pErreurs.Count)
        DerniereErreur = last
    End If
End Property

Public Sub AjouterErreur(Num As Long, Desc As String, Src As String, Ligne As Long)
    Dim ei As ErreurInfo
    ei.Numero = Num
    ei.Description = Desc
    ei.Source = Src
    ei.Ligne = Ligne
    pErreurs.Add ei
End Sub

Private Sub Class_Initialize()
    Set pErreurs = New Collection
End Sub

Private Sub Class_Terminate()
    Set pErreurs = Nothing
End Sub

Option Explicit

Dim ErreurManager As New ClasseErreur

Sub WorkflowAvecErreurs()
    On Error GoTo GestionErreur
    
    Dim ageTest As Integer
    ageTest = -5  ' Erreur simulée
    
    Exit Sub
    
GestionErreur:
    ErreurManager.AjouterErreur Err.Number, Err.Description, Err.Source, Erl
    MsgBox "Erreur loggée: " & ErreurManager.DerniereErreur.Description
    Resume Next
End Sub

Sub AfficherErreursLoggees()
    MsgBox "Erreurs totales: " & ErreurManager.pErreurs.Count  ' Accès interne pour démo
End Sub

Option Explicit

Private WithEvents wbEvents As Workbook
Private WithEvents wsEvents As Worksheet

Private Sub Workbook_Open()
    Set wbEvents = ThisWorkbook
    Set wsEvents = ThisWorkbook.Sheets(1)
End Sub

Private Sub wbEvents_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Target.Column = 1 Then
        Target.Offset(0, 1).Value = Now
        Application.EnableEvents = False
        MsgBox "Colonne A modifiée à " & Now
        Application.EnableEvents = True
    End If
End Sub

Private Sub wsEvents_Change(ByVal Target As Range)
    If Target.Address = "$A$1" Then
        MsgBox "A1 changée: " & Target.Value
    End If
End Sub

Option Explicit

#If VBA7 Then
    Declare PtrSafe Function SHBrowseForFolder Lib "shell32" _
        (lpBrowseInfo As BROWSEINFO) As LongPtr
    Declare PtrSafe Function SHGetPathFromIDList Lib "shell32" _
        (ByVal pidl As LongPtr, ByVal pszPath As String) As Long
#Else
    Declare Function SHBrowseForFolder Lib "shell32" _
        (lpBrowseInfo As BROWSEINFO) As Long
    Declare Function SHGetPathFromIDList Lib "shell32" _
        (ByVal pidl As Long, ByVal pszPath As String) As Long
#End If

Type BROWSEINFO
    hwndOwner As LongPtr
    pidlRoot As LongPtr
    pszDisplayName As String
    lpszTitle As String
    ulFlags As Long
    lpfn As LongPtr
    lParam As LongPtr
End Type

Function SelectionnerDossier(titre As String) As String
    Dim bi As BROWSEINFO
    Dim pidl As LongPtr
    Dim chemin As String
    
    bi.hwndOwner = 0
    bi.lpszTitle = titre
    bi.ulFlags = 1  ' Nouvelle fenêtre
    
    pidl = SHBrowseForFolder(bi)
    chemin = Space$(512)
    SHGetPathFromIDList pidl, chemin
    SelectionnerDossier = Left(chemin, InStr(chemin, vbNullChar) - 1)
End Function

Sub TesterDossier()
    MsgBox SelectionnerDossier("Choisir dossier")
End Sub