• Privacywetgeving
    Het is bij Helpmij.nl niet toegestaan om persoonsgegevens in een voorbeeld te plaatsen. Alle voorbeelden die persoonsgegevens bevatten zullen zonder opgaaf van reden verwijderd worden. In de vraag zal specifiek vermeld moeten worden dat het om fictieve namen gaat.

Complexe formule naar VBA

Status
Niet open voor verdere reacties.

Piet Bom

Verenigingslid
Lid geworden
13 nov 2010
Berichten
786
Beste Moderators: Ik twijfel op ik deze topic onder Excel of onder VBA moet plaatsen (als je de vraag doorleest snap je mijn twijfel ;))

Beste Excel specialisten,
Ik heb na veel speurwerk op deze site HelpMij-Excel een complexe formule gevonden die goed werkt.
Nu wil ik die formule omzetten naar VBA omdat ik die functie dan beter kan gebruiken in mijn planningstooltje (zie Blad2)
De betreffende complexe formule staat op sheet: Kalender in cell: N3
Ik wil graag een VBA function in cell: N2 die precies hetzelfde doet met 2 parameters (Einddatum en Duur) om de Startdatum te berekenen.
Deze formule berekent startdatums en wel zodanig dat er rekening gehouden wordt met kalenders met effectieve werktijden en rekent met dagen, uren, minuten en secondes.
Ik heb al 1 conversie van een andere complexe formule (in cell: N7) en de VBA-function: cell N9=Calc_FinDate_INTL(K6;L6) (berekent de Einddatum vanaf een Startdatum en een activiteitduur)
Het is me gelukt, maar heeft me een dag gekost :(
Op sheet: Blad2 staat een toepassing in de vorm van een planning waarvoor ik de VBA-functions voor wil gaan gebruiken (als eenvoudiger alternatief op professionele planningstools)
Ik heb het origineel zo ver mogelijk gestript om alleen het nodige over te houden zodat het probleem duidelijker wordt.
Misschien niet helemaal gelukt, maar toch.....
Mijn vraag is dus hoe kun je het beste te werk gaan om een lange complexe Excel-formule om te zetten naar een VBA-function. (opbreken in logische brokken c.q. deelformules)
Alle tips en ideeën om dat handiger en sneller te doen zijn welkom.
 

Bijlagen

  • Planning_met_uren_en_kalenders_Rev8_helpmij.xlsb
    144,6 KB · Weergaven: 68
Laatst bewerkt:
Neem er een macro van op en pas deze waar nodig aan.
De opgenomen macro van de formule in Kalender.[N3]:
Code:
    ActiveCell.FormulaR1C1 = _
        "=INT((WORKDAY(R[-1]C[-3],-1*(CEILING((R[-1]C[-2]-sec_1000ste-MAX(MIN(1*(WEEKDAY(R[-1]C[-3],2)<6)*MOD(R[-1]C[-3],1),FTime),STime)+FTime)/(FTime-STime),1)-1),Holidays)+MAX(MIN(1*(WEEKDAY(R[-1]C[-3],2)<6)*MOD(R[-1]C[-3],1),FTime),STime)-(R[-1]C[-2]-sec_1000ste)+CEILING(FTime-MAX(MIN(1*(WEEKDAY(R[-1]C[-3],2)<6)*MOD(R[-1]C[-3],1),FTime),STime)+(R[-1]C[-2]-sec_1000ste),FT" & _
        "ime-STime)-FTime+STime)/sec_1+0.5)*sec_1" & ""

Begin met de verwijzingen in R1C1 notatie te wijzigen in de juiste A1 notatie.
Met de originele formule bij de hand is dat een makkie.
 
Laatst bewerkt:
Bedankt voor je reactie, edmoor.
Dat is inderdaad vrij eenvoudig om de formule in een bepaalde cell te krijgen.
Het lijkt mij niet echt nodig om de R1C1 notatie om te zetten naar A1 notatie, want die werken beide hetzelfde.
Ik zou ook die formule m.b.v. een macro kunnen copy/pasten naar de cellen die ik nodig heb.
Ik zie nog niet zo snel hoe ik de aanpassing moet doen om een VBA-functie te krijgen die ik met 2 parameters kan aansturen zo dat de startdatum berekent wordt.
Het handige van een VBA-functie is dat ik die formule eenvoudig kan kopiëren naar de cell waar ik een startdatum wil laten berekenen en dan in edit-mode met de twee cell-verwijzigingen te schuiven naar de parameter-posities. Met de Excel-formule moet ik 5 verwijzigingen opschuiven en dat is vervelend en geeft een grotere kans op fouten.
Verder hoop ik te kunnen testen of een VBA-functie sneller doorrekend dan de gewone Excel-formule, wat nuttig kan zijn bij grotere planningen.

Affijn ik ga nog verder aan de slag met jouw idee (misschien moet het muntje nog vallen :))
 
Ik zou beginnen met een andere formule:

PHP:
=K2+1-NETWORKDAYS.INTL(K2-L2;K2;;Holidays)-MOD(L2;1)
 
Laatst bewerkt:
Da's nogal een verschil! :D
 
Vertaald naar UDF:

in N2:
PHP:
=F_snb(K2;L2)

Code:
Function F_snb(a, b)
  F_snb = Evaluate(Replace(Replace(1 * a & "+1-NETWORKDAYS.INTL(" & 1 * (a - b) & ";" & 1 * a & ";1;Holidays)-MOD(" & 1 * b & ";1)", ",", "."), ";", ","))
End Function

We kunnen in VBA natuurlijk de periode vanuit de einddatum per dag teruglopen naar de einddatum minus de doorlooptijd, waarbij iedeere keeer getest wordt of een datum een weekenddag is of een vrije dag, maar ik vermoed dat diezelfde ingebouwde funktionaliteit als in networkdays.intl in Excel (waarvoor in VBA geen equivalent bestaat) sneller werkt dan een zelf gebouwde VBA-vervanger.



Gebruik van een range:

in N2:
PHP:
=F_snb(K2:M2)

Code:
Function F_snb(sn)
  sn = sn
  sn(1, 3) = Replace(1 * (sn(1, 1) - sn(1, 2)), ",", ".")
  sn(1, 1) = Replace(1 * sn(1, 1), ",", ".")
  sn(1, 2) = Replace(1 * sn(1, 2), ",", ".")
  
  F_snb = Evaluate(sn(1, 1) & "+1-NETWORKDAYS.INTL(" & sn(1, 3) & "," & sn(1, 1) & ",1,Holidays)-MOD(" & sn(1, 2) & ",1)")
End Function
 
Laatst bewerkt:
Allereerst erg knap van snb om die mega-formule te doorgronden en te 'verbeteren' :thumb:

Heb 'm zelf nog iets herschreven (Vind zelf dat "Holidays" als parameter moet worden ingegeven en niet 'vast' in de funktie) en middels worksheetfunction
Code:
Function Piet(a, b, c)
Piet = a + 1 - Application.WorksheetFunction.NetworkDays_Intl(a - b, a, , c) - (b - a * Int(b \ a))
End Function

aanroep:
PHP:
=Piet(K2;L2;Holidays)
 
@E v R

Je kun beter .WorksheetFunction. weglaten.

Int(b\a) is dubbelop: b\a geeft altijd hetzelfde resultaat
 
Met een beetje uitleg waarom :)
Het delen met een \ teken levert altijd een Integer waarde op.
 
Yep, zie het, thanks
qua .WorksheetFunction had intellisense nodig ;) (daarnaast heeft het mijn voorkeur tijdens het schrijven)
 
Naar mijn verwachting zal blijken dat deze VBA varianten langzamer zijn dan dezelfde berekening in Excel functies. VBA is single-threaded, Excel gebruikt alle processoren als dat mogelijk is.
 
Yep, zie het, thanks
qua .WorksheetFunction had intellisense nodig ;) (daarnaast heeft het mijn voorkeur tijdens het schrijven)

Maar daardoor kunnen er onverwachte fouten gegenereerd worden.
Volgens mij heb jij allang geen Intellisense meer nodig :d


Ik vrees dat de eerder geplaatste UDF niet het gewenste resultaat oplevert.

Code:
Function F_snb(sn)
  sn = sn
  sn(1, 3) = Replace(1 * (sn(1, 1) - sn(1, 2)), ",", ".")
  sn(1, 1) = Replace(1 * sn(1, 1), ",", ".")
  sn(1, 2) = Replace(1 * sn(1, 2), ",", ".")
  
  F_snb = Evaluate(sn(1, 1) & "-1-MAX(IF(NETWORKDAYS.INTL(" & sn(1, 1) & "-ROW(1:300)," & sn(1, 1) & ",1,Holidays)=INT(" & sn(1, 2) & "),ROW(1:300)))-MOD(" & sn(1, 2) & ",1)")
End Function
 
Laatst bewerkt:
Woooow, van een hoop (mooie) reacties.
Jullie hebben je er echt in verdiept :)
Ik ga daar vanavond eens voor zitten en 1 voor 1 testen of ik ze kan toepassen in mijn planningstool.
Het ziet er goed en hoopvol uit. Spannend !
Geweldige site die HelpMij.nl en vooral jullie als Helpers :)
Dit wordt WERELDNIEUWS ! (denk ik) (na de uitvinding van het wiel (NL) en het stromend water (BE))
 
Ik zou beginnen met een andere formule:

PHP:
=K2+1-NETWORKDAYS.INTL(K2-L2;K2;;Holidays)-MOD(L2;1)
@snb,
Dat is zeker een hele korte formule.
Maar na de test blijkt dat hij nog niet helemaal goed werkt. Zie bijlage.....
Misschien is er een kleine aanpassing nodig ?


PHP:
=F_snb(K2;L2)
Deze VBA-functie was wel waar ik naar op zoek ben.
Ik bewonder je one-liners.

@E v R,
Dat van die 2 extra parameters vind ik wel een goeie.
Het biedt de mogelijkheid om in een planning met meerdere verschillende kalenders te werken.

@jkpieterse,
Goed om te weten dat de Excel-formules sneller zullen zijn dan VBA-functies.
Ik ga dat later nog eens testen om het verschil te kwantificeren.
Misschien moet ik dus niet zoeken naar een VBA-oplossing, maar naar een compactere Excel-formule (met 2 onafhankelijke variabelen)
Ik ga nog wat stoeien met jullie oplossingen en kom er later op terug.

P.S. Had nog nooit van Intellisense gehoord, laat staan dat ik wist wat het betekend.
 

Bijlagen

  • Planning_met_uren_en_kalenders_Rev8_helpmij2.xlsb
    152,1 KB · Weergaven: 35
Nieuwe VBA-versie zonder de Excelfunktie Networkdays.INTL

In N2:
PHP:
=F_snb_000(K2:L2)

in L2: 122:00:00

Code:
Dim c00

Function F_snb_000(sn)
  sn = sn
  if c00="" then c00 = "|" & Join([transpose(holidays)], "|") & "|"
 
  For j = 1 To 300
     d00 = Fix(sn(1, 1)) - j
     If Weekday(d00, 2) < 6 And InStr(c00, Format(d00, "|0|")) = 0 Then y = y + 1
     If y = Fix(sn(1, 2)) Then Exit For
  Next
  
  F_snb_000 = d00 - CDate(Format(sn(1, 2), "hh:mm:ss"))
End Function
 
Laatst bewerkt:
O sorry snb, die had ik gemist (te laat, te moe :()
Ik heb de laatste functie (een hele andere benadering) net getest en die geeft bij mij niet het beoogde resultaat.

PHP:
Test9: Einddatum = vr 20-7-2018  16:00:00, Duur = 40u, zou Startdatum = ma 16-7-2018  08:00:00 moeten geven, jouw functie F_snb_000(K11:L11) geeft: wo 18-7-2018  08:00:00

Dit is een normale werkweek van 5 dagen per week en 8 uur per dag

Wat zie ik over het hoofd ?
 

Bijlagen

  • Planning_met_uren_en_kalenders_Rev8_helpmij3.xlsb
    152,5 KB · Weergaven: 36
Bij jou beslaat een dag slechts 8 uur; in mijn formule duurt ie 24 uur.

Aangepast:

Code:
Function F_snb_000(sn)
  sn = sn
  c00 = "|" & Join([transpose(holidays)], "|") & "|"
 
  For j = 1 To 300
     d00 = Fix(sn(1, 1)) - j
     If Weekday(d00, 2) < 6 And InStr(c00, Format(d00, "|0|")) = 0 Then y = y + 1
     If y = Fix(sn(1, 2)) * 3 Then Exit For
  Next
  
  F_snb_000 = d00 - CDate(Format(sn(1, 2), "hh:mm:ss"))
End Function
 
Door jullie tips ben ik anders gaan denken.
Het idee van snb om via een loop van de bron-datum naar de doeldatum te gaan, geeft een redelijk snelle oplossing.
Ik kan nog niet zo compact programmeren als jullie VBA-guru's, maar ik heb een goed werkende code gemaakt.
Code:
Function Calc_StartDate(fin As Double, dur As Double, Week_Patroon As String, Holidays As Range) As Double
  STime = Range("STime").Value: FTime = Range("FTime").Value
  sec_1 = Range("sec_1").Value: sec_1000ste = Range("sec_1000ste").Value
  fin = Int(fin / sec_1 + 0.5) * sec_1 'afronden op hele seconde
  dur = Int(dur / sec_1 + 0.5) * sec_1 'afronden op hele seconde
  dt1 = fin
  du1 = 0
'------------------------------------------------------
  If dt1 > Int(dt1) + FTime Then dt1 = Int(dt1) + FTime
'------------------------------------------------------
  If dt1 < Int(dt1) + STime Then
Stap1:
    dt1 = dt1 - 1: wd = Weekday(dt1, vbMonday)
    If Mid(Week_Patroon, wd, 1) = "1" Then GoTo Stap1
    If WorksheetFunction.CountIf(Holidays, Int(dt1)) > 0 Then GoTo Stap1
    dt1 = Int(dt1) + FTime
  End If
'------------------------------------------------------
  If dt1 > Int(dt1) + STime Then
    dt1 = dt1 + 1
Stap2:
    dt1 = dt1 - 1: wd = Weekday(dt1, vbMonday)
    If Mid(Week_Patroon, wd, 1) = "1" Then GoTo Stap2
    If WorksheetFunction.CountIf(Holidays, Int(dt1)) > 0 Then GoTo Stap2
    old_dt1 = dt1
    dt1 = Int(dt1) + STime
    du1 = du1 + (old_dt1 - dt1): du1 = Int(du1 / sec_1 + 0.5) * sec_1
    If du1 >= (dur + 0 * sec_1000ste) Then dt1 = dt1 + (du1 - dur): GoTo Uit1
  End If
'------------------------------------------------------
Stap3:
  dt1 = dt1 - 1: wd = Weekday(dt1, vbMonday)
  If Mid(Week_Patroon, wd, 1) = "1" Then GoTo Stap3
  If WorksheetFunction.CountIf(Holidays, Int(dt1)) > 0 Then GoTo Stap3
  du1 = du1 + FTime - STime: du1 = Int(du1 / sec_1 + 0.5) * sec_1
  If du1 >= (dur + 0 * sec_1000ste) Then dt1 = dt1 + (du1 - dur): GoTo Uit1
  GoTo Stap3
Uit1:
  Calc_StartDate = dt1
End Function
Ik ga hem nog verder testen in een live planning.
Bedankt voor jullie tips.
Als de tests goed verlopen, zal ik deze topic afsluiten
 

Bijlagen

  • Planning_met_uren_en_kalenders_Rev8_helpmij3.xlsb
    158,5 KB · Weergaven: 51
Status
Niet open voor verdere reacties.
Steun Ons

Nieuwste berichten

Terug
Bovenaan Onderaan