Guru do Excel e Power BI

Enviado: **23 Mar 2021 às 17:14**

Boa tarde

Precisava de uma ajuda porque não sei o que está errado na macro para calculo do número de dias trabalhados entre duas datas.

Na planilha que anexo tenho uma macro que deveria calcular o número de dias uteis entre duas datas e tenho três ranges nomeados que são os feriados e tolerâncias de ponto que preciso descontar, nomeadamente F_NACIONAIS (feriados nacionais), F_MUNICIPAIS (feriados municipais/locais) e TOLERANCIAS (tolerâncias de ponto que funcionam como mais um "feriado")
O que faço é fazer a união deste três ranges nomeados e aplicar a formula do VBA - WorksheetFunction.NetworkDays, só que está a dar erro.

O que estou a fazer de errado?

Obrigado pela ajuda
Jorge Cabral

Enviado: **24 Mar 2021 às 08:12**

Salve, Jorge.
Me parece que não é aceito mais do que um intervalo como parâmetro.

Uma solução seria agrupar os três intervalos na coluna G e utilizar:

Código: Selecionar todos

dt = Application.NetworkDays(dti, dtf, Range("F_NACIONAIS"))

#
Outra solução seria manter como está a lista em G, agrupar as outras duas listas na coluna I e utilizar:

Código: Selecionar todos

dt = Application.NetworkDays(dti, dtf, Range("F_NACIONAIS")) + Application.NetworkDays(dti, dtf, Range("F_MUNICIPAIS")) - Application.NetworkDays(dti, dtf)

Enviado: **24 Mar 2021 às 08:24**

Bom dia Caro Osvaldo

Mais uma vez obrigado pela sua ajuda.

De facto tb acho que só aceita um intervalo - The list can be either a range of cells that contains the dates or an array constant of the serial numbers that represent the dates. - só que, como também aceita um array , tem como agregar as três colunas de datas num só array?

Enviado: **24 Mar 2021 às 08:58**

Me parece que sim, e nesse caso acho que você precisaria montar no próprio código uma matriz com as datas dos feriados, sem referenciar os intervalos da planilha. Ou talvez carregar alguma Collection a partir das listas contidas na planilha.

Enviado: **24 Mar 2021 às 14:55**

@osvaldomp,

Depois de muitas tentativas cheguei a este código, mas não sei é possível melhorá-lo e/ou simplificá-lo, dá para dar uma vista de olhos?

Código: Selecionar todos

Function ReadRangeRowsToCollection(r As Range, c As Collection) As Collection
    Dim iRow   As Long
    Dim iCol   As Long
    Dim rangeArr As Variant
    Dim rowArr As Variant
    '
    rangeArr = r.Value
    
    'Dados para a Collection
    For iRow = 1 To r.Rows.Count
        ReDim rowArr(1 To r.Columns.Count)
        For iCol = 1 To r.Columns.Count
            rowArr(iCol) = rangeArr(iRow, iCol)
        Next iCol
        c.Add rowArr
    Next iRow
    
    Set ReadRangeRowsToCollection = c
End Function

Sub Test_Coll()

    Dim ws     As Worksheet
    Dim Rng    As Range
    Dim collectionAll    As Collection, c1 As Collection, c2 As Collection, c3    As Collection
    Dim dArr()   As Long

    Set ws = Worksheets("SETTINGS")
    
    Set collectionAll = New Collection
    Set c1 = New Collection
    Set c2 = New Collection
    Set c3 = New Collection
    
    
    Set c1 = ReadRangeRowsToCollection(ws.Range("F_NACIONAIS"), collectionAll)
    Set c2 = ReadRangeRowsToCollection(ws.Range("F_MUNICIPAIS"), collectionAll)
    Set c3 = ReadRangeRowsToCollection(ws.Range("TOLERANCIAS"), collectionAll)
    
    Dim aux()  As Variant: ReDim aux(0 To collectionAll.Count - 1): ReDim dArr(0 To collectionAll.Count - 1)
    Dim i      As Integer
    For i = 1 To collectionAll.Count
        aux(i - 1) = collectionAll.Item(i)
    Next
    
    dti = #4/1/2021#
    dtf = #4/30/2021#
    
    For i = 0 To collectionAll.Count - 1
        dArr(i) = DateSerial(Year(aux(i)(1)), Month(aux(i)(1)), Day(aux(i)(1)))
    Next i
    
    dt = WorksheetFunction.NetworkDays(dti, dtf, dArr)
    MsgBox dt

End Sub

Obrigado

Enviado: **25 Mar 2021 às 12:46**

Salve, Jorge.
Carregando uma matriz com as datas dos três intervalos funciona aqui. Resultado em L12.

Código: Selecionar todos

Sub ContaDiasDeTrabalho()
 Dim dArr() As Long, dt As Long, c As Range, i As Long, dti As Date, dtf As Date, rng As Range
  Set rng = Range("F_NACIONAIS,F_MUNICIPAIS,TOLERANCIAS")
  ReDim dArr(rng.Cells.Count)
  For Each c In rng
   dArr(i) = DateSerial(Year(c.Value), Month(c.Value), Day(c.Value))
   i = i + 1
  Next c
  dti = #4/1/2021#
  dtf = #4/30/2021#
  dt = Application.NetworkDays(dti, dtf, dArr)
  [L12] = dt
End Sub

Enviado: **25 Mar 2021 às 14:24**

@osvaldomp,

Com a sua dica anterior fui pesquisar as Collections e com uns códigos que fui encontrando montei a rotina acima, mas o seu código está clean e muito simples, como sempre. TOP!

Muito obrigado mais uma vez
Jorge

Guru do Excel e Power BI

Dias trabalhados entre Datas - RESOLVIDO

Dias trabalhados entre Datas - RESOLVIDO

Re: Dias trabalhados entre Datas

Re: Dias trabalhados entre Datas

Re: Dias trabalhados entre Datas

Re: Dias trabalhados entre Datas

Re: Dias trabalhados entre Datas

Re: Dias trabalhados entre Datas