MS Excel

Nikoho tady snad neohromím informací, že maticový vzorec se ve VBA vkládá do vlastnosti Range.FormulaArray v notaci R1C1. Vybaven touto znalostí jsem tak učinil. Při vkládání vzorce pro věčný kalendář jsem si přitom tvrdě natloukl nos. Teprve když jsem si pořádně přečetl Help, zjistil jsem, že maximální délka řetězce pro FormulaArray je 255 znaků. A ten vzorec jich ale měl 261 !!! Důvod pro toto omezení nechápu, protože maticový vzorec může být prakticky i podstatně delší.
Vůbec nejdelší maticový vzorec, který jsem objevil, pochází z dílny elninoslov. Jeho úkolem je extrahovat číslo z řetězce s alfanumerikou. Vzorec má v české lokalizaci 439 znaků a skutečně řádně funguje. Ale do vlastnosti FormulaArray ho pro jeho délku pomocí VBA vložit nelze.
Vyjadřuji hluboký obdiv autorovi, že takový funkční vzorec dokázal sestavit. Kladu si ale otázku, jestli je takové řešení účelné. Vsadím se, že bych dokázal napsat UDF ve VBA, která by umělo všechno, co ten megavzorec, a které by se skládalo z menšího počtu znaků, než ten vzorec. A hlavně - bylo by nepoměrně srozumitelnější...

Zaslat odpověď >

Strana: 1 2 další »

elninoslov(8.6.2018 2:03)#040625

Áno vspomínam si, myslím že toto je ono. Metóda 1 má v SK 318 znakov. V CZ má až 439 ?
Tož já sa v tom niekedy pekne vyžívam

UDF by bola podstatne jednoduchšia a čitateľnejšia to je jasné. Tak ale možno existujú iné jednobunkové vzorce, jednoduchšie, netuším ...

Příloha: xlsx

40625_slo-z-textu.xlsx (13kB, staženo 32x)

citovat

eLCHa(8.6.2018 8:18)#040626

Omezení se dá obejít. Např. takto: ActiveWorkbook.Names.Add "TestCSE", "=IF($a$3="""","""",RIGHT(REPT(""0"",LEN($a$3))&SUM(IFERROR(MID($a$3,LARGE(IF(ISERROR(--MID($a$3,LEN($a$3)+1-ROW($A$1:INDEX($A:$A,LEN($a$3))),1)),FALSE,LEN($a$3)+1-ROW($A$1:INDEX($A:$A,LEN($a$3)))),ROW($A$1:INDEX($A:$A,LEN($a$3)))),1),0)*POWER(10,ROW($A$1:INDEX($A:$A,LEN($a$3)))-1)),COUNT(--MID($a$3,LEN($a$3)+1-ROW($A$1:INDEX($A:$A,LEN($a$3))),1))))+IF($a$3="""","""",RIGHT(REPT(""0"",LEN($a$3))&SUM(IFERROR(MID($a$3,LARGE(IF(ISERROR(--MID($a$3,LEN($a$3)+1-ROW($A$1:INDEX($A:$A,LEN($a$3))),1)),FALSE,LEN($a$3)+1-ROW($A$1:INDEX($A:$A,LEN($a$3)))),ROW($A$1:INDEX($A:$A,LEN($a$3)))),1),0)*POWER(10,ROW($A$1:INDEX($A:$A,LEN($a$3)))-1)),COUNT(--MID($a$3,LEN($a$3)+1-ROW($A$1:INDEX($A:$A,LEN($a$3))),1))))"
ActiveCell.Formula = "=TestCSE"citovat

Vovka(8.6.2018 9:46)#040627

Zajímavé řešení přes Names. Jak ale ta buňka pozná, že jde o maticový vzorec?
Napadá mne jiná cesta přes Names. Udělal jsem si rozbor vzorce od elninoslov, který ukázal, že vzorec obsahuje čtyři shodné dlouhé sekvence. Uložením té sekvence do Names a její volání do konečného vzorce by ho zkrátilo nejmíň na polovinu. Svůj automatický rozbor obsahu původního vzorce přikládám v podobě obrázku.

Příloha: jpg

40627_elninoslov.jpg (141kB, staženo 61x)

citovat

eLCHa(8.6.2018 10:36)#040629

@Vovka
Jak ale ta buňka pozná, že jde o maticový vzorec?

Pojmenovaný vzorec je maticový.citovat

Vovka(8.6.2018 11:06)#040630

@ eLCHa
Netušil jsem, že lze vytvořit pojmenovaný maticový vzorec. Z Vašeho příkladu to neplyne. Ale vyzkouším!citovat

Vovka(8.6.2018 15:33)#040632

Vyzkoušel jsem. Vytvořit přímo pojmenovaný maticový vzorec, uložený jako obsah Name, se mi ale nepovedlo. Co ovšem šlo, bylo uložení obsahu vzorce (bez maticového označení) do názvu (např. Honza). Teprve při vložení vzorce do buňky (=Honza) musím tento vzorec potvrdit Ctrl+Shift+Enter, aby se to začalo chovat maticově.
Podobnou možnost využívám i já, aniž bych použil Names. Český obsah budoucího maticového vzorce vytvořím někde ve VBA a následně ho přenesu ve VBA do buňky jako FormulaLocal. Musím ale skončit tím, že ručně pomocí Ctrl+Shift+Enter z toho vzorce udělám vzorec maticový.

Právě ten ruční konec dělá z automatu poloautomat. Tím takové řešení (proti vložení do FormulaArray) je nedotažené. Zatím ale pro maticové vzorce, delší než 255 znaků, neznám lepší možnost.citovat

elninoslov(8.6.2018 17:35)#040633

Ak chcete dať do bunky C3 dlhý maticový vzorec, a môžete použiť pri tom DefName, tak takto to ide:
Sub pokus()
With ActiveSheet
.Range("C3").Formula = "=IF(A3="""","""",RIGHT(REPT(""0"",LEN(A3))&SUM(IFERROR(MID(A3,LARGE(IF(ISERROR(--MID(A3,LEN(A3)+1-ROW($A$1:INDEX($A:$A,LEN(A3))),1)),FALSE,LEN(A3)+1-ROW($A$1:INDEX($A:$A,LEN(A3)))),ROW($A$1:INDEX($A:$A,LEN(A3)))),1),0)*POWER(10,ROW($A$1:INDEX($A:$A,LEN(A3)))-1)),COUNT(--MID(A3,LEN(A3)+1-ROW($A$1:INDEX($A:$A,LEN(A3))),1))))"
.Names("MATICOVY_VZOREC").RefersToR1C1 = Range("C3").FormulaR1C1
.Range("C3").Formula = "=MATICOVY_VZOREC"
End With
End Subcitovat

eLCHa(8.6.2018 20:31)#040641

@Vovka
Vyzkoušel jsem. Vytvořit přímo pojmenovaný maticový vzorec, uložený jako obsah Name, se mi ale nepovedlo.

Nevím jak jste to zkoušel. Mně to funguje, vždy fungovalo (vzorec vložený do názvu se mi vždy počítá jako maticový bez stisku ctrl+shift+enter).
Ten příklad je z přílohy, kterou sem dal elninoslov (jen je to 2x aby to bylo delší než 255 znaků).citovat

Vovka(9.6.2018 1:27)#040647

Musím přiznat, že pojmenovaný vzorec s "maticovým charakterem" se mi po zavolání opravdu chová jako maticový. Současně platí moje dlouholetá zkušenost, že pojmenované vzorce, které takový charakter nemají, se po zavolání chovají jako běžné nematicové vzorce.
Důvod, proč jsem si maticového chování pojmenovaných vzorců nevšiml, je velmi prostý. Maticové vzorce používám jen zcela výjimečně a vůbec už mne nenapadlo takový vzorec vložit do Name. Teď jsem hledal, jak to docílit a žádný speciální obrat jsem neobjevil. Nenapadlo mne, že není co objevovat, protože se to udělá samo. Nevzpomínám si, že bych o tom v nějakém manuálu našel zmínku.citovat

elninoslov(9.6.2018 2:20)#040648

Hm, no u mňa ide zapísať dlhý vzorec do Name iba takto:
a) pri vytváraní Name (keď neexistuje), použiť normálny vzorec A1
b) pri prepisovaní vzorca v existujúcom Name nejde použiť RefersTo = vzorec A1, ale zožerie to iba RefersToR1C1 = vzorec R1C1.

To je zaujímavé, lebo R1C1 je dlhší ako A1.

Ak teda mám len vzorec A1, tak ho pomocne vložím do bunky ako Formula, a odtiaľ ho načítam do do Name ako R1C1
Name().RefersToR1C1 = Bunka.FormulaR1C1.

Teda ide o to, či sa mi ten vzorec ľahšie skladá ako A1 alebo R1C1, potom záleží či budú s tým opletačky alebo nie pri vkladaní do Name.citovat

Strana: 1 2 další »

Téma: VBA nepřeje maticovým megavzorcům

Uživatelské menu

Menu

Formulář Faktura

Formulář Faktura IV

Helios iNuvio

On-line nástroje