VBA code te lang?

[276159]

Ik ben op mijn werk bezig om een after sales op te zetten voor onze afdeling. Nu ben ik bezig om de code's zo te schrijven dat dmv een macro een after-sales blad gemaakt wordt met een bijbehorden zaal die gasten gehuurd hebben.

Nu krijg ik na 5 zalen enkel een foutmelding

(Run-time error '-2147024809-(80070057)'
The item with the specific name wasn't found.

Als ik dan in de code kijk, is hij hetzelfde als alle andere zalen die hij aanmaakt. Zijn er teveel macro code's waardoor hij er na 5 gedaan te hebben er mee stopt?

Bijgevoegd heb ik het bestand waar het om gaat.

 

[Wigi]

Hoi

Volgens mij weet je de macro recorder knop wel staan

Onafgezien van het feit dat er een limiet is of zou zijn, moet je eerder proberen om soortgelijke code in een aparte procedure te zetten. En dan die procedure aanroepen. Dat is veel korter qua code, veel duidelijker, en vraagt achteraf veel minder onderhoud aan de code. Een wijziging hoef je immers dan maar 1 keer door te voeren.

Bvb.

Sub ToonTekst()

    Call IetsTonenOpHetScherm("zomaar wat tekst")

End Sub

Private Sub IetsTonenOpHetScherm(sBoodschap As String)

    MsgBox sBoodschap, vbInformation, "De ingegeven boodschap"

End Sub

Voer eens de macro ToonTekst uit en kijk wat er gebeurt. Verander dan "zomaar wat tekst" in een andere tekst en kijk weer wat er gebeurt. Snappie?

Eens dat gebeurd is, is het zaak om die ene (of paar) algemene procedures zo goed mogelijk te schrijven. Dus vermijd .Select, .Activate en nog andere dingen die enkel maar de code vertragen.

Wigi

 

[snb]

Vermijd ook Call als je een routine aanroept.

 

[Wigi]

Vermijd ook Call als je een routine aanroept.

Wat is de reden daarvan en wat win je door dit achterwege te laten?

 

[snb]

Het is overbodig.

Uit de hulp van de VBEditor:

Notities

U hoeft het sleutelwoord Call niet te gebruiken wanneer u een procedure oproept.

 

[276159]

Ik heb de code die wigi me gaf eens uitgeprobeerd. Volgens mij bedoel je hier mee te zeggen dat je dmv een code een andere code op kan roepen? Of heb ik dit verkeerd.

Nu begrijp ik alleen niet je punt hierin hoe ik mijn werkmap zo kan krijgen dat hij die foutmelding niet geeft. (ben beginnend met Macro's, dus geheel zelf schrijven lukt idd niet, ik gebruik voornamelijk de recorder, en pas de gegevens aan die ik wil verkrijgen)

 

[Ginger]

Het is overbodig.

Uit de hulp van de VBEditor:

Notities

U hoeft het sleutelwoord Call niet te gebruiken wanneer u een procedure oproept.

Naar mijn idee vergroot het gebruik van 'Call' wel de leesbaarheid van je codes. Het zorgt volgens mij ook niet voor vertraging dus is het maar wat je prettig vindt. Toch?

Groet, Leo

 

[Wigi]

Ik heb de code die wigi me gaf eens uitgeprobeerd. Volgens mij bedoel je hier mee te zeggen dat je dmv een code een andere code op kan roepen? Of heb ik dit verkeerd.

Klopt helemaal! Je hebt 1 of een paar algemene procedures, met algemene kapstokken. De ene keer "vervang" je een kapstok door iets, daarna door iets anders, ... maar belangrijk is dat de logica van de procedure en wat het doet slechts op 1 plaats staat en niet op tientallen plaatsen.

Nu begrijp ik alleen niet je punt hierin hoe ik mijn werkmap zo kan krijgen dat hij die foutmelding niet geeft.

Klopt ook. Jij zal nu de keuze moeten maken: die foutmeldingen proberen recht te zetten (en overal ! aanpassen) en verder werken zoals nu, ofwel mijn suggestie doorvoeren (veel beperktere code, veel overzichtelijker, en fouten zullen makkelijker gevonden en aangepast zijn). Jij kiest

Wigi

 

[Wigi]

Naar mijn idee vergroot het gebruik van 'Call' wel de leesbaarheid van je codes. Het zorgt volgens mij ook niet voor vertraging dus is het maar wat je prettig vindt. Toch?

Groet, Leo

Zo denk ik er ook over.

Ik verkies:

Sub ToonTekst()

    Call IetsTonenOpHetScherm("zomaar wat tekst")

End Sub

boven

Sub ToonTekst()

    IetsTonenOpHetScherm "zomaar wat tekst"

End Sub

Ieder zijn/haar manier van werken uiteraard :thumb:

Wigi

 

[276159]

Klopt helemaal! Je hebt 1 of een paar algemene procedures, met algemene kapstokken. De ene keer "vervang" je een kapstok door iets, daarna door iets anders, ... maar belangrijk is dat de logica van de procedure en wat het doet slechts op 1 plaats staat en niet op tientallen plaatsen.


Klopt ook. Jij zal nu de keuze moeten maken: die foutmeldingen proberen recht te zetten (en overal ! aanpassen) en verder werken zoals nu, ofwel mijn suggestie doorvoeren (veel beperktere code, veel overzichtelijker, en fouten zullen makkelijker gevonden en aangepast zijn). Jij kiest

Wigi

Even op vakantie geweest, en nu weer druk aan de slag. Nu vraag ik me alleen af, hoe kan ik de code korter krijgen, waardoor ik niet tegen een foutmelding aanloop na een aantal code's te hebben opgeroepen. Hierboven wordt gesproken over zo min mogelijk; select, activate etc. maar waarvoor vervang ik ze dan? Is er een manier om deze code's in te korter en overzichtelijker te maken.

 

[Demeter]

Select komt meer voor door het gebruik van de macro recorder.
Deze registreerd iedere aktie die je doet.
Veel van deze akties zijn vaak overbodig,zoals het selecteren van een cel als je deze naam ook direct in het commando mee kan nemen.

Voorbeeld.

Range("D3").Select
    ActiveCell.FormulaR1C1 = "=Biesbosch!R1C19"

wordt dan

Range("D3").FormulaR1C1 = "=Biesbosch!R1C19"

net zoals:

    Sheets("Voorblad").Select
    ActiveWindow.SelectedSheets.Visible = False

het volgende kan worden

    Sheets("Voorblad").Visible = False

Zo wordt je code ook een stuk leesbaarder.

 

[Demeter]

Klein voorbeeldje:

Sub MakenBiesbosch()

Application.ScreenUpdating = False

    Sheets("Zaal").Visible = True
        Sheets("Zaal").Copy Before:=Sheets(4)
        Sheets("Zaal (2)").Name = "Biesbosch"
        ActiveWorkbook.Names.Add Name:="Databasenmr2", RefersToR1C1:="=Biesbosch!R5C8"
    Sheets("Zaal").Visible = False
    
    Sheets("Database").Visible = True
        Sheets("Database").Copy After:=Sheets(7)
        Sheets("Database (2)").Name = "DTB-Biesbosch"
        Range("C2:I501").Replace What:="basenmr", Replacement:="basenmr2", LookAt:= _
            xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
        Range("C2:I501").Replace What:="Zaal!$", Replacement:="Biesbosch!$", LookAt:= _
            xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
    Sheets("Database").Visible = False

        With Sheets("Totalen")
            Range("C3").FormulaR1C1 = "=Biesbosch!R7C18"
            Range("D3").FormulaR1C1 = "=Biesbosch!R1C19"
            Range("E3").FormulaR1C1 = "=Biesbosch!R2C19"
            Range("F3").FormulaR1C1 = "=Biesbosch!R3C19"
            Range("G3").FormulaR1C1 = "=Biesbosch!R4C19"
            Range("I3").FormulaR1C1 = "=Biesbosch!R6C18"
        End With

Application.ScreenUpdating = True

End Sub

merk ook de volgende code in je macro op:
Application.ScreenUpdating = False
en onderaan:
Application.ScreenUpdating = True

1e regel zett de scherm updating uit zodat er niet het geflikker ziet.
2e regel zet deze weer aan.

 

[276159]

Tnx, heeft me al een heel eind op weg geholpen. Ook duidelijk door in verschillende alinea's te schrijven, maakt de code overzichtelijk.

Kom alleen op een probleem, in de laatste alinea wil ik een aantal formule's naar het werkblad 'totalen' schrijven. Dit zie je ook in de code, enkel schrijft hij deze weg op het werkblad 'DTB-Biesbosch'. Kan dit komen doordat het werkblad 'totalen' nog hidden is?

 

[Wigi]

With Sheets("Totalen")
            [B][SIZE="4"].[/SIZE][/B]Range("C3").FormulaR1C1 = "=Biesbosch!R7C18"
            [B][SIZE="4"].[/SIZE][/B]Range("D3").FormulaR1C1 = "=Biesbosch!R1C19"
            [B][SIZE="4"].[/SIZE][/B]Range("E3").FormulaR1C1 = "=Biesbosch!R2C19"
            [B][SIZE="4"].[/SIZE][/B]Range("F3").FormulaR1C1 = "=Biesbosch!R3C19"
            [B][SIZE="4"].[/SIZE][/B]Range("G3").FormulaR1C1 = "=Biesbosch!R4C19"
            [B][SIZE="4"].[/SIZE][/B]Range("I3").FormulaR1C1 = "=Biesbosch!R6C18"
End With

 

[Demeter]

Zie de punt voor iedere range in de code van Wigi.
Heb deze in de vluggigheid vergeten.

Prettig weekend.

 

[276159]

Thanks hierdoor is mijn vraag zo goed als beantwoord, ipv. 5 zalen aan te kunnen maken, kan ik er nu 20 maken. (ben de code's zo veel mogelijk over verschillende werkbladen aan het wegschrijven zodat ik uiteindelijk alles kan doen wat ik wil).

Loop nu enkel tegen een ander probleempje aan in de code. Oorspronkelijk was de code:

Range("C2:I501").Select
Selection.Replace What:="basenmr", Replacement:="basenmr3", LookAt:= _ 
xlPart, SearchOrder:=xlByRows, MatchCase:=False, Searchformat:=False, _
ReplaceFormat:=False
Selection.Replace What:="Zaal!$", Replacement:="Biesbosch!$", LookAt:= _ 
xlPart, SearchOrder:=xlByRows, MatchCase:=False, Searchformat:=False, _
ReplaceFormat:=False

Deze is nu herschreven naar:

Range("C2:I501").Replace What:="basenmr", Replacement:="basenmr3", LookAt:= _ 
xlPart, SearchOrder:=xlByRows, MatchCase:=False, Searchformat:=False, _
ReplaceFormat:=False
Range("C2:I501").Replace What:="Zaal!$", Replacement:="Biesbosch!$", LookAt:= _ 
xlPart, SearchOrder:=xlByRows, MatchCase:=False, Searchformat:=False, _
ReplaceFormat:=False

Hier loop ik tegen het probleem aan. In de macro geef je aan dat je iets wijzigt in de code in de behorende cellen van DTB-Biesbosch, Cellen C2:I501. Dit doet hij enkel niet, na afloop van de macro blijft de tekst Zaal!$ & basenmr staan.

De bedoeling erachter is, dat hij vanuit de aangemaakte zaal, hij de omzet wegschrijft naar de database van de bijbehorende zaalnaam. Dit doet hij nu niet, omdat hij deze namen niet verandert. Als ik dit naderhand handmatig doe, doet hij het natuurlijk wel.

Alvast bedankt voor de uitstekende hulp hier.

Update:
Ben zelf aan het zoeken geweest, het hoe en waarom hij deze macro niet juist uitvoerde heb ik niet kunnen vinden. Maar heb de macro ingekort en een andere excelbestand gemaakt waarop hij de gegevens schrijft. Hierdoor is het origineel kleiner geworden, en het andere excelbestand hoef ik alleen op te vragen bij cijferverklaringen dat ik 1x per maand moet doen.

Probleem hierdoor zo goed als zijnde opgelost. Nogmaals bedankt hier voor de hulp