Microsoft Excel

Herbers Excel/VBA-Archiv

VBA FormulaArray nur 256 Zeichen | Herbers Excel-Forum


Betrifft: VBA FormulaArray nur 256 Zeichen von: Werner Brinkmann
Geschrieben am: 25.10.2008 21:25:22

Hallo Zusammen,

ich möchte Formeln einer ziemlich umfangreichen Jahresarbeitsplanung in Excel, per VBA-Code korrigieren, da diese Datei(en) x-mal vervielfältigt und "beplant" sind. (über 100 Dateien mit mittlerweile über 28.000 Formeln je Datei)
Bei einer bestimmten Matrixformelgröße, über 256 Zeichen, VBA Code:

... .FormulaArray = "=(Sum(($B$1='Bestand 2008'!$Q$3:$Q$5210)*($B$2='Bestand 2008'!$C$3:$C$5210)*(CD11='Bestand 2008'!$R$3:$R$5210)*(CB11='Bestand 2008'!$F$2:$N$2)*('Bestand 2008'!$F$3:$N$5210))+Sum(($B$1='Bestand 2008'!$Q$3:$Q$5210)*($B$2='Bestand 2008'!$C$3:$C$5210)*(CE11='Bestand 2008'!$R$3:$R$5210)*(CB11='Bestand 2008'!$F$2:$N$2)*('Bestand 2008'!$F$3:$N$5210)))*CC11"

bekomme ich diese Fehlermeldung:
"Laufzeitfehler '1004':
Die FormulaArray-Eigenschaft des Range-Objektes kann nicht festgelegt werden.


In den jew. Zeilen konnten die Matrix/Arrayformel so eingegeben werden.

ZelleFormel
CF11{=(SUMME(($B$1='Bestand 2008'!$Q$3:$Q$5000)*($B$2='Bestand 2008'!$C$3:$C$5000)*(CD11='Bestand 2008'!$R$3:$R$5000)*(CB11='Bestand 2008'!$F$2:$N$2)*('Bestand 2008'!$F$3:$N$5000))+SUMME(($B$1='Bestand 2008'!$Q$3:$Q$5000)*($B$2='Bestand 2008'!$C$3:$C$5000)*(CE11='Bestand 2008'!$R$3:$R$5000)*(CB11='Bestand 2008'!$F$2:$N$2)*('Bestand 2008'!$F$3:$N$5000)))*CC11}


Kennt hier jemand vielleicht einen Weg, wie Arrayformeln größer 256 Zeichen, per VBA-Code gepatcht werden können?

Gruß Werner Brinkmann

  

Betrifft: AW: VBA FormulaArray nur 256 Zeichen von: Reinhard
Geschrieben am: 25.10.2008 21:58:06

Hi Werner,

du könntest für

='Bestand 2008'!$Q$3:$Q$5210

usw. einen Namen vergeben und die namen in die Formel einfüügen *schätz*

Gruß
Reinhard


  

Betrifft: AW: VBA FormulaArray nur 256 Zeichen von: Werner Brinkmann
Geschrieben am: 25.10.2008 22:13:26

Hallo Reinhard,

danke für die Info, aber auch dieser Weg klappt nicht, da Excel die Variable bei der Zuweisung direkt wieder auflöst, schade.

Trotzdem danke

Gruß Werner


  

Betrifft: AW: VBA FormulaArray nur 256 Zeichen von: Reinhard
Geschrieben am: 25.10.2008 22:47:25

Hallo Werner,

okay, neuer Versuch , scheinbar wird hier nicht umgewandelt:

Sub tt()
ActiveWorkbook.Names.Add Name:="Ä", RefersToR1C1:="=Tabelle1!R1C4"
Range("A1").Formula = "=Ä"
Range("A1").FormulaArray = Range("A1").Formula
End Sub


Gruß
Reinhard


  

Betrifft: AW: VBA FormulaArray nur 256 Zeichen von: Werner Brinkmann
Geschrieben am: 26.10.2008 08:46:30

Hallo Reinhard,

der Lösungsansatz hört sich gut an, muss ich ausprobieren.
Komme aber erst gegen Abend dazu, melde dann das Ergebnis.

Danke aber schon mal.

Gruß
Werner


  

Betrifft: AW: VBA FormulaArray nur 256 Zeichen von: Werner Brinkmann
Geschrieben am: 26.10.2008 21:55:42

Hallo Reinhard,

ich habe soeben Deine Idee umgesetzt.
Funzt leider nicht, auch hier liegt die Grenze bei 256 Zeichen, leider.

Weiß evtl. noch jemand einen Lösungsansatz?
Die zu patchenden Arrayformeln haben über 400 Zeichen und konnten manuell so ohne Probleme eingetragen werden.

Danke und Gruß
Werner


  

Betrifft: AW: VBA FormulaArray nur 256 Zeichen von: Werner Brinkmann
Geschrieben am: 26.10.2008 21:57:14

... Frage ist noch offen

Gruß
Werner


  

Betrifft: AW: VBA FormulaArray nur 256 Zeichen von: Daniel
Geschrieben am: 26.10.2008 22:58:01

Hi
ich vermute mal,m du hast Reinhard nicht richtig verstanden, ich versuchs mal etwas besser zu beschreiben:

du hast in deiner Formel relativ viele lange Zellbezüge wie "'Bestand 2008'!$Q$3:$Q$5210"
wenn du jetzt jedem verwendeten Zellbezug einen kurzen Excelnamen zuweist (unter EINFÜGEN - NAMEN - DEFININEREN), dann kannst du diesen Namen dann in deiner Formel anstelle des Zellbezugs verwenden und kommst somit u.U. unter die 256-Zeichengrenze.

als beispiel sieht das dann so aus:

in Excel definierte Namen:

B8Q:   'Bestand 2008'!$Q$3:$Q$5210
B8R:   'Bestand 2008'!$R$3:$R$5210
B8C:   'Bestand 2008'!$C$3:$C$5210



Originalformel (ausschnitt):
... .FormulaArray = "=(Sum(($B$1='Bestand 2008'!$Q$3:$Q$5210)*($B$2='Bestand 2008'!$C$3:$C$5210)*...
Formel unter Verwendung der Namen(gleicher ausschnitt):
... .FormulaArray = "=(Sum(($B$1=B8Q*($B$2=B8C)*...

vielleicht gelingt es dir ja über diesen Trick, unter 256 Zeichen zu kommen.
falls das noch nicht reicht, man kann nicht nur Zellbereiche als Namen definieren, sondern sogar ganze Formeln, dh wenn es dir gelingt, Teilformeln als Namen abzubilden, kannst du die endgültige Formel noch weiter verkürzen.

Wenn das alles nichts hilft, dann bleibt dir wohl nur der Weg, die Formel mit allen Parametern als UDF nachzubauen und diese dann in der Exceldatei zu verwenden.

Gruß, Daniel


  

Betrifft: AW: VBA FormulaArray nur 256 Zeichen von: Werner Brinkmann
Geschrieben am: 27.10.2008 09:34:18

Hallo Daniel,
hallo Reinhard.

das war's!
Ich war auf den falschen Tripp und wollte in der entspr. Zelle die Arrayformel immer als Klartext stehen haben.
Die Lösung mit den Bezügenzuweisungen über Namen ist genial, alleine für eine weitere Anpassung dieser Jahresarbeitsplanung (JAP). Es sind bei der Pflege einfach zu viele Personen in unterschiedlichen Geschäftsbereichen an diesem Elaborat beteiligt, so, dass auch solch "unsinnige" Tabellenblattbezeichnung "Bestand 2008" zustande gekommen sind. Diese JAP soll mehrere Jahre sukksessive fortgeführt und durch itteraitive Prozesse immer näher an die wirklichkeit herangeführt werden.
Dieser Tipp bringt mich wirklich ein ganzes Stück weiter.
Vielen Dank dafür.

Gruß
Werner


Beiträge aus den Excel-Beispielen zum Thema "VBA FormulaArray nur 256 Zeichen"