• Úvod
• Excel návod
• Excel šablony
• Nástěnka
• Mobility
• Tipy na výlet
• Galerie
• Fórum

MS Excel

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

Zaslal/a Vovka 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:  « předchozí  1 2

Vovka(9.6.2018 8:16)#040649

Nevím, jak pro koho, ale pro mne je toto vlákno velmi výživné. Rozbor chování Names byl pro mne objevný dík příspěvkům od elninoslov a eLCHa.
Obrázek, který si tady stáhlo víc lidí, zpracovává jiný maticový vzorec, než který lze nalézt v sešitě, jejž přiložil elninoslov. Jde o odpověď na dotaz v jiném fóru z 31.1.2018. Ten vzorec v lokálním řádkovém zobrazení zní:
{=--DOSADIT(DOSADIT(ZPRAVA(ZLEVA(A3;DÉLKA(A3)-POZVYHLEDAT(PRAVDA;ČÁST(A3;DÉLKA(A3)+1-ŘÁDEK(A$1:INDEX(A:A;DÉLKA(A3)));1)=" ";0));POZVYHLEDAT(PRAVDA;ČÁST(ZLEVA(A3;DÉLKA(A3)-POZVYHLEDAT(PRAVDA;ČÁST(A3;DÉLKA(A3)+1-ŘÁDEK(A$1:INDEX(A:A;DÉLKA(A3)));1)=" ";0));DÉLKA(ZLEVA(A3;DÉLKA(A3)-POZVYHLEDAT(PRAVDA;ČÁST(A3;DÉLKA(A3)+1-ŘÁDEK(A$1:INDEX(A:A;DÉLKA(A3)));1)=" ";0)))+1-ŘÁDEK(A$1:INDEX(A:A;DÉLKA(ZLEVA(A3;DÉLKA(A3)-POZVYHLEDAT(PRAVDA;ČÁST(A3;DÉLKA(A3)+1-ŘÁDEK(A$1:INDEX(A:A;DÉLKA(A3)));1)=" ";0)))));1)>"9";0)-1);",";"");".";",")}

Posouzení čitelnosti a rozluštitelnosti vzorce nechávám na vás.citovat

eLCHa(9.6.2018 8:32)#040650

@elninoslov

Nejsem si úplně jistý, zda jsem správně pochopil to, co jste napsal. Takže pokud je má odpověď mimo, tak...

Přes vba mi jde vzorec do názvu vložit pouze v anglickém tvaru. Což není problém, protože během psaní kódu si ho vytvořím v listu, odladím a anglický tvar vložím do kódu. V běhu kódu se tím vyhnu mezikroku na listu (který vždy vyvolá kalkulaci).
Pokud chci odkaz v názvu změnit (název už existuje), tak to nedělám přes refersto, ale název odstraním a vytvořím znovu.citovat

elninoslov(9.6.2018 21:43)#040656

No a už sme skoro na rovnakej "vlne" v tom čo hovoríme. :)

Vo VBA :
-do Name ide vložiť dlhý vzorec iba pri vytváraní Name, alebo keď je Name už vytvorený, tak
a) "chmatom" cez bunku
b) vymazaním Name a opätovným vytvorením

-do bunky nejde vkladať dlhý vzorec ako FormulaArray, a treba použiť Name

-vzorce kratšie ako 255 znakov nieje problém vkladať do Name ani pri vytvorení ani cez RefersTo, ale ani ako maticové cez FormulaArray priamo do bunky

-maticový vzorec sa nikdy nezadáva s {}

-každý maticový vzorec v Name je automaticky maticový, bez akéhokoľvek zásahu ako {}, rovnako ako každý vzorec vložený do bunky cez FormulaArray

PS: dlhý > 255 znakovcitovat

Vovka(12.6.2018 16:22)#040682

@ elninoslov
Ten rozklad pro vkládání dlouhých maticových vzorců je velmi cenný a ode mne má potlesk! Zasloužil by si, aby se dostal do manuálů k Excelu, jak je srozumitelně napsaný.citovat

Vovka(20.6.2018 19:49)#040755

Tak nevím, jestli jsem nepřechválil. Je možné, že jsem si něco z vašeho rozboru vyložil blbě. Cesta přes Names mi ale vázne. Nikoliv v tom, že bych tím postupem nedokázal dlouhý maticový vzorec vložit. To šlape. Cesta přes NAMES mi ale zaplevelí vzorec odkazy na jména listu, a navíc nějak zvláštně posouvá relativní adresy ve vzorci. A aby toho nebylo málo, pokud jde o jeden maticový vzorec pro celou oblast buněk, vznikne mi tím postupem extra vzorec pro každou buňku v dané oblasti. Asi to používám v jiném kontextu než vy, protože jinak byste si jistě takových "drobniček" dávno sami všimli.
Zatím jsem tedy musel setrvat u svého postupu, který výše uvedená zvěrstva nedělá, který ale vyžaduje potvrzení importovaného dlouhého maticového vzorce zkratkou Ctrl+Shift+Enter.citovat

Strana:  « předchozí  1 2

Uživatelské menu

Nejste přihlášen(a)

• přihlásit
• registrace

\n

Menu

On-line nástroje

Formulář Faktura

Aktivní diskuse