VBA, Word 2007, Procedure te groot

[jackfish]

Ik ben bijna aan het einde van mijn werk (een mooi praktisch bruikbaar Userform dat data naar het document en excel wegschrijft) en krijg de melding "compileerfout; procedure te groot" ...

Alle code staat "onder" het userform en bevat kennelijk teveel regels? Als ik de lijst om te exporteren naar Excel weghaal, geeft VBA geen foutmelding meer. Ik heb geprobeerd om een module in te voegen en de code voor export naar excel daarin weg te schrijven. Dat werkt op zich wel , alleen krijg ik de 'private sub Naar_Database' niet van het userform, via een command-button, opgestart.

Private Sub Naar_Database_Click()
Call Naar_Database
End Sub

Code hierboven geeft: compileerfout; Ongeldig gebruik van een eigenschap

Private Sub Naar_Database_Click()
Run "Naar_Database"
End Sub

Code hierboven geeft fout 1004; macro kan niet worden uitgevoerd, niet beschikbaar of macro's zijn uitgeschakeld.


Op te roepen macro in module1

Sub Naar_Database()
Dim oExcel As Excel.Application
Dim oWB As Excel.Workbook

Set oExcel = New Excel.Application
Set oWB = oExcel.Workbooks.Open("C:\helpmij.xlsx")
oExcel.Visible = True
With oWB.Sheets(1)

.Cells(.Rows.Count, 1).End(xlUp).Offset(1) = TxtVBClientNummer.Text
.Cells(.Rows.Count, 2).End(xlUp).Offset(1) = TxtVBGeboortedatum.Text
.Cells(.Rows.Count, 3).End(xlUp).Offset(1) = IIf(CBvbMan, 1, 0)
.....
.Cells(.Rows.Count, 550).End(xlUp).Offset(1) = IIf(CBanticonceptieNee, 1, 0)

End With
oWB.Close True
oExcel.Quit
Set oWB = Nothing
Set oExcel = Nothing
End Sub

Wie kan mij verder helpen. Ik ben een beetje wanhopig na al het werk en posts op dit forum :shocked:

 

[snb]

Ik zou beginnen die code maar eens heel grondig te wieden.

De macro 'Naar_database' kan bijv. al gereduceerd worden tot 5 regels.

 

[jackfish]

Ga ik dat eerst proberen ... Is er een richtlijn waar ik naartoe kan werken of is het, mij overigens niet vreemd, trial and error?

 

[OctaFish]

Wellicht dat jij de code opmaak zo geweldig mooi vind, maar ik vermoed dat de meeste lezers code het liefst opgemaakt zien met de CODE knop (#)

 

[jackfish]

Nou ja Octafish, ik bedoelde het userform qua opmaak en het idee dat met het aanklikken van selectievakjes er een verslag voor je getypt wordt. En als die data ook nog eens in Excel kunnen komen, heb je data voor verder onderzoek. De opmaak van de code kan vermoedelijk beter, al heb ik mij gehouden zoveel mogelijk gehouden aan de suggesties in de posts.

 

[OctaFish]

Ik bedoelde meer het verschil tussen dit:

Private Sub Naar_Database_Click()
Call Naar_Database
End Sub

en dit

Private Sub Naar_Database_Click()
     Call Naar_Database
End Sub

 

[snb]

Zie mijn reaktie in je andere post voor minder 'obese' programmeren.

Als je bijv. 550 kolommen wil vullen gebruik je daarvoor bij voorkeur een lus en een array: dat scheelt heel veel coderegels.

 

[edmoor]

Ik bedoelde meer het verschil tussen dit:

Private Sub Naar_Database_Click()
Call Naar_Database
End Sub

en dit

Private Sub Naar_Database_Click()
     Call Naar_Database
End Sub

Het was mij ook opgevallen maar wilde er maar niks van zeggen.
Je hebt wel gelijk want inspringpunten en andere leesbaarheid kenmerken raken zo verloren.

 

[jackfish]

Kom op zeg snb,

Krijg ik thuis, op het werk en nu ook op dit forum te maken met mijn leefstijl :d Ondanks een afgeslankte code blijft het te zwaar (of maak ik misbruik van het userform?). Ik ga mij verdiepen in de array en een lus; nieuwe begrippen voor mij.

Het bestand (helaas te groot om bij te voegen) dat ik gemaakt heb is bedoeld om de adminstratieve last van verpleegkundigen, die clienten met ernstige psychiatrische problematiek jaarlijks lichamelijk moeten screenen, te verlichten. Het scheelt mij ongeveer een half uur per screening. Op jaarbasis loopt dat divisiebreed op tot ongeveer 500 uur! Die tijd kunnen we dan beter inzetten voor onze clienten.

Om de behandeling verder te verbeteren, wil ik graag meer inzicht krijgen in de problemen van de populatie en daarvoor is excel aan dit invulformulier gekoppeld. Het zou dus mooi zijn als ik het op een of andere manier werkend kreeg. Met 1 druk op de knop en een verslag en een database.

Dank voor het meedenken!

 

[edmoor]

Wijzig de regel Run "Naar_Database" eens in Call Naar_Database
Dat heb je geprobeerd zie ik net pas maar dat zou goed moeten zijn.
De fout m.b.t. de eigenschap lijkt me te komen van:
oExcel.Visible = True
Dat moet m.i. oWB.Visible = True zijn.

En heb je voor .Cells(.Rows.Count, 1) echt 550 regels staan?
Op zich zou dat geen probleem moeten zijn maar dat zou ik toch anders inrichten.
Bijvoorbeeld per textbox op het _Exit event.

 

[jackfish]

De wijziging maakt geen verschil edmoor. Inderdaad zoveel regels gemaakt en ik zou daar ook niet snel opnieuw mee beginnen. ik hoop dat ik e.e.a. werkend kan krijgen, ga eerst kijken naar arrays en lussen.

Bekijk bijlage Invulformulier Somatische Screening - rowscount - helpmij.zip

 

[edmoor]

Je kunt beter het hele document met Userform en code even posten.

 

[jackfish]

Heb ik ook gedacht, alleen is het bestand 464kb groot. Door de limiet (100kb) van dit forum kan ik het niet posten. Ik heb de code wel in een word bestand gezet (lay-out is ongelukkig) voor de beeldvorming.

Bekijk bijlage Code invulformulier in document.docx

 

[snb]

Bijv.

Sub M_snb()
  redim sn(449)
  for j=0 to 449
    sn(j)=me("ct_" & format(j,"000").Value
  next
    
  with getobject("O:\LDZ&B\Afdelingen\Somatische Screening\Rene van der Snoek\Database Somatisch Consult - Rene van der Snoek - fACT Dordrecht binnen.xlsx")
     .sheets(1).cells(rows.count).end(xlup).offset(1).resize(,550)=sn
     .close true
  end with
End Sub

Als je de userformcontrols nu de namen geeft van 'ct_001' t/m 'ct_550' is deze code voldoende om de gegevens in een array 'sn' te zetten en in 1 keer (onzichtbaar) naar het Excelbestand te schrijven.

Je gebruikt heel veel 'vaste tekst' in je code en nog een hoop doublures ook.
Die kun je veel beter in een documentvariabele zetten en in een VBA array variabele inlezen.

 

[edmoor]

Je kunt het bestand ook op een filehost zetten en hier de link plaatsen.

 

[jackfish]

Briljant, 10 regels i.p.v. 550 ...

Voor de export naar excel vraagt het benoemen van de checkboxes dan wel om een andere strategie. Niet een herkenbare naam geven zoals CBnaam maar ct_001 en dan goed een index bijhouden. Daarmee moet ik dan ook alle voorwaardelijke docvariabelen in het document hernoemen. Om de werkwijze goed voor de geest te krijgen heb ik testbestandje bijgevoegd. Alleen kan ik het niet testen omdat onderstaand deel van de code een foutmelding geeft: kan het opgegeven object niet vinden.

sn(j)=me("ct_" & format(j,"000")).Value

Hoewel ik dat stukje code wel denk te begrijpen weet ik niet wat er mis mee is.

Ik begrijp verder dat je met vaste tekst bedoeld, de tekstregel die naar het document wordt geschreven als de waarde van een chekcbox 'waar' is.

.Variables("ct_001") = IIf(ct_001, "dit is harde tekst. ", " ")

Op welke manier moet je de harde tekst in een documentvariabele zetten en met een VBA array de variabelen inlezen? Ik heb gezocht maar vrees dat ik de oplossing, net als de oplossing van sbn #14 in deze post, niet op het internet kan vinden.

Bekijk bijlage van userform via array naar excel en document - helpmij.zip

 

[jackfish]

Voor de beeldvorming het invulformulier: link extracted

 

[snb]

In de bijlage jouw bewerkte bestand.

De Userform_Initialize procedure zet je in de macromodule van het userform.

In thisdocument een macro waarmee je teksten naar de documentvariabele 'teksten' schrijft. Dit hoef je maar 1 keer te doen. Daarna blijft het bestand die gegevesn bevatten.

het eerste element van een array heeft het indexnummer 0; ik heb de namen van de vinkvakken daarop aangepast om hun namen te laten correspondeeren met de index van de array waarin ze weggeschreven worden.
De 'Caption' van een vinkvak mag natuulijk wel iets anders bevatten dan de naam van het vinkvak.

 

[jackfish]

Eerst het zuur, dan het zoetste van het zoetste begrijp ik

Nog een tweetal zure dingen:
Wanneer ik de data naar het excel-bestand wil wegschrijven geeft het de foutmelding: object vereist

.sheets(1).Cells(Rows.Count).End(xlup).Offset(1).Resize(, 550) = sn

Heeft die foutmelding te maken met de userform_Initialize procedure? Ik begrijp niet goed wat je in die procedure (datgene wat geladen moet worden moet komen bij opstarten) moet komen.


Een ander belangrijk ding. In het formulier werk ik ook met textboxen waarvan de inhoud zowel naar het document als excel bestand moet komen. Wanneer ik een textvak op het userform zet, loopt ook die code vast; ongeldige procedure-aanroep of ongeldig argument

.Variables("ct_" & Format(j, "000")) = IIf(Me("ct_" & Format(j, "000")), sn(j), " ")

Bekijk bijlage Van userform via array naar excel en document - v2.zip

 

[snb]

Debugging lijkt me een schone taak voor de vragensteller

Gebruik svp code tags, geen citaat tags:

sheets(1).Cells(Rows.Count[COLOR="#FF0000"][B],1[/B][/COLOR]).End(xlup).Offset(1).Resize(, 550) = sn

Je moet een onderscheid maken tussen userformelementen die een waarde 'waar/onwaar' opleveren en elementen die een tekstwaarde opleveren (of nog een andere waarde).