< návrat zpět

MS Excel


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

Zaslal/a 7.6.2018 22:17

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ší »
#040625
elninoslov
Á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 5
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: xlsx40625_slo-z-textu.xlsx (13kB, staženo 31x)
citovat
icon #040626
eLCHa
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
#040627
avatar
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: jpg40627_elninoslov.jpg (141kB, staženo 60x)
40627_elninoslov.jpg
citovat
icon #040629
eLCHa
@Vovka
Jak ale ta buňka pozná, že jde o maticový vzorec?

Pojmenovaný vzorec je maticový.citovat
#040630
avatar
@ eLCHa
Netušil jsem, že lze vytvořit pojmenovaný maticový vzorec. Z Vašeho příkladu to neplyne. Ale vyzkouším!citovat
#040632
avatar
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
#040633
elninoslov
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 Sub
citovat
icon #040641
eLCHa
@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
#040647
avatar
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
#040648
elninoslov
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ší »

Uživatelské menu

Nejste přihlášen(a)
avatar\n

Menu

On-line nástroje

Formulář Faktura

Formulář Faktura IV

Oblíbený formulář Faktura byl vylepšen a rozšířen.
Více se dočtete zde.

Aktivní diskuse

odpocet a storno tl.

PavDD • 28.3. 8:53

odpocet a storno tl.

Začátečník • 26.3. 14:39

odpocet a storno tl.

PavDD • 26.3. 10:22

odpocet a storno tl.

elninoslov • 26.3. 7:50

odpocet a storno tl.

PavDD • 26.3. 7:26

odpocet a storno tl.

elninoslov • 25.3. 22:34

odpocet a storno tl.

Začátečník • 25.3. 15:09