Arrays en Excelformules

Status
Niet open voor verdere reacties.
@ snb
Zalig leesvoer en grondig zoals trouwens je hele website. Mijn complimenten voor al het werk en tijd dat je er in gestoken hebt.
Dit verdient meer dan zomaar een pluim.

Ik heb echter 1 bedenking.(eigenlijk meer een observatie)
Ondanks verhitte discussies lijkt er geen verschil te bestaan in het effekt van de ene of de andere schrijfwijze.
Klik om te vergroten...
Ik weet niet of het hier relevant is, maar uit eigen ervaring heb ik ondervonden dat bij het gebruik van Match er toch wel degelijk een verschil bestaat.
Wanneer bij het zoeken geen overeenkomst gevonden wordt geeft Worksheetfunction.Match een Error in je code en gaat in Debug. Gebruik je hier echter Application.Match dan kan je door gebruik te maken van IsError de fout opvangen en je code laten doorlopen.
Code: 
Sub tst()

    x = WorksheetFunction.Match(6, Columns(1), 0) 'bij het zoeken naar ontbrekend getal 6 in kolom A
    
End Sub

Sub tst2()

    x = Application.Match(6, Columns(1), 0)
    If Not IsError(x) Then 'of If IsError(x) Then 
        '.....
    End If
    
End Sub
Zoals ik al zei misschien niet relevant hier maar toch belangrijk voor iedereen denk ik om dit even te melden.
Kijk al uit naar een vervolg.
:thumb::thumb::thumb:
 
Laatst bewerkt:
@Rudi,

Bedankt, goede suggestie.
Ik ga uitzoeken of dat misschien geldt voor alle formules die een fout kunnen genereren (zoal bijv. ook Vlookup, etc)
 
Inderdaad interessante kost. Ik wist niet dat je arrays in worksheetfuncties kon gebruiken. Zoals Rudi ook al aan gaf is de foutafhandeling een belangrijk verschil tussen het gebruik van Application en WorksheetFunction. Een ander klein verschil is dat je in het laatste geval gebruik kunt maken van Intellisense. Daarnaast blijkt dat er soms verschillen kunnen ontstaan bij de uitkomsten van currencies. Zie bijvoorbeeld https://social.msdn.microsoft.com/Forums/en-US/cca85cb2-9973-45fa-8486-63b0972b0eed/worksheetfunction-vs-application

Daarnaast ontbrak er volgens mij een stukje in het volgende voorbeeldje:
Code: 
sn=sheet1.range("A1:A10")
MsgBox Evaluate("sum(" & Join(Application.Transpose(sn), ",") [COLOR="#FF0000"]& ")")[/COLOR]

Dit voorbeeldje geldt natuurlijk alleen voor basisgegevens die in een kolom staan. Als de gegevens in een rij staan had ik verwacht dat het volgende zou werken, maar dat blijkt niet het geval:
Code: 
sn = sheet1.Range("A1:J1")
MsgBox Evaluate("sum(" & Join(sn, ",") & ")")
Dit kreeg ik alleen maar als volgt werkend:
Code: 
sn = sheet1.Range("A1:J1")
MsgBox Evaluate("sum(" & Join(Application.Transpose(Application.Transpose(sn)), ",") & ")")
 
Of:
Code: 
MsgBox Evaluate("sum(" & Join(application.index(sn, 1, 0), ",") & ")")
 
@Rebmog

Dank voor je reaktie: Ik zal het ontbrekende aanvullen.

Iedere range die je uit een werkblad in een variabele plaatst is, tenzij het een enkele cel is, een meer-dimensionele array. (schrijven is weer een ander verhaal).
Join is alleen maar van toepassing op een 1-dimensionele array.
Dus zowel een vertikale als een horizontale 2-dimensionele array zal eerst geconverteerd moeten worden naar een 1-dimensionele voordat je de methode 'join' kunt toepassen.
Zowel jouw methode: transpose(transpose, als die van HSV: Index(sn,j,0) kun je daarvoor gebruiken.
 
Het verschil waarop Rudi mij attendeerde is veel ingrijpender dan ik aanvankelijk dacht.
De methode application.formule biedt vele malen meer mogelijkheden om van Excelformules gebruik te maken.
Application.worksheetfunction.formule en Worksheetfunction.formule schieten bij het minste of geringste in de stress: een VBA foutmelding en onderbreking van de code.
Ze kunnen niet alleen een error (bijv. niet gevonden) van een Excelformule niet aan, ze zijn ook niet in staat als matrixformule te funktioneren.
Dat kan Application.formule gelukkig wel.

Dat biedt (althans voor mij) nieuw perspektieven.
In de gewijzigde bijdrage zie je dan ook toepassingen van Application.Find en Application.Substitute, die ik voorheen niet als reële opties had beschouwd.
Mijn conclusie op dit moment is: vergeet die worksheetfunction.formule variant, gebruik alleen de Application.formule variant.

De suggestie van Rudie was/is natuurlijk welkom, maar heeft me wel veel werk/hoofdbrekens gekost.

@Rebmog
Ik vind 'intelli'sense te grillig om als argument te gebruiken in het betoog.
Om onverklaarbare redenen worden de ene keer wel alternatieven getoond en de andere keer niet, en vaak worden ook alternatieven getoond die ronduit fout zijn.
Valt meer in de categorie 'intelli'nonsense.
Ik blijf dan maar de voorkeur houden voor F2, de object bladeraar.
 
Laatst bewerkt:
@WB

Ik pak je nog wel eens terug ;)
 
Ik had me al aangemeld voor ik reageerde.
Zeer interessante stof.
Er staan hier en daar nog wat schrijffoutjes op de site, maar dit terzijde.

Ik volg het met plezier.
 
Hoi,

Wat is de reden dat application.index de beperking heeft van het aantal rijen van Excel 2003.
Ik was met een stukje bezig op een ander forum en ik wilde eens kijken of ik de snelheid nog wat op kon krikken.
De resize even ingesteld, de currentregion is 80.000 rijen.

Verhoog de resize met een rij en je krijgt fout 13.
Code: 
With Worksheets("Tabelle1")
    sn = .Cells(1).CurrentRegion.Resize(65536, 11)
      For j = 2 To UBound(sn)
         If sn(j, 11) = "x" Then
'              den Ordnungsbegriff (Key) zusammenstellen, 'Spaltenzahl anpassen
a_sn = Application.Index(sn, j, 0))
 
MS is vergeten bij de overgang van E2003 naar E2007 de grenzen van transpose en index aan te passen aan de gewijzigde omvang van het werkblad.
 
Niet erg handig van een miljardenbedrijf.
Ik zat even op je site te kijken of ik iets verkeerd deed terwijl ik het vorige week nog gebruikte.
Zodoende kwam ik de beperking tegen en kon er niet zo snel iets over vinden.

Bedankt voor je snelle reactie.
 
@HSV

Okay, okay, ik snap de kritiek. Zal de website snel aanvullen.
 
Kritiek op zo'n top-site.
Ik zou niet durven.
 
Status
Niet open voor verdere reacties.
Steun Ons

Nieuwste berichten

Terug
Bovenaan Onderaan