"Dé grootste en gratis computerhelpdesk van Nederland"
-
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.
Macro's in Excel 2010 vs Excel 2016
- Onderwerp starter anton44
- Startdatum
- Status
snb
Verenigingslid
- Lid geworden
- 12 jun 2008
- Berichten
- 19.710
Een bestand met in ieder record een gelijk aantal velden:
Code:
Sub M_snb()
sn = Split(Replace(CreateObject("scripting.filesystemobject").opentextfile("J:\download\csv_voorbeeld.csv").readall, Chr(34), ""), vbCrLf)
With CreateObject("scripting.dictionary")
For j = 0 To UBound(sn) - 1
.Item(.Count) = Split(sn(j), ",")
Next
sp = Application.Index(.items, Evaluate("row(1:" & .Count & ")"), Array(1, 3, 5, 7, 9, 2, 14))
End With
Sheet1.Cells(1).Resize(UBound(sp), UBound(sp, 2)) = sp
End Sub
anton44
Verenigingslid
- Lid geworden
- 20 mei 2005
- Berichten
- 1.597
Ik krijg het .csv bestand van de bank binnen met 15 kolommen. Voor mijn gebruik voeg ik daar hulpkolommen bij en rubrieken zoals budgetcodes met de daarbij behorende tellingen. Hierdoor ontstaat een tabel van 56 kolommen.
De code die je hierbij aanlevert kan ik niet plaatsen - mijn gebrek aan VBA kennis
CSV RABO importeren
Hallo Anton,
Het van jou ontvangen csv bestand van Bank: krijg jij een gelijk csv bestand zoals ik die van de Rabo krijg? Of had je 'tekst naar kolommen'al uitgevoerd?
Bijgaand 2 files
- csv bestand
- Financieel bestand
Ik heb mijn bestand versimpeld en aangepast om op het forum te zetten, evenals de csv file uitsluitend dummy info.
Plaats beide bestanden op je bureaublad en kijk eerst eens in de financiële file.
De knopjes op het blad Contol spreken voor zich. Daaronder een kleine DBase (om tijdens het overzetten van de info de korte naam en de code op te zoeken).
Rechts daarvan tellingen welke ik weer op andere sheets gebruik. Betreft een complete financiële administratie van een vereniging (incl Balans en V&W rekening + nog meer).
Kijk maar eens of je er iets aan hebt.
Groeten
Bekijk bijlage Trainers Financien.xlsm
Bekijk bijlage CSV_Trainers.csv
Hierbij de code
Hallo Anton,
Het van jou ontvangen csv bestand van Bank: krijg jij een gelijk csv bestand zoals ik die van de Rabo krijg? Of had je 'tekst naar kolommen'al uitgevoerd?
Bijgaand 2 files
- csv bestand
- Financieel bestand
Ik heb mijn bestand versimpeld en aangepast om op het forum te zetten, evenals de csv file uitsluitend dummy info.
Plaats beide bestanden op je bureaublad en kijk eerst eens in de financiële file.
De knopjes op het blad Contol spreken voor zich. Daaronder een kleine DBase (om tijdens het overzetten van de info de korte naam en de code op te zoeken).
Rechts daarvan tellingen welke ik weer op andere sheets gebruik. Betreft een complete financiële administratie van een vereniging (incl Balans en V&W rekening + nog meer).
Kijk maar eens of je er iets aan hebt.
Groeten
Bekijk bijlage Trainers Financien.xlsm
Bekijk bijlage CSV_Trainers.csv
Hierbij de code
Code:
Option Explicit
'Option Private Module
Dim MyPath As String
Dim MyDownloads As String
Dim tFilename As String
Dim sFilename As String
Dim mFilename As String
Dim LastRow As Long
Sub FINANCIEN_VERWERKEN()
tFilename = "YOUR NAME"
sFilename = ThisWorkbook.Name
mFilename = "CSV_Trainers.csv"
MyPath = ThisWorkbook.Path & "\"
MyDownloads = "C:\Users\" & Environ("Username") & "\Desktop\"
Application.ScreenUpdating = False
If Dir(MyDownloads & mFilename) = "" Then
MsgBox "Er is GEEN nieuwe informatie van de BANK beschikbaar." & vbNewLine & vbNewLine & "", vbInformation, tFilename
Exit Sub
Else
'csv van komma gescheiden naar kolommen
Workbooks.OpenText Filename:=MyDownloads & mFilename, Origin:=xlWindows
Columns("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1 _
), Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array _
(20, 1), Array(21, 1), Array(22, 1), Array(23, 1), Array(24, 1), Array(25, 1), Array(26, 1)) _
, TrailingMinusNumbers:=False
'opslaan als temp.xlsx, bewerken en formule plaatsen
ActiveWorkbook.SaveAs Filename:="C:\Users\Admin\Desktop\RABO_TEMP.xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
Range("B:B,C:C,D:D,E:E,H:H,K:K,L:L,M:M,N:N,R:R,U:U,V:V,X:X,Y:Y,Z:Z").Delete Shift:=xlToLeft
Rows("1:1").Delete Shift:=xlUp
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
Union(Columns(2), Columns(4), Columns(5), Columns(6), Columns(7)).Insert Shift:=xlToRight
Range(Cells(1, 2), Cells(LastRow, 2)).FormulaR1C1 = "=IF(RC[-1]=""NL15RABO0123456789"",""rabo"",""rabo"")"
Range(Cells(1, 5), Cells(LastRow, 5)).FormulaR1C1 = "=IF(LEFT(RC[-1],1)=""-"",RIGHT(RC[-1],LEN(RC[-1])-1),RC[-1])"
Range(Cells(1, 6), Cells(LastRow, 6)).FormulaR1C1 = "=IF(LEFT(RC[-2],1)=""-"",RC[-1]*-1,RC[-1]*1)"
Range(Cells(1, 6), Cells(LastRow, 6)).Style = "Comma"
Range(Cells(1, 7), Cells(LastRow, 7)).FormulaR1C1 = "=UPPER(RC[3])"
Cells.Copy
Cells.PasteSpecial Paste:=xlPasteValues
'geschikt maken c.q. positie kolommen gelijk aan financiele file
Range(Cells(1, 8), Cells(LastRow, 8)).Cells.Replace What:=" ", Replacement:="", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Columns("A:A").Delete Shift:=xlToLeft
Columns("C:D").Delete Shift:=xlToLeft
Range("G:K,M:M").Delete Shift:=xlToLeft
Columns("F:F").Cut
Columns("C:C").Insert Shift:=xlToRight
Columns("D:E").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("F:F").Cut
Columns("H:H").Insert Shift:=xlToRight
Columns("H:H").Delete Shift:=xlToLeft
'copy values van temp naar financiele file
Application.Goto Reference:=Workbooks(sFilename).Worksheets("BOEKINGEN").Cells(2, 1)
ActiveSheet.Range("E2").Name = "copy_end" 'is om later de formules in de range nieuwe boekingen om te zetten naar values
Do Until Range("RABO_TEMP.xlsx!A1") = 0
Application.Goto Reference:=Workbooks(sFilename).Worksheets("BOEKINGEN").Cells(2, 1)
Rows(2).Copy
Rows(2).Insert xlDown
Windows("RABO_TEMP.xlsx").Activate
Range(Cells(1, 1), Cells(1, 8)).Copy
Application.Goto Reference:=Workbooks(sFilename).Worksheets("BOEKINGEN").Cells(2, 1)
Selection.PasteSpecial xlValues
Windows("RABO_TEMP.xlsx").Activate
Rows(1).Delete xlUp
Loop
'alle nieuwe boekingen zitten nu in financiele file en formules (voor korte naam en code) worden geplaatst
Application.Goto Reference:=Workbooks(sFilename).Worksheets("BOEKINGEN").Cells(2, 1)
Range(Cells(2, 4), Cells(2, 4).End(xlDown).Offset(-1, 0)).FormulaR1C1 = "=IF(ISERROR(VLOOKUP(MID(RC[2],1,10),DBASE,3,0))=TRUE,""?"",VLOOKUP(MID(RC[2],1,10),DBASE,3,0))"
Range(Cells(2, 5), Cells(2, 5).End(xlDown).Offset(-1, 0)).FormulaR1C1 = "=IF(RC[-1]=""?"",""?"",IF(RC[-4]=""rabo bsr"",""-"",IF(RC[2]<0,VLOOKUP(RC[1],DBASE,4),VLOOKUP(RC[1],DBASE,5))))"
Range(Cells(2, 4), "copy_end").Copy
Cells(2, 4).PasteSpecial xlValues
Application.CutCopyMode = False
ActiveWorkbook.Names("copy_end").Delete
Application.Run "SORT_DATUM"
Workbooks("RABO_TEMP.xlsx").Close True
Kill MyDownloads & "RABO_TEMP.xlsx"
Application.Goto Reference:=Workbooks(sFilename).Worksheets("BOEKINGEN").Cells(2, 2)
Application.ScreenUpdating = True
End If
End Subanton44
Verenigingslid
- Lid geworden
- 20 mei 2005
- Berichten
- 1.597
Hi Trainers,
Bedankt voor je uitgebreide info.
Ik heb aan het geplaatste .csv-bestand niets veranderd qua structuur. De bank levert het zo aan. Qua naam is iedere download anders - Van/tot datum zijn daarin verwerkt.
De mogelijkheid om alleen de mutaties te downloaden sinds de vorige download kent mijn bank niet. Er moet dus e.e.a. geregeld worden om dubbelingen te onderkennen en de echte van de valse te onderscheiden.
Uiteraard betekent dat een verschil voor het importeren.
Verder maak ik geen gebruik van een hulpbestand maar doe het voorwerk op een daarvoor ingericht tabblad.
Het redigeren van kolommen deed ik aanvankelijk ook als bij jou script maar ik heb tijdwinst geboekt door de kolommen te sorteren. Hiermee worden de kolommen in de juiste positie gebracht en kunnen ook lege kolommen tussen de andere gebracht worden - kwestie van nummeren in de kop.
Voorlopig ben ik tevreden met hetgeen ik vandaag heb weten te bereiken., tenzij er nog iemand een goed idee heeft om er nog een paar tienden van seconden af te halen.:thumb:
Winst was er door het sorteren van de kolommen en het verwijderen van onnodige/dubbele celopmaak.
Bedankt voor je uitgebreide info.
Ik heb aan het geplaatste .csv-bestand niets veranderd qua structuur. De bank levert het zo aan. Qua naam is iedere download anders - Van/tot datum zijn daarin verwerkt.
De mogelijkheid om alleen de mutaties te downloaden sinds de vorige download kent mijn bank niet. Er moet dus e.e.a. geregeld worden om dubbelingen te onderkennen en de echte van de valse te onderscheiden.
Uiteraard betekent dat een verschil voor het importeren.
Verder maak ik geen gebruik van een hulpbestand maar doe het voorwerk op een daarvoor ingericht tabblad.
Het redigeren van kolommen deed ik aanvankelijk ook als bij jou script maar ik heb tijdwinst geboekt door de kolommen te sorteren. Hiermee worden de kolommen in de juiste positie gebracht en kunnen ook lege kolommen tussen de andere gebracht worden - kwestie van nummeren in de kop.
Voorlopig ben ik tevreden met hetgeen ik vandaag heb weten te bereiken., tenzij er nog iemand een goed idee heeft om er nog een paar tienden van seconden af te halen.:thumb:
Winst was er door het sorteren van de kolommen en het verwijderen van onnodige/dubbele celopmaak.
jkpieterse
Terugkerende gebruiker
- Lid geworden
- 18 mei 2015
- Berichten
- 2.823
Als je dit:
vervangt door:
wordt het dan sneller?
Idem als je dit:
vervangt door:
Code:
521 Sheets("Bank").Select
522 Sheets("Bank").Range("A" & lLaatsteRij).Select
523 ActiveWindow.ScrollRow = lLaatsteRij - (Range("A2") + 1)
Code:
522 Application.Goto Sheets("Bank").Range("A" & lLaatsteRij), TrueIdem als je dit:
Code:
807 With Sheets("Bank")
808 For i = .UsedRange.Rows.Count To 1 Step -1
809 If IsNumeric(Left(.Cells(i, 55), 55)) Then 'de 56 staat voor de 67e kolom
810 If (.Cells(i, 55).Value) = 2 Then .Cells(i, 55).EntireRow.Delete
811 End If
812 Next
813 End Withvervangt door:
Code:
Dim vValue as variant
807 With Sheets("Bank")
vValue = .Cells(i, 55).Value2
808 For i = .UsedRange.Rows.Count To 1 Step -1
809 If IsNumeric(Left(vValue, 55)) Then 'de 56 staat voor de 67e kolom
810 If (vValue) = 2 Then .Cells(i, 55).EntireRow.Delete
811 End If
812 Next
813 End Withanton44
Verenigingslid
- Lid geworden
- 20 mei 2005
- Berichten
- 1.597
Hi Jan Karel,
Alweer bedankt voor je voorstellen.
Het 1e deel met 522 heb ik ingebracht. De code werkt maar de tijdmeeting geeft 0,0000 verschil met de oorspronkelijke code
Je 2e voorstel geeft een fout op vValue = .Cells(i, 55).Value2 (Fout 1004 tijdens uitvoering / Door de toepassing of object gedefinieerde fout.)
Alweer bedankt voor je voorstellen.
Het 1e deel met 522 heb ik ingebracht. De code werkt maar de tijdmeeting geeft 0,0000 verschil met de oorspronkelijke code
Je 2e voorstel geeft een fout op vValue = .Cells(i, 55).Value2 (Fout 1004 tijdens uitvoering / Door de toepassing of object gedefinieerde fout.)
Reactie op bericht #45
Hi Anton,
Duidelijk, dank voor je reactie.
Ja, het er uit halen van de dubbelingen (wat ik dus niet hoef te doen) kost toch wat tijd.
Ben blij dat je voorlopig tevreden bent met je eigen aanpak.
Hoop overigens wel dat jouw bank overgaat naar de nieuwe indeling en de mogelijkheid gaat geven transacties te downloaden vanaf de laatste download.
Succes en groet,
Trainers
Hi Anton,
Duidelijk, dank voor je reactie.
Ja, het er uit halen van de dubbelingen (wat ik dus niet hoef te doen) kost toch wat tijd.
Ben blij dat je voorlopig tevreden bent met je eigen aanpak.
Hoop overigens wel dat jouw bank overgaat naar de nieuwe indeling en de mogelijkheid gaat geven transacties te downloaden vanaf de laatste download.
Succes en groet,
Trainers
anton44
Verenigingslid
- Lid geworden
- 20 mei 2005
- Berichten
- 1.597
Ik heb via de Community deze vraag en ook afgeleiden daarvan meerdere malen aangekaart maar zonder resultaat.
Het is een 100% internetbank maar IT is niet hun sterkste kant.
Een slogan van hun (wat ze in mijn ogen niet waarmaken):
We ruimen alle financiële complexiteit en frustratie voor je uit de weg. Dit doen we met de juiste combinatie van de beste mensen en de slimste algoritmes.
jkpieterse
Terugkerende gebruiker
- Lid geworden
- 18 mei 2015
- Berichten
- 2.823
Vreemd die 1004 fout op vValue = ... zou echt niet moeten gebeuren.
roeljongman
Terugkerende gebruiker
- Lid geworden
- 30 mei 2010
- Berichten
- 1.384
- Office versie
- Microsoft 365
@ anton, ik heb je excelbestand niet gezien omdat we hier alleen naar code zit te kijken, maar ik denk dat jij het jezelf behoorlijk ingewikkeld hebt maakt. Als boekhouder download ik ook vaak bestanden van allerlei banken en ik hoef nooit meer dan max 8 a 10 kolommen toe te voegen om te krijgen wat ik wil. Als ik hoor dat je van 15 kolommen naar 56 kolommen en ik ook al die sorteer acties en wijzigen van volgorde van kolommen bekijk. dat duidt op onnodig gecreëerde complexiteit. Er lijkt steeds wat bijgekomen waar dan weer een plekje voor gevonden moest worden.
Voor de downloaden en bewerken van data hou ik altijd een paar "gouden regels" aan
1. originele download blijft in die volgorde, alle extra kolommen worden achteraan toegevoegd. (of helemaal vooraan)
maar nooit ertussen dat spaart enorm gepuzzel welke kolom waarheen verplaatst moet worden bij import van data.
als ik de tabel zelf wil bekijken gebruik overzichtsknoppen (groeperen) om (tijdelijk) overbodige kolommen eenvoudig te verbergen wn zorg je dat gerelateerde kolommen (tijdelijk) dichterbij elkaar komen te staan voor het eenvoudiger lezen.
maar meestal maak ik een aparte draaitabel of formule tabel om de data te presenteren en te lezen in de layout die ik prettig vind. Daarmee voorkom je dus zowel complexe verplaatsingen in de data als individuele kolom opmaak elke keer als je importeert want de aparte rapportages behouden hun opmaak als de onderliggende data wordt aangevuld.
dat de onderliggende tabel dan niet helemaal
2. voorkom dubbele data.
Jij klaagt dat je niet sinds laatste download kan downloaden. Toegegeven het is hinderlijk. Maar toch is dubbele data (eenvoudig) te voorkomen. Ik merk als boekhouder dat ik ook niet altijd op datum laatste download kan vertrouwen dus vraag altijd om bestanden met een bepaald tijdvak.
wat ik altijd doe om dubbeling te voorkomen:
1 in mijn excel bestand zet ik een max-formule op de datumkolom waardoor ik de datum van laatste transactie altijd weet zonder te hoeven zoeken voor ik de download maak of aanvraag.
2. in de download van de bank neem ik als startdatum de max-datum+1 en als einddatum altijd de datum van gisteren (of einde maand) maar nooit incl vandaag.
Door vandaag uit te sluiten loop je niet het risico dat je maar de helft van de transacties hebt voor die dag en de volgende keer die dag nog eens moet importeren met dubbelingen tot gevolg.
Op die manier heb je al bijna gegarandeerd geen dubbele downloads. Enige extra controle die je voor de zekerheid dan doet is of je berekend banksaldo overeenkomt met het opgegeven eindsaldo op die datum van de bank (meestal zit het eindsaldo al op elke regel verwerkt). Het gebeurd bij mij maar heel zelden dat het saldo niet klopt omdat ik regels mis.
Ook helpt het als je altijd data download voor een zelfde tijdvak, bijv een week van maandag tm zondag of een maand.
Kortom met een combinatie van een paar gedragsregels en een eenvoudige controle formule kun je een complexe vergelijkings-macro voorkomen
Je bent al een week urenlang bezig om een paar seconde doorlooptijd van je macro af te krijgen, maar ik denk dat je veel meer tijdwinst kan behalen door je macro code aanzienlijk te reduceren door je onderliggende data op een handiger manier te structureren. Wellicht vind je het gewoon leuk om het zo te doen en uit te vogelen, dan is dat prima..
Maar het klinkt toch ook een beetje alsof je zegt dat je raceauto zijn topsnelheid niet haalt op de Ginkelse heide en vervolgens probeert het motorvermogen en betere banden te monteren om te constateren dat je nog steeds geen 200km per uur kan rijden op de Ginkelse heide. Maar dat ligt niet aan de auto en zijn afstellingen maar aan de ondergrond. mul zand is trager dan strak asfalt.. jou data heeft meer paralellen met mul zand waar je bij elke import flink in zit te spitten om het bruikbaar dan van een strakke asfaltweg dat elke week een stukje wordt verlengd.
Dit hele betoog helpt jou huidige macro's niet sneller maken, maar snelheid is niet alleen een kwestie van een betere afstelling van deauto macro.. Dus als snelheid echt van belang is voor jou of gewoon een uitdaging dan moet je ook naar alle elementen / omgevingsfactoren kijken en niet te snel concluderen dat excel bij elke versie alleen maar trager wordt.
Misschien dat mijn andere inkijk helpt om nog eens naar andere manieren te kijken om het geheel sneller te maken.
Voor de downloaden en bewerken van data hou ik altijd een paar "gouden regels" aan
1. originele download blijft in die volgorde, alle extra kolommen worden achteraan toegevoegd. (of helemaal vooraan)
maar nooit ertussen dat spaart enorm gepuzzel welke kolom waarheen verplaatst moet worden bij import van data.
als ik de tabel zelf wil bekijken gebruik overzichtsknoppen (groeperen) om (tijdelijk) overbodige kolommen eenvoudig te verbergen wn zorg je dat gerelateerde kolommen (tijdelijk) dichterbij elkaar komen te staan voor het eenvoudiger lezen.
maar meestal maak ik een aparte draaitabel of formule tabel om de data te presenteren en te lezen in de layout die ik prettig vind. Daarmee voorkom je dus zowel complexe verplaatsingen in de data als individuele kolom opmaak elke keer als je importeert want de aparte rapportages behouden hun opmaak als de onderliggende data wordt aangevuld.
dat de onderliggende tabel dan niet helemaal
2. voorkom dubbele data.
Jij klaagt dat je niet sinds laatste download kan downloaden. Toegegeven het is hinderlijk. Maar toch is dubbele data (eenvoudig) te voorkomen. Ik merk als boekhouder dat ik ook niet altijd op datum laatste download kan vertrouwen dus vraag altijd om bestanden met een bepaald tijdvak.
wat ik altijd doe om dubbeling te voorkomen:
1 in mijn excel bestand zet ik een max-formule op de datumkolom waardoor ik de datum van laatste transactie altijd weet zonder te hoeven zoeken voor ik de download maak of aanvraag.
2. in de download van de bank neem ik als startdatum de max-datum+1 en als einddatum altijd de datum van gisteren (of einde maand) maar nooit incl vandaag.
Door vandaag uit te sluiten loop je niet het risico dat je maar de helft van de transacties hebt voor die dag en de volgende keer die dag nog eens moet importeren met dubbelingen tot gevolg.
Op die manier heb je al bijna gegarandeerd geen dubbele downloads. Enige extra controle die je voor de zekerheid dan doet is of je berekend banksaldo overeenkomt met het opgegeven eindsaldo op die datum van de bank (meestal zit het eindsaldo al op elke regel verwerkt). Het gebeurd bij mij maar heel zelden dat het saldo niet klopt omdat ik regels mis.
Ook helpt het als je altijd data download voor een zelfde tijdvak, bijv een week van maandag tm zondag of een maand.
Kortom met een combinatie van een paar gedragsregels en een eenvoudige controle formule kun je een complexe vergelijkings-macro voorkomen
Je bent al een week urenlang bezig om een paar seconde doorlooptijd van je macro af te krijgen, maar ik denk dat je veel meer tijdwinst kan behalen door je macro code aanzienlijk te reduceren door je onderliggende data op een handiger manier te structureren. Wellicht vind je het gewoon leuk om het zo te doen en uit te vogelen, dan is dat prima..
Maar het klinkt toch ook een beetje alsof je zegt dat je raceauto zijn topsnelheid niet haalt op de Ginkelse heide en vervolgens probeert het motorvermogen en betere banden te monteren om te constateren dat je nog steeds geen 200km per uur kan rijden op de Ginkelse heide. Maar dat ligt niet aan de auto en zijn afstellingen maar aan de ondergrond. mul zand is trager dan strak asfalt.. jou data heeft meer paralellen met mul zand waar je bij elke import flink in zit te spitten om het bruikbaar dan van een strakke asfaltweg dat elke week een stukje wordt verlengd.
Dit hele betoog helpt jou huidige macro's niet sneller maken, maar snelheid is niet alleen een kwestie van een betere afstelling van de
Misschien dat mijn andere inkijk helpt om nog eens naar andere manieren te kijken om het geheel sneller te maken.
snb
Verenigingslid
- Lid geworden
- 12 jun 2008
- Berichten
- 19.710
@roel
Volledig mee eens.
Maar het probleem zit in het feit, zoals JKP ook al constateerde, dat er, in plaats van een querytable aan te passen, steeds een nieuwe querytable aan het bestand wordt toegevoegd.
Dat is een effektief recept voor vertraging. Dat Excel 2016 trager loopt dan Excel 2010 komt dan alleen maar omdat het bestand in 2018 meer Querytabels bevat dan in 2010. Je kunt voorspellen dat het bestand alleen maar trager gaat worden, of het nu onder 2016, 2010, 2003, of 2000 gedraaid wordt.
Stelregel : blijf van VBA af als je niet iedere VBA-regel voor 100% procent begrijpt.
Volledig mee eens.
Maar het probleem zit in het feit, zoals JKP ook al constateerde, dat er, in plaats van een querytable aan te passen, steeds een nieuwe querytable aan het bestand wordt toegevoegd.
Dat is een effektief recept voor vertraging. Dat Excel 2016 trager loopt dan Excel 2010 komt dan alleen maar omdat het bestand in 2018 meer Querytabels bevat dan in 2010. Je kunt voorspellen dat het bestand alleen maar trager gaat worden, of het nu onder 2016, 2010, 2003, of 2000 gedraaid wordt.
Stelregel : blijf van VBA af als je niet iedere VBA-regel voor 100% procent begrijpt.
anton44
Verenigingslid
- Lid geworden
- 20 mei 2005
- Berichten
- 1.597
@roeljongman: Bedankt voor je uitvoerig betoog. Ik begrijp je adviezen al heb ik ook wel een paar kanttekeningen. Ik heb niet geconcludeerd dat excel bij elke versie alleen maar trager wordt. IK heb alleen geconstareerd dat op mijn nieuwe laptop Win10/Excel2016 de macro van een zekere toepassing meer tijd nodig hebben dan op mijn oudere machine met Win7/Excel 2010.
Zou je de opmerking "kijken naar alle elementen / omgevingsfactoren" wat nader willen toelichten? kun je aangeven wat je hier mee bedoeld.
Ik vind het voor mezelf een uitdaging om dergelijke projectjes die aanvankelijk redelijk eenvoudig beginnen verder uit te laten groeien als daar behoefde aan is. Ik zie dit als een hobby en niet het toepassen van de applicatie voor meer of minder zakelijk gebruik.
@snb: Ook bedankt voor je reactie en ook hier wil ik graag op reageren.
Als ik je stelregel : "blijf van VBA af als je niet iedere VBA-regel voor 100% procent begrijpt" goed interpreteer zou niemand VBA kunnen toepassen als hij/zij er geen opleiding voor gehad heeft.
Ik heb ervaren dat bij aangedragen (deel-)oplossingen ik die meestal werkbaar heb gekregen en ook enigszins kan begrijpen maar zeker niet voor 100%. Ik deel je mening in deze niet.
Je stelregel zou kunnen betekenen dat helpmij.nl niemand hulp kan bieden die niet in VBA onderlegd is.
Toch hoop ik dat je in voorkomende gevallen mijn VBA vragen zult blijven beantwoorden zoals je dat in het verleden met waardering en respect gedaan hebt..
Zou je de opmerking "kijken naar alle elementen / omgevingsfactoren" wat nader willen toelichten? kun je aangeven wat je hier mee bedoeld.
Ik vind het voor mezelf een uitdaging om dergelijke projectjes die aanvankelijk redelijk eenvoudig beginnen verder uit te laten groeien als daar behoefde aan is. Ik zie dit als een hobby en niet het toepassen van de applicatie voor meer of minder zakelijk gebruik.
@snb: Ook bedankt voor je reactie en ook hier wil ik graag op reageren.
Als ik je stelregel : "blijf van VBA af als je niet iedere VBA-regel voor 100% procent begrijpt" goed interpreteer zou niemand VBA kunnen toepassen als hij/zij er geen opleiding voor gehad heeft.
Ik heb ervaren dat bij aangedragen (deel-)oplossingen ik die meestal werkbaar heb gekregen en ook enigszins kan begrijpen maar zeker niet voor 100%. Ik deel je mening in deze niet.
Je stelregel zou kunnen betekenen dat helpmij.nl niemand hulp kan bieden die niet in VBA onderlegd is.
Toch hoop ik dat je in voorkomende gevallen mijn VBA vragen zult blijven beantwoorden zoals je dat in het verleden met waardering en respect gedaan hebt..
snb
Verenigingslid
- Lid geworden
- 12 jun 2008
- Berichten
- 19.710
Je redenering klopt niet.
Als voorwaarde stel ik het resultaat van je inspanningen: het begrijpen van iedere regel VBA.
Ik zeg niets over het proces dat daaraan vooraf gaat: opleiding, zelfstudie, nadenken, testen, hulp van een ander (bijv. van dit forum).
Ook VBA is een taal, die bij verkeerd/slordig gebruik tot rampen kan leiden.
Als je niet 100% zeker bent of wat je doet goed overkomt kun je beter gebruik maken van een tolk/programmeur.
Als voorwaarde stel ik het resultaat van je inspanningen: het begrijpen van iedere regel VBA.
Ik zeg niets over het proces dat daaraan vooraf gaat: opleiding, zelfstudie, nadenken, testen, hulp van een ander (bijv. van dit forum).
Ook VBA is een taal, die bij verkeerd/slordig gebruik tot rampen kan leiden.
Als je niet 100% zeker bent of wat je doet goed overkomt kun je beter gebruik maken van een tolk/programmeur.
roeljongman
Terugkerende gebruiker
- Lid geworden
- 30 mei 2010
- Berichten
- 1.384
- Office versie
- Microsoft 365
@anton, ach ja sommige dingen schrijf ik misschien wat te stellig nuances op de juiste plek leggen is altijd lastig als je iemands motieven niet kent. dus verwoord je het wat algemeen. vat het niet persoonlijk op zo was het niet bedoeld ik probeerde slechts een beeld te schetsen. Ik kom het heel vaak in mijn werk tegen dat mensen een hele werkdag besteden aan een macro een paar seconde sneller te krijgen. terwijl het in het dagelijks werkproces echt niet uitmaakt of een macro in 2 of 6 seconde klaar is.
De opmerking over alle elementen / omgevingsfactoren was misschien wat abstract geworden. Jou laatste zin aan mij
"Ik vind het voor mezelf een uitdaging om dergelijke projectjes die aanvankelijk redelijk eenvoudig beginnen verder uit te laten groeien als daar behoefde aan is. Ik zie dit als een hobby en niet het toepassen van de applicatie voor meer of minder zakelijk gebruik."
Geeft wel aardig weer wat ik in projectjes tegenkom hoe bij aanpassingen juist niet naar alle elementen of het totaal wordt gekeken.
Het begint met een mooie eenvoudige code, dan moeten er bijv 2 kolommen bij en komt er of een paar regels code bij of zelfs een aparte macro om dat te regelen. na 5 o 6 uitbreidingen wordt met de ene code een deel van het werk van de andere code weer ongedaan gemaakt worden.
Eigenlijk moet je elke keer als je iets wilt toevoegen je hele code evalueren en opnieuw bepalen wat de meest efficiënte methode is om alle taken uit te voeren. En zo nodig de hele code herschrijven om dat te bereiken. Ook moet je kijken soms evalueren of de ooit begonnen opzet nog werkbaar is. zoals bij jou waarbij het bestand vn 15 naar 56 kolommen is gegroeid. afijn de uitleg blijft wat abstract maar het is wel wat ik ook ervaren heb in mijn "learning curve". durft je hele code opnieuw aan te pakken als je vind dat het te lang gaat duren. soms helemaal vanaf 0 code opbouwen. En vaak betekent dat ook dat je de onderliggende historische data moet omzetten of aanpassen voor de nieuwe werkwijze.
als ik jou code bekijk zie ik niet meteen heel schokkende dingen anders dan wat hier al aangedragen is op dingen die effectief dubbel gaan, het is wel voor het eerst dat ik meer dan 3 macro's zie om een relatief simpel bankbestand te bewerken tot een eindproduct. maar zonder onderliggende data is het lastig oordelen hoe nodig of onnodig dat is.
De opmerking over alle elementen / omgevingsfactoren was misschien wat abstract geworden. Jou laatste zin aan mij
"Ik vind het voor mezelf een uitdaging om dergelijke projectjes die aanvankelijk redelijk eenvoudig beginnen verder uit te laten groeien als daar behoefde aan is. Ik zie dit als een hobby en niet het toepassen van de applicatie voor meer of minder zakelijk gebruik."
Geeft wel aardig weer wat ik in projectjes tegenkom hoe bij aanpassingen juist niet naar alle elementen of het totaal wordt gekeken.
Het begint met een mooie eenvoudige code, dan moeten er bijv 2 kolommen bij en komt er of een paar regels code bij of zelfs een aparte macro om dat te regelen. na 5 o 6 uitbreidingen wordt met de ene code een deel van het werk van de andere code weer ongedaan gemaakt worden.
Eigenlijk moet je elke keer als je iets wilt toevoegen je hele code evalueren en opnieuw bepalen wat de meest efficiënte methode is om alle taken uit te voeren. En zo nodig de hele code herschrijven om dat te bereiken. Ook moet je kijken soms evalueren of de ooit begonnen opzet nog werkbaar is. zoals bij jou waarbij het bestand vn 15 naar 56 kolommen is gegroeid. afijn de uitleg blijft wat abstract maar het is wel wat ik ook ervaren heb in mijn "learning curve". durft je hele code opnieuw aan te pakken als je vind dat het te lang gaat duren. soms helemaal vanaf 0 code opbouwen. En vaak betekent dat ook dat je de onderliggende historische data moet omzetten of aanpassen voor de nieuwe werkwijze.
als ik jou code bekijk zie ik niet meteen heel schokkende dingen anders dan wat hier al aangedragen is op dingen die effectief dubbel gaan, het is wel voor het eerst dat ik meer dan 3 macro's zie om een relatief simpel bankbestand te bewerken tot een eindproduct. maar zonder onderliggende data is het lastig oordelen hoe nodig of onnodig dat is.
anton44
Verenigingslid
- Lid geworden
- 20 mei 2005
- Berichten
- 1.597
Hierbij mijn toegezegde info.
Werkwijze Bankmutaties
1. Sub VBA
Importeren van een .csv mutatiebestand met variabele naam een een apart tablad (Import).
Kolomformaten worden waar mogelijk daarbij ingesteld.
Mutatiedata worden in 15 kolom aangeleverd.
Mijn bank kent geen mogelijkheid om alleen nieuwe mutaties sinds de vorige download te kiezen. Standaard ingesteld op een tijdsbestek van 1 maand.
Een ander bereik is door handmatig via een kalender begin en einddatum te kiezen.
2. Sub VBA
Kolommen schikken voor de gewenste lay-out in het tabblad Bank voor het totale overzicht.
Aanvankelijk gebruikt ik hiervoor kolommen verplaatsen en kolommen invoegen. Snelheidswinst werd bereikt door de kolommen te sorteren. Hiermee is het tussenvoegen van lege
kolommen ook gerealiseerd.
Hier worden ook de gewenste 21 kolommen voor het tabelarisch kasboek ingevoerd voor uitsplitsing van bedragen per budgetcode.
Het totaal aantal kolommen komt hiermee op 56 (incl. noodzakelijke hulpkolommen)
In regel 6 staat de gewenste kolomvolgorde (in tekstformaat ivm latere filtering op datum) vermeld. Deze rij wordt telkenmale naar rij 7 gekopieerd en opgenomen in de
sorteerbewerking.
3. Sub VBA
Filteren/verwijderen van overtollige regels/mutaties die ouder zijn dan mijn vorige mutatiedatum.
De datum daarvoor wordt opgehaald uit het blad Bank (totaaloverzicht)
4. Sub VBA
Rij 5 bevat de formules en opmaak die naar de overgebleven mutaties worden gekopieërd. Eerst in geselecteerde kolommen de formules en vervolgens voor alle rijen de opmaak.
Aanvankelijk deed ik dat door de kolommen te kiezen (enkel of gebied al naar gelang). De nieuwe methode geeft ook hier tijdwinst.
5. Sub VBA
Het verplaatsen van de bewerkte en opgemaakte rijen naar het overzichtsblad = toevoegen vanaf de 1e lege rij.
6. Sub VBA
Voorbereiding voor het vinden van dubbelingen. Door in een hulpkolom een string samen te stellen (formule==A50&B50&C50&D50&I50&T50&Z50&AE50) van rubrieken die
kenmerkend zijn voor de mutatie wordt mbv (formule=ALS(AANTAL.ALS(bereik;te onderzoeken cel)>1;2;1)) een code 1 of 2 geplaatst.
Met VBA wordt nu naar het getal 2 gezocht en de hele rij verwijderd. Persoonlijk vergelijk ik daarna of het getoonde saldo overeenkomt met dat van de bank om valse dubbelingen uit
te sluiten.
7. Sub VBA
Voor een verbeterde leesbaarheid laat ik alle mutaties van het totaaloverzicht met de gewenste sorteervolgorde sorteren.
Hiermee is de VBA routine beeindigd.
Handmatig vul ik daarna de budgetcodes aan. Een groot deel gebeurd automatisch mbv formules adhv zoeken van de naam tegenrek.houder in een tabel.
Opmerkingen:
Reeds eerder gemeld: De codes voor het meten van looptijden heeft me enorm geholpen om de vinger op de "zere" plekken te leggen.
a. Door aanpassingen van codes en verwijdering van dubbele routines voor celopmaak is er een significante tijdwinst geboekt.
b. Als test heb ik het aantal kolommen van het tabellarisch kasboek-deel teruggebracht naar 3 stuks. Hierbij is er een nauwelijks meetbare tijdwinst geconstateerd in de macro's. Ieder
meeting toont enige variatie.
c. De codes heb ik ondergebracht in een aantal sub's om bij wijzigingen van codes de diverse fases beter te kunnen testen (déactiveren van calls).
Als proef heb ik de end sub enz verwijderd waardoor het één doorlopende code werd. Een tijdmeting gaf een winst van 0,5 sec.
d. Ik schat in dat ik mijn mijn zeer beperkte kennis van VBA (try abd error) een bevredigend eindresultaat heb bereikt. Het is en blijft voor mij een hobby en uitdaging om daar waar ik
mogelijkheden zie VBA inzet te proberen (waar nodig met hulp en respect voor helpende forumleden). Catastrofes zijn niet aan de orde. Alles blijft binnenshuis.
Werkwijze Bankmutaties
1. Sub VBA
Importeren van een .csv mutatiebestand met variabele naam een een apart tablad (Import).
Kolomformaten worden waar mogelijk daarbij ingesteld.
Mutatiedata worden in 15 kolom aangeleverd.
Mijn bank kent geen mogelijkheid om alleen nieuwe mutaties sinds de vorige download te kiezen. Standaard ingesteld op een tijdsbestek van 1 maand.
Een ander bereik is door handmatig via een kalender begin en einddatum te kiezen.
2. Sub VBA
Kolommen schikken voor de gewenste lay-out in het tabblad Bank voor het totale overzicht.
Aanvankelijk gebruikt ik hiervoor kolommen verplaatsen en kolommen invoegen. Snelheidswinst werd bereikt door de kolommen te sorteren. Hiermee is het tussenvoegen van lege
kolommen ook gerealiseerd.
Hier worden ook de gewenste 21 kolommen voor het tabelarisch kasboek ingevoerd voor uitsplitsing van bedragen per budgetcode.
Het totaal aantal kolommen komt hiermee op 56 (incl. noodzakelijke hulpkolommen)
In regel 6 staat de gewenste kolomvolgorde (in tekstformaat ivm latere filtering op datum) vermeld. Deze rij wordt telkenmale naar rij 7 gekopieerd en opgenomen in de
sorteerbewerking.
3. Sub VBA
Filteren/verwijderen van overtollige regels/mutaties die ouder zijn dan mijn vorige mutatiedatum.
De datum daarvoor wordt opgehaald uit het blad Bank (totaaloverzicht)
4. Sub VBA
Rij 5 bevat de formules en opmaak die naar de overgebleven mutaties worden gekopieërd. Eerst in geselecteerde kolommen de formules en vervolgens voor alle rijen de opmaak.
Aanvankelijk deed ik dat door de kolommen te kiezen (enkel of gebied al naar gelang). De nieuwe methode geeft ook hier tijdwinst.
5. Sub VBA
Het verplaatsen van de bewerkte en opgemaakte rijen naar het overzichtsblad = toevoegen vanaf de 1e lege rij.
6. Sub VBA
Voorbereiding voor het vinden van dubbelingen. Door in een hulpkolom een string samen te stellen (formule==A50&B50&C50&D50&I50&T50&Z50&AE50) van rubrieken die
kenmerkend zijn voor de mutatie wordt mbv (formule=ALS(AANTAL.ALS(bereik;te onderzoeken cel)>1;2;1)) een code 1 of 2 geplaatst.
Met VBA wordt nu naar het getal 2 gezocht en de hele rij verwijderd. Persoonlijk vergelijk ik daarna of het getoonde saldo overeenkomt met dat van de bank om valse dubbelingen uit
te sluiten.
7. Sub VBA
Voor een verbeterde leesbaarheid laat ik alle mutaties van het totaaloverzicht met de gewenste sorteervolgorde sorteren.
Hiermee is de VBA routine beeindigd.
Handmatig vul ik daarna de budgetcodes aan. Een groot deel gebeurd automatisch mbv formules adhv zoeken van de naam tegenrek.houder in een tabel.
Opmerkingen:
Reeds eerder gemeld: De codes voor het meten van looptijden heeft me enorm geholpen om de vinger op de "zere" plekken te leggen.
a. Door aanpassingen van codes en verwijdering van dubbele routines voor celopmaak is er een significante tijdwinst geboekt.
b. Als test heb ik het aantal kolommen van het tabellarisch kasboek-deel teruggebracht naar 3 stuks. Hierbij is er een nauwelijks meetbare tijdwinst geconstateerd in de macro's. Ieder
meeting toont enige variatie.
c. De codes heb ik ondergebracht in een aantal sub's om bij wijzigingen van codes de diverse fases beter te kunnen testen (déactiveren van calls).
Als proef heb ik de end sub enz verwijderd waardoor het één doorlopende code werd. Een tijdmeting gaf een winst van 0,5 sec.
d. Ik schat in dat ik mijn mijn zeer beperkte kennis van VBA (try abd error) een bevredigend eindresultaat heb bereikt. Het is en blijft voor mij een hobby en uitdaging om daar waar ik
mogelijkheden zie VBA inzet te proberen (waar nodig met hulp en respect voor helpende forumleden). Catastrofes zijn niet aan de orde. Alles blijft binnenshuis.
anton44
Verenigingslid
- Lid geworden
- 20 mei 2005
- Berichten
- 1.597
Als aanvulling.
Eenzelfde test op mijn "oudere"laptop geeft een voor mij verrassend resultaat: :shocked:
Nieuwe laptop Win10/Exc2016 = 2.40 sec
Oudere laptop Win07/Exc2010 = 2.98 sec
Het fenomeen lijkt te zitten in het grafische werk m.n. de beeldopbouw die bij de nieuwe laptop wat stropiger lijkt te verlopen en mijn subjectieve waarneming blijkbaar beïnvloedt.
Eenzelfde test op mijn "oudere"laptop geeft een voor mij verrassend resultaat: :shocked:
Nieuwe laptop Win10/Exc2016 = 2.40 sec
Oudere laptop Win07/Exc2010 = 2.98 sec
Het fenomeen lijkt te zitten in het grafische werk m.n. de beeldopbouw die bij de nieuwe laptop wat stropiger lijkt te verlopen en mijn subjectieve waarneming blijkbaar beïnvloedt.
- Status
