Problème Macro Solveur

Le 11/11/2024 à 13:20

Bonjour je cherche de l’aide pour une macro d’optimisation voici mon objectif

Objectif

Je souhaite créer une macro VBA qui utilise le solveur pour optimiser la répartition des heures de travail mensuelles pour plusieurs ressources, en respectant les contraintes suivantes :

Heures saisonnières par ressource : Chaque ressource a un nombre total d’heures à répartir sur 12 mois.

Heures mensuelles maximales : Chaque mois a un nombre maximal d’heures de travail disponibles pour toutes les ressources combinées.

Données

Onglet “Ressources” :

Colonne A : Ressource (1 à 5)

Colonne B : Heures_saisonnières (56, 34, 23, 12, 14)

Onglet “Budget” :

Colonne A : Mois (1 à 12)

Colonne B : Heures_mensuelles (19, 12, 23, 10, 12, 3, 1, 23, 13, 1, 1, 30)

Contraintes :

Total des heures par ressource : Le total des heures sur 12 mois pour chaque ressource doit être égal aux heures saisonnières spécifiées.

Total des heures par mois : Le total des heures pour toutes les ressources combinées chaque mois ne doit pas dépasser les heures mensuelles spécifiées.

La macro que j’ai faite est la suivante :

Sub OptimisationAvecSolver()
    Dim wsRessources As Worksheet
    Dim wsBudget As Worksheet
    Dim wsResultat As Worksheet
    Dim i As Integer, j As Integer
    Dim nRessources As Integer, nMois As Integer
    Dim heuresSaisonnieres() As Integer
    Dim heuresMensuelles() As Integer

    ' Définir les feuilles de calcul
    Set wsRessources = ThisWorkbook.Sheets("Ressources")
    Set wsBudget = ThisWorkbook.Sheets("Budget")

    ' Créer une feuille de résultat
    Set wsResultat = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
    wsResultat.Name = "Resultat"

    ' Définir le nombre de ressources et de mois
    nRessources = 5 ' Nombre de ressources
    nMois = 12 ' Nombre de mois dans l'année

    ' Charger les heures saisonnières (Ressources!B2:B6) comme entiers
    ReDim heuresSaisonnieres(1 To nRessources)
    For i = 1 To nRessources
        heuresSaisonnieres(i) = Int(wsRessources.Cells(i + 1, 2).Value) ' Forcer à un entier
    Next i

    ' Charger les heures mensuelles (Budget!B2:B13) comme entiers
    ReDim heuresMensuelles(1 To nMois)
    For j = 1 To nMois
        heuresMensuelles(j) = Int(wsBudget.Cells(j + 1, 2).Value) ' Forcer à un entier
    Next j

    ' Créer la feuille de résultat et initialiser les cellules avec 0
    For i = 1 To nRessources
        wsResultat.Cells(i + 1, 1).Value = wsRessources.Cells(i + 1, 1).Value ' Nom des ressources
        For j = 1 To nMois
            wsResultat.Cells(i + 1, j + 1).Value = 0 ' Initialiser les heures allouées à 0
        Next j
    Next i

    ' Initialiser le solveur
    SolverReset
    SolverOptions Precision:=0.0001, MaxTime:=100, Iterations:=1000 ' Réduire la précision et limiter le temps et les itérations

    ' Ajouter les variables de décision (heures allouées à chaque ressource chaque mois) comme entiers
    For i = 1 To nRessources
        For j = 1 To nMois
            SolverAdd CellRef:=wsResultat.Cells(i + 1, j + 1), Relation:=3, FormulaText:="0" ' Initialisation avec 0
            SolverAdd CellRef:=wsResultat.Cells(i + 1, j + 1), Relation:=1, FormulaText:="int" ' Forcer l'entier
        Next j
    Next i

    ' Ajouter les contraintes de budget mensuel
    For j = 1 To nMois
        SolverAdd CellRef:=wsResultat.Range(wsResultat.Cells(2, j + 1), wsResultat.Cells(nRessources + 1, j + 1)), Relation:=2, FormulaText:=heuresMensuelles(j) ' Heures mensuelles
    Next j

    ' Ajouter les contraintes de heures saisonnières par ressource
    For i = 1 To nRessources
        SolverAdd CellRef:=wsResultat.Range(wsResultat.Cells(i + 1, 2), wsResultat.Cells(i + 1, nMois + 1)), Relation:=2, FormulaText:=heuresSaisonnieres(i) ' Heures saisonnières
    Next i

    ' Définir la fonction objectif : maximiser la somme des heures allouées
    SolverOk SetCell:=wsResultat.Cells(nRessources + 2, nMois + 2), MaxMinVal:=1, ValueOf:=0, ByChange:=wsResultat.Range(wsResultat.Cells(2, 2), wsResultat.Cells(nRessources + 1, nMois + 1))

    ' Résoudre le problème
    SolverSolve UserFinish:=True

    ' Remplir la feuille de résultats avec les résultats optimisés
    For i = 1 To nRessources
        For j = 1 To nMois
            wsResultat.Cells(i + 1, j + 1).Value = Int(wsResultat.Cells(i + 1, j + 1).Value) ' Arrondir à l'entier
        Next j
    Next i
    MsgBox "Optimisation terminée !"
End Sub

EDIT MODERATEUR : mise du code entre balises </> (voir bouton), merci d'y penser à l'avenir.

Mais elle ne marche pas car on me renvoie l’erreur suivante :

Une erreur c’est produite ou la mémoire est saturé.

Savez vous pourquoi et comment na debugger ?

Le 11/11/2024 à 14:51

bonjour,

Tes formulatext doivent être des strings. Ta cellule "objectif" doit contenir une formule, quel calcul faut-il optimiser ?

Sub OptimisationAvecSolver()
    Dim wsRessources As Worksheet
    Dim wsBudget As Worksheet
    Dim wsResultat As Worksheet
    Dim i As Integer, j As Integer
    Dim nRessources As Integer, nMois As Integer
    Dim heuresSaisonnieres() As Integer
    Dim heuresMensuelles() As Integer

    ' Définir les feuilles de calcul
    Set wsRessources = ThisWorkbook.Sheets("Ressources")
    Set wsBudget = ThisWorkbook.Sheets("Budget")

    On Error Resume Next
    ThisWorkbook.Sheets("resultat").Delete
    On Error GoTo 0
    ' Créer une feuille de résultat
    Set wsResultat = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
    wsResultat.Name = "Resultat"

    ' Définir le nombre de ressources et de mois
    nRessources = 5 ' Nombre de ressources
    nMois = 12 ' Nombre de mois dans l'année

    ' Charger les heures saisonnières (Ressources!B2:B6) comme entiers
    ReDim heuresSaisonnieres(1 To nRessources)
    For i = 1 To nRessources
        heuresSaisonnieres(i) = Int(wsRessources.Cells(i + 1, 2).Value) ' Forcer à un entier
    Next i

    ' Charger les heures mensuelles (Budget!B2:B13) comme entiers
    ReDim heuresMensuelles(1 To nMois)
    For j = 1 To nMois
        heuresMensuelles(j) = Int(wsBudget.Cells(j + 1, 2).Value) ' Forcer à un entier
    Next j

    ' Créer la feuille de résultat et initialiser les cellules avec 0
    For i = 1 To nRessources
        wsResultat.Cells(i + 1, 1).Value = wsRessources.Cells(i + 1, 1).Value ' Nom des ressources
        For j = 1 To nMois
            wsResultat.Cells(i + 1, j + 1).Value = 0 ' Initialiser les heures allouées à 0
        Next j
    Next i

    ' Initialiser le solveur
    SolverReset
    SolverOptions Precision:=0.0001, MaxTime:=100, Iterations:=1000 ' Réduire la précision et limiter le temps et les itérations

        ' Ajouter les variables de décision (heures allouées à chaque ressource chaque mois) comme entiers
    solveradd cellref:=wsResultat.Range("B2:M6"), Relation:=3, FormulaText:="0" ' Initialisation avec 0
    solveradd cellref:=wsResultat.Range("B2:M6"), Relation:=1, FormulaText:="int" ' Forcer l'entier
    'For i = 1 To nRessources
    '    For j = 1 To nMois
    '        solveradd cellref:=wsResultat.Cells(i + 1, j + 1), Relation:=3, FormulaText:="0" ' Initialisation avec 0
    '        solveradd cellref:=wsResultat.Cells(i + 1, j + 1), Relation:=1, FormulaText:="int" ' Forcer l'entier
    '    Next j
    'Next i

    ' Ajouter les contraintes de budget mensuel
    For j = 1 To nMois
     rg = wsResultat.Range(wsResultat.Cells(2, j + 1), wsResultat.Cells(nRessources + 1, j + 1)).Address
        solveradd cellref:=rg, Relation:=2, FormulaText:=heuresMensuelles(j) & "" ' Heures mensuelles
    Next j

    ' Ajouter les contraintes de heures saisonnières par ressource
    For i = 1 To nRessources
        solveradd cellref:=wsResultat.Range(wsResultat.Cells(i + 1, 2), wsResultat.Cells(i + 1, nMois + 1)), Relation:=2, FormulaText:=heuresSaisonnieres(i) & "" ' Heures saisonnières
    Next i

    ' Définir la fonction objectif : maximiser la somme des heures allouées
    SolverOk SetCell:=wsResultat.Cells(nRessources + 2, nMois + 2), MaxMinVal:=1, ValueOf:=0, ByChange:=wsResultat.Range(wsResultat.Cells(2, 2), wsResultat.Cells(nRessources + 1, nMois + 1))

    ' Résoudre le problème
    SolverSolve UserFinish:=True

    ' Remplir la feuille de résultats avec les résultats optimisés
    For i = 1 To nRessources
        For j = 1 To nMois
            wsResultat.Cells(i + 1, j + 1).Value = Int(wsResultat.Cells(i + 1, j + 1).Value) ' Arrondir à l'entier
        Next j
    Next i
    MsgBox "Optimisation terminée !"
End Sub
Le 11/11/2024 à 15:04

Merci pour ta réponse enfaite quand j’execute ma macro excel m’envoit un message comme quoi c’est trop lourd pour être exécuter par le solveur sauf que je ne vois pas de boucle infini ni en quoi mon calcul est très lourd…

Le 11/11/2024 à 15:16

Bonjour,

le problème de memoire est résolu dans la correction de code que j'ai mise. (formulatext doit recevoir un string comme paramètre, je convertis en string en ajoutant & ""), j'ai également optimisé la définition des contraintes.

Le 11/11/2024 à 15:34

re,

Solver pour des problèmes pareils ???

Vous avez un fichier ?

Le 11/11/2024 à 17:40

Bonjour,

Je vous partagerais le fichier dans la soirée mon objectif est d’optimiser au maximum pour chaque ressource de production le nombre d’heure en fonction du budget total et des heures minimal a faire par article sur la période.

Pour info après modification le tableau que me renvoie la macro ne contient que des 0.

Dans tous les cas un immense merci pour votre aide.

Le 11/11/2024 à 17:54

re,

Pour info après modification le tableau que me renvoie la macro ne contient que des 0.

Normal, tu n'as pas de formule en N7 (qui est la cellule qui devrait contenir la fonction à optimiser)

Le 11/11/2024 à 19:03

re, salut h2so4,

un essai

10bellini.xlsb (30.73 Ko)
Le 11/11/2024 à 22:18

Bonjour,

Ça marche parfaitement merci beaucoup c’est exactement se que je voulais faire.
il ne me reste plus qu’a le modifier pour pouvoir ajuster le nombre de ressources (exemple 100 ressources) et jouer sur la contrainte ressource ( exemple effectuer les heures en 6 mois au lieu de 12)

Je vous tiens au courant si j’y arrive :)

Encore merci !

Le 12/11/2024 à 12:09

re,

voici la même macro, un peu plus paramétrée (donc à partir du nombre de mois et ressources est fait tout tout seul).

Et on a aussi une limite "minimale" pour les 2

5bellini.xlsb (27.96 Ko)

PS. Il faut savoir que vos 60 variables "integer", c'est presque le max, si c'est encore plus lourd, Solveur se perd dans les branches, mais vous constaterez cela vous-même .

Rechercher des sujets similaires à "probleme macro solveur"