Gantt free con Excel – gestire il carico delle risorse con VBA

Gantt con Excel risorse vbaAbbiamo visto nell’articolo Gantt free con Excel – gestire le risorse come creare dei grafici per il carico di lavoro delle risorse assegnate al progetto. Nella versione proposta il numero di risorse era limitato a 7, ma con l’ausilio del VBA (Visual Basic for Application) è possibile aumentare a piacere il numero di risorse assegnate e generare dei grafici dinamici. Vediamo come fare.

Adattare la struttura dei dati

Innanzi tutto preleviamo il file originale dal primo tutorial file gantt-con-excel.xls e salviamolo sul nostro PC. Procediamo quindi alla modifica della struttura dei fogli di lavoro:

  • Selezioniamo il foglio gantt e dalla scheda dati clicchiamo su Separa – Cancella struttura. Questa operazione eliminerà la struttura precedentemente creata e le colonne E – K diventeranno visibili.
  • Cancelliamo le colonne F – K e quindi modifichiamo l’intestazione della colonna K denominandola Risorse
     Gantt con Excel
  • Creiamo un nuovo foglio e rinominiamolo AnagRis
  • La struttura del foglio è la seguente:
    • Giorni: Inseriamo in D1 la formula =gantt!F3 e trasciniamola fino alla colonna IN
    • Risorsa: colonna A
    • Gruppo: colonna B
    • Costo/gg: colonna C
    • Disponibilità: inseriamo la disponibilità delle risorse nell’aera che va dalla colonna D alla IN, dove con il valore 1 rappresentiamo la disponibilità della risorsa, mentre con una cella vuota rappresentiamo l’indisponibilità della stessa.

     

    Gantt con Excel - gestione risorse

Abbinare le risorse ai task

Per abbinare o eliminare una risorsa da un task useremo il Visual Basic:

  • Richiamiamo l’editor VBA con la combinazione di tasti Alt+F11ed inseriamo un modulo ed due User Form dal menu in alto a sinistra
     Gantt con Excel - Modulo

     

  • Dalla finestra Proprietà dei due oggetti rinominiamo il primo Form con il nome GestioneRisorse, il secondo con GraficoRisorse e il modulo con Funzioni.
  • Inseriamo nel Form GestioneRisorse una Casella combinata che chiameremo ElencoRisorse e due pulsanti che chiameremo Inserisci ed Elimina
     Gantt con Excel - gestione risorse
  • Richiamiamo l’editor di codice della maschera facendo doppio click su di essa ed inseriamo le seguenti parti di codice:
    Option Explicit
    
    Private Sub UserForm_Initialize()
        Dim i As Integer
    
        With Sheets("AnagRis")
            i = 3
            While Trim(.Cells(i, 1)) <> ""
                ElencoRisorse.AddItem Trim(.Cells(i, 1))
                i = i + 1
            Wend
        End With
        ElencoRisorse.ListIndex = 0
    End Sub
    
    Private Sub Inserisci_Click()
        'controlla che la la risorsa non sia già stata assegnata a questo task
        If InStr(Trim(ActiveCell.Value), ElencoRisorse.Value & ";") <> 0 Then
            MsgBox "Risorsa già abbinata", vbInformation, "Errore di inserimento"
            Exit Sub
        End If
        'accoda la risorsa a quelle già presenti
        ActiveCell.Value = Trim(ActiveCell.Value) & " " & ElencoRisorse.Value & ";"
    
    End Sub
    
    Private Sub Elimina_Click()
        'non esegue nulla se la risorsa non è presente ed esce dalla routine
        If InStr(Trim(ActiveCell.Value), ElencoRisorse.Value & ";") = 0 Then Exit Sub
    
        'esegue la cancellazione
        ActiveCell.Value = NewString(Trim(ActiveCell.Value), ElencoRisorse.Value & ";")
    
    End Sub

Creare il grafico delle risorse

  • Ora prendiamo il Form GraficoRisorse ed inseriamo una Casella combinata che chiameremo ElencoRisorse e un pulsante che chiameremo VisualizzaGrafico.
  • Richiamiamo l’editor di codice della maschera facendo doppio click su di essa ed inseriamo le seguenti parti di codice:
    Option Explicit
    
    Private Sub UserForm_Initialize()
        Dim i As Integer
    
        With Sheets("AnagRis")
            i = 3
            While Trim(.Cells(i, 1)) <> ""
                ElencoRisorse.AddItem Trim(.Cells(i, 1))
                i = i + 1
            Wend
        End With
        ElencoRisorse.ListIndex = 0
    End Sub
    
    Private Sub VisualizzaGrafico_Click()
        Application.DisplayAlerts = False
        Application.ScreenUpdating = False
    
        If SheetExists("DatiGraph") Then
            Sheets("DatiGraph").Visible = True
            Sheets("DatiGraph").Select
            Cells.Select
            Selection.ClearContents
        Else
            Sheets.Add After:=Sheets(Sheets.Count)
            ActiveSheet.Name = "DatiGraph"
        End If
        Fill_DatiGraph (ElencoRisorse.Value)
    
        Sheets("DatiGraph").Visible = False
    
        Application.DisplayAlerts = True
        Application.ScreenUpdating = True
    
        Unload Me
    End Sub
    
    Private Sub Fill_DatiGraph(risorsa As String)
        Dim i As Integer
        Dim j As Integer
    
        Range("A1") = "Disponibilità"
        Range("A2") = "Carico"
        Range("A3") = "Assegnazione"
        Range("A4") = "Sovrassegnazione"
        Range("A5") = "Attività"
    
    'Disponibilità
        i = 3
        While Sheets("AnagRis").Cells(i, 1) <> ""
            If Sheets("AnagRis").Cells(i, 1) = risorsa Then GoTo finded
            Debug.Print Sheets("AnagRis").Cells(i, 1)
            i = i + 1
        Wend
    finded:
        For j = 4 To 248
            Sheets("DatiGraph").Cells(1, j - 2) = Sheets("AnagRis").Cells(i, j)
        Next j
    'Carico
        Range("B2").Select
        ActiveCell.FormulaR1C1 = "=SUM(R[4]C:R[503]C)"
    'Assegnazione
        Range("B3").Select
        ActiveCell.FormulaR1C1 = "=IF(R[-2]C<=R[-1]C,R[-2]C,R[-1]C)" 'Sovrassegnazione     Range("B4").Select     ActiveCell.FormulaR1C1 = "=IF(R[-2]C-R[-3]C>0,R[-2]C-R[-3]C,0)"
    'Calendario
        Range("B5").Select
        ActiveCell.FormulaR1C1 = "=gantt!R[-2]C[4]"
    'Trascina le formule
        Range("B2:B5").Select
        Selection.Copy
        Range("B2:IL5").Select
        ActiveSheet.Paste
        Range("B5:IL5").Select
        Selection.NumberFormat = "d/m;@"
    
        For i = 6 To 505
            Sheets("DatiGraph").Cells(i, 1) = Sheets("gantt").Cells(i - 2, 1)
            If InStr(Sheets("gantt").Cells(i - 2, 5), risorsa & ";") > 0 Then
                For j = 6 To 250
                    If Sheets("gantt").Cells(i - 2, j).Value = "x" Then
                        Sheets("DatiGraph").Cells(i, j - 4) = 1
                    End If
                Next j
            End If
        Next i
    
        Sheets("GraficoCaricoRis").Select
        ActiveChart.ChartTitle.Select
        ActiveChart.ChartTitle.Text = risorsa
    
    End Sub
  • Inseriamo un nuovo modulo e rinominiamolo “Funzioni”.
    Gantt con Excel - funzione vba
  •  Inseriamo quindi il seguente codice:
    Option Explicit
    
    Function NewString(StartString, ToCancel) As String
        Dim StartStringLen As Integer
        Dim ToCancelLen As Integer
        Dim StartPos As Integer
        Dim NewLeft As String
        Dim NewRight As String
    
        StartStringLen = Len(StartString)
        ToCancelLen = Len(ToCancel)
        StartPos = InStr(StartString, ToCancel)
    
        If StartPos = 1 Then
            NewLeft = ""
        Else
            NewLeft = Left(StartString, StartPos - 1)
        End If
    
        If StartPos + ToCancelLen >= StartStringLen Then
            NewRight = ""
        Else
            NewRight = Right(StartString, StartStringLen - StartPos - ToCancelLen)
        End If
    
        NewString = NewLeft & NewRight
    End Function
    
    Function SheetExists(SheetName As String) As Boolean
        Dim Test As Boolean
        On Error Resume Next
    
        SheetExists = False
        Sheets(SheetName).Visible = True
        Sheets(SheetName).Select
        Test = Sheets(SheetName).Range("A1").Select
    
        If Test Then SheetExists = True
    End Function
    
    Sub LoadGestioneRisorse()
        Load GestioneRisorse
        GestioneRisorse.Show
    End Sub
    
    Sub LoadGraficoRisorse()
        Load GraficoRisorse
        GraficoRisorse.Show
    End Sub
  • Clicchiamo sul Form GraficoRisorse ed eseguiamolo premendo il tasto funzionale “F5”. Selezioniamo una risorsa che sia stata assegnata e quindi clicchiamo su “Visualizza Grafico”. Verrà creato il foglio di lavoro nascosto “DatiGraph”.
  • Torniamo sul file Excel e rendiamo visibile il foglio selezionando “Scopri..” dal menu a tendina richiamabile con il tasto destro una volta posizionati su una scheda.

    Gantt con Excel - scopri folgio

  • Selezioniamo tutti i dati delle prime 5 righe a partire dalla colonna “A” e creiamo un grafico come quello riportato nel file scaricabile o a nostro piacimento. È importante che il foglio del grafico si chiami “GraficoCaricoRis” e che contenga il titolo, in quanto le nostre macro usano questi riferimenti.

Inserire i pulsanti

Per rendere agevole il lavoro dell’utente inseriamo due pulsanti nel foglio “gantt” creando semplicemente due rettangoli con lo strumento disegno (Inserisci – Illuistrazioni – Forme).

Abbiniamo le macro ai due pulsanti selezionandoli e cliccando su “Assegna macro” dal menu a tendina richiamabile con tasto destro del mouse (Attenzione: per far apparire il menu a tendina è necessario selezionare il pulsante e non il suo contenuto).

Gantt con Excel - abbina risorse

Gantt con Excel - assegna macro

Il file creato è più compatto ed è ulteriormente migliorabile inserendo dei controlli sul codice in VBA o modificando la modalità di visualizzazione del diagramma di Gantt sempre agendo attraverso codice VBA.

È possibile scaricare il file qui

Se avete suggerimenti o domande scriveteci! Sarò lieto di rispondervi e di aiutarvi.

Puoi anche approfondire l’argomento leggendo i nostri percorsi di crescita personale e professionale.

 

5 thoughts on “Gantt free con Excel – gestire il carico delle risorse con VBA

  1. molto interessante. complimenti
    sarebbe bello poter scaricare il file funzionante. infatti lo zip contiene un file esempio senza estensione e non usabile.
    grazie

  2. Ciao Ragazzi, innnanzitutto complimenti e grazie per lo sharing!
    Sto aiutando una persona nella gestione della sua piccola azienda (circa 10 dipendenti), che NON ha ad oggi nessun tipo di informatizzazione. Nel tempo che intercorre fra ora e l’inserimento di un piccolo gestionale open source (ogni suggeirmento è gradito – io per ora sto testando Odoo), vorrei suggerirle l’utilizzo del vs GAANT in excel, che oggi ho scaricato testato e trovo veloce, facile e intuitivo.
    Al fine di renderlo più efficace avrei bisogno di chiederVi se potete aiutarmi ad inserire qualche funzione (non dovrebbe essere complesso, ma io non so come farlo) e potrebbe essere utile anche ad altri.
    Vado al punto: gli ordini arrivano in numero di pezzi e con un data di consegna. Viene eseguita una prova di realizzazione con i relativi tempi, con i quali poi si costruisce il piano di lavoro con un progressivo di pezzi al giorno. Sarebbe quindi utile avere una colonna iniziale che identifica il progetto che racchiude diverse attività (es. potrebbe chiamarsi Progetto o Ordine). Poi dopo attività una colonna nella quale inserire i numeri di pezzi da produrre (es. potrebbe chiamarsi Quantità). Una colonna fra Start e End nella quale inserire i tempi di produzione per un pezzo (es. potrebbe chiamarsi Timing). E avere una colonna in anagrafica risorse che considerasse le ore di lavoro per risorsa (es. potrebbe chiamarsi ore lav/gg. A questo punto il programma potrebbe generare i seguenti dati in automatico:
    1) sapendo la data di consegna ed inserendo quindi la data di END, potrebbe generare la data di START che sarà data dalle ore lav/gg diviso Timing per risorsa (ore di lavoro vanno considerate per risorse in modo da calcolare già eventuali differenze di orario).
    2) sul GAANT in corrispondenza del giorno e dell’attività potrebbe comparire il numero di pezzi prodotti al giorno, dato che potrebbe anche prendere in considerazione la famosa disponibilità per risorsa (ore lav + disponibilità)
    3) avendo le attività raggruppate per progetto (che dovrebbero essere inserite in ordine cronologico) dovrebbe poter anche generare le date di START delle singole attività correlate in funzione dei pezzi prodotti nell’attività precedente (questo parte dal presupposto che tutte le attività siano correlate)

    Vi ringrazio fin da ora e resto a disposizione per chiarimenti.
    Sergio

    1. Ciao Sergio, quanto chiedi è fattibile, se abbiamo capito bene cosa chiedi 🙂
      Ti suggeriamo di contattarci privatamente all’indirizzo worklifemanagement@iwolm.com, così possiamo accordarci meglio. Per esempio un problema che potresti incontrare è il livellamento o il numero di ordini da gestire..

  3. Sapreste suggerirmi come posso inserire l’indisponibilità della macchina in un problema single machine scheduling? C’è qualche funzione che permette di escludere determinati intervalli o devo creare una Macro?

    1. Ciao, dagli elementi che ci hai fornito sembra che tu abbia la necessità di risolvere un problema di ottimizzazione. In questo caso parliamo di problemi di Programmazione Lineare e non di Project Management. Se invece ti riferisci ad una risorsa che hai assegnato ad un task, dovresti lavorare sul calendario di disponibilità. In questo caso dipende dal software che stai usando, ma tendenzialmente tutti quelli più strutturati ti danno la possibilità di creare e gestire calendari specifici per risorsa.

Lascia un commento

Il tuo indirizzo email non sarà pubblicato. I campi obbligatori sono contrassegnati *