Formules met gestructureerde referenties in tabellen VBA

[wmr]

Hallo allemaal,

Ik ben mij aan't verdiepen in gestructureerde referenties in tabellen. Dat lukt op zich aardig.

Het lukt mij ook om zo'n formule op te nemen en die in een bepaalde cel in te voeren.

Wat nog niet lukt, is om met application.worksheetfunction een variabele te vullen en de waarde daarvan in een cel te zetten.
Vooral als je veel formules met Vert.zoeken hebt kan je bestand sloom worden. Dus op deze manier zorg ik ervoor dat ik geen formules maar alleen uitkomsten heb.

In bijgaande bestand heb ik een paar eenvoudige formules, te beginnen met een vermenigvuldiging Aantal*Prijs in cel I5, dan Som in cel K5.
Vervolgens Vert.zoeken in cel F5 en G5.

Ik moet even op weg worden geholpen. In VBA zie je wat ik heb geprobeerd en niet werkt.

Over mijn vragen rond application.worksheetfunction kan ik niets vinden op internet. Dus als je een goede site weet ....

Alvast bedankt voor de hulp.
Willem

 

[snb]

Ik snap je 'probleem' niet.
Als je goed gebruik maakt van de Listobject-eigenschappen heb je geen VBA nodig.

 

[mulderwme]

Hallo snb,

Het gaat erom dat ik iets nieuws aan't leren ben. Waar ik vroeger in VBA uit de voeten kon met worksheetfunctions, kan ik dat nu nog niet.

Groet
Willem

 

[snb]

VBA is bedoeld om in het werkgeheugen te werken, niet om gebruikersinteraktie met het werkblad te imiteren.

Als in kolom 2 de nettoprijs
Als in kolom 3 het aantal items
Dan in kolom 4 de prijs ex. BTW
En in kolom 5 de prijs incl. BTW

Sub M_snb()
   sn=sheet1.listobjects(2).databodyrange

   for j=1 to ubound(sn)
     sn(j,4)=sn(j,2)*sn(j,3)
     sn(j,5)=sn(j,4) *1.21
   next

   sheet1.listobjects(2).databodyrange=sn
End Sub

Interaktie met het werkblad vertraagt de macro enorm (screenrefreshing, recalculation, etc.).
Het voordeel van VBA is nu juist de ultrasnelle verwerking.


NB. Worksheetfunction geeft bij bepaalde Excelformules onvoorspelbare foutmeldingen. Je kunt beter het préfix 'Application' gebruiken.
Je kunt bijv. beter Application.Sum gebruiken dan Worksheetfunction.Sum

 

[mulderwme]

Dank je wel snb.

De prefix Application gebruik ik idd altijd.

Wat ik wil, gebruik ik bijv. heel vaak als ik gebruikers iets via een venster wil laten invoeren, bij grotere applicaties.

Dus jouw antwoord is nog niet helemaal een antwoord op mijn vraag.

Groet
Willem

 

[snb]

Wat ik wil, gebruik ik bijv. heel vaak als ik gebruikers iets via een venster wil laten invoeren, bij grotere applicaties.

Geef svp eens een voorbeeld daarvan.
Bedoel je met 'venster' een Userform ?

 

[mulderwme]

Ik bedoel idd een userform.

In VBA heb ik precies aangegeven om welke voorbeeld formules het gaat. Daar moet ik mee op weg worden geholpen.
Dan kom ik zelf wel uit Aantal(), Aantal.als(), Index(), Vergelijken() enz.

 

[snb]

Kijk goed in de hulpbestanden van de Excel-formules. Sommge formules vereisen een Range als rekenelement. VBA kan die per definitie niet aanleveren. Bijv. de formule Rank(range1;range2). Dat ga je met VBA niet redden.

In jouw voorbeeld kun je dit gebruiken:

Sub Formule_som()
  sn = [TblOrder[Prijs]]
  sp = [TblOrder[Aantal]]
  st = Application.SumProduct(sn, sp)
End Sub

Als je in [A18:A26] cijfers hebt staan:

Sub M_snb()
    MsgBox Join(Application.Transpose(Application.Rank([A18:A26], [A18:A26])), vbLf)   ' werkt
    
    sp = [A18:A26]
    MsgBox Join(Application.Transpose(Application.Rank(sp, sp)), vbLf)     '   werkt niet
End Sub

 

[mulderwme]

Ik ben nog wat aan 't experimenteren geweest en de functie SUM en Vlookup werken.

Hierbij het nieuwe bestand.

 

[snb]

Je gebruikt nog steeds ten onrechte Worksheetfunction.

Dit loopt, maar is volstrekt overbodig in een dynamische tabel:

Totaal = Application.Sum([TblOrder[Aantal]])

 

[mulderwme]

Ik ben nog me nog maar net aan't verdiepen in dynamische tabellen. Dus zie het maar als onderdeel van mijn leerproces.