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

di gaetano
7 commenti

Abbiamo 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.

 

Scelti per te

Subscribe
Notificami
guest
7 Commenti
più nuovi
più vecchi più votati
Inline Feedbacks
View all comments
7
0
Would love your thoughts, please comment.x
()
x
Gaetano Cirino e Vittorio Da Sois

Gaetano e Vittorio

CHE LAVORO FARE

Vuoi cambiare lavoro ma non sai cosa fare?

Ti piacerebbe lavorare con entusiasmo?

Centra la Professione