Calc: Summanden in Zelle zählen

[Enzol]

Hallo Leute.

Ich bin dabei eine recht aufwändige MS-Excel-Tabelle in OpenOffice zu konvertieren.
Läuft soweit auch alles ganz gut bis auf, wie sollte es anders sein, ein Makro das ich verwende,
um die Anzahl der Summanden in einer Zelle auszulesen.

Ich habe also diverse Zellen in denen der user Werte im Format

=[wert1]+[wert2]+[wert3] ...

einträgt.

Das Makro 'anzahlsummanden' zählt die Summanden in einer Zelle und gibt die Anzahl aus,
mit der dann anderswo weiter gearbeitet wird. Hier der originale Code in VBA:

Code: Alles auswählen

Public Function anzahlSummanden(zelle As Range)
    Dim formel As String
    Dim zaehler As Integer
    zaehler = 0
    
    'wenn Formel vorhanden
    If zelle.HasFormula Then
    
        'Formel als Text holen
        formel = zelle.Formula
        
        'durch Text durchgehen und Anzahl der '+' zählen
        Dim i As Integer
        For i = 1 To Len(formel)
            If Mid(formel, i, 1) <> "+" Then
                zaehler = 1
            End If
           
        Next
        
        For i = 1 To Len(formel)
            If Mid(formel, i, 1) = "+" Then
                zaehler = zaehler + 1
            End If
           
        Next
    End If
           
    'Ergebnisrückgabe
    anzahlSummanden = zaehler
End Function

Nun habe ich schon eine ganze Weile im Basic-Editor rumgefummelt und diverse Hinweise im Netz verfolgt,
komme aber nicht zu einem funktionierenden Makro ... zum verzweifeln!

Kann mir jemand weiterhelfen?

Grüße

Enzol



Moderation,4: BASIC-Fragen im zuständigen Unterforum stellen; verschoben und Betreff angepasst; CODE anstelle QUOTE tags gesetzt

[Karolus]

Hallo
Darfs auch ne einfache Calc-formel sein?

Code: Alles auswählen

=LÄNGE(A1)-LÄNGE(WECHSELN(A1;"+";""))+1

oder muss es Basic sein ?

Code: Alles auswählen

function anzplus ( ausdruck as string )
 
 	tmp = ubound (split(ausdruck, "+"))
 	anzplus = tmp +1
 	
end function

Gruß Karo

[F3K Total]

Hallo,
habe in deinen Code geschaut: Mein Calc kennt kein

Code: Alles auswählen

.HasFormula

Daher habe ich die Zeile in

Code: Alles auswählen

If zelle.Formula <> "" Then

geändert, dann läufts bei mir.

Gruß R

[Karolus]

Hallo
Entschuldige, ich hatte übersehen das du eine "richtige" Zellformel auswerten möchtest.

zu 1. Formel:
Ersetze in der Formel A1 durch FORMEL(A1)

zu 2. [ siehe Edit ]
Funktioniert in Calc so nicht, weil aus einem Aufruf =ANZAHLSUMMANDEN(A1) kein vollständiges Zellobjekt übergeben wird, sondern nur der Zellinhalt als Text/Wert ohne Zugriff auf die zugrundeliegende Formel.

@F3k: siehe 2.
Edit zu 2.
Anscheinend ist die VBA-unterstützung doch schon weit genug, hier funktioniert auch:

Code: Alles auswählen

Option VBASupport 1
Function anzahlSummanden(zelle As Range)
        Dim zaehler As Integer
        zaehler = 0
        If zelle.HasFormula  Then
           formel = zelle.Formula()           
           zaehler = ubound( split( formel, "+")) +1
        End If
        anzahlSummanden = zaehler
End Function

Gruß Karo

[Enzol]

Hallo nochmal.

1. Formel

Funktioniert jetzt wunderbar, danke. (So ein "Formel holen" gibt es in Excel ohne Makros gar nicht oder?)
Das Problem ist nur, dass die Ermittlung der Summanden nur ein Teil einer ganzen Verkettung von Berechnungen ist,
die diverse Verhaltensweisen und Fehlermöglichkeiten des users in anderen Zellen berücksichtigen müssen.
Ich versuche gerade das irgendwie mit zahllosen WENN-Schachtelungen und Hilfszellen hinzubekommen,
ist aber auf halber Strecke schon ziemlich Kraut- und Rübig.


2. Makro

Eine Makro-Lösung wäre daher wohl tatsächlich das beste, wobei die oben gepostete VBA-Lösung ideal ist, da sie:
- den Zähler von vornherein auf 0 setzt
(sollte die entsprechende Zelle also noch gar keine eingegeben Daten enthalten, kommt 0 und nicht N/V oder FALSCH zurück)
- tatsächlich überprüft, ob eine Formel vorhanden ist
(sollte also z.B. ein Eingabefehler vorkommen, "=" vergessen, oder keine Eingabe gemacht sein, kommt auch wieder 0 zurück)

Mit dieser 0 kann im Zweifelsfall prima und fehlerfrei weiter gerechnet werden und zahllose WENN-Klauseln werden überflüssig.

Dein Makro wie es jetzt ist, Karo, mag er immernoch nicht ... gibt mir ständig den "Objektvariablen"-Fehler bezogen auf
zelle.HasFormula zurück. Mit F3Ks Variante, kommt leider ebenfalls dieser Fehler zurück.

Noch irgendwelche Ideen das Ding festzunageln?

Enzol

[DPunch]

Aloha

Anscheinend ist die VBA-unterstützung doch schon weit genug, hier funktioniert auch:

Bei mir (Version 3.2.1, Win XP) übergibt das auch nur das Ergebnis der Formel (bzw wirft eine Fehlermeldung aus).

Noch irgendwelche Ideen das Ding festzunageln?

Wie Karolus schon vorgeschlagen hatte, Du kannst ja direkt die Formel übergeben und diese verarbeiten

Code: Alles auswählen

=ANZAHLSUMMANDEN(FORMEL(A1))

Code: Alles auswählen

Function anzahlSummanden(Formel as String) as Integer
		zaehler = 0
		If Instr(Formel,"=") > 0 Then
			aTmp = Split(Formel,"+")
			zaehler = UBound(aTmp) + 1
		End If
		anzahlSummanden = zaehler
End Function

Dadurch hättest Du weiterhin die störenden Fehlermeldungen (#NV etc), um das zu umgehen, müsste man schon einen etwas umständlicheren Weg gehen, ala

Code: Alles auswählen

=ANZAHLSUMMANDEN(ZELLE("Adresse";A2))

Code: Alles auswählen

Function anzahlSummanden(Zelle as String) as Integer
		zaehler = 0
		oDoc = thisComponent
		If Instr(Zelle,".") > 0 Then
			aTmp = Split(Zelle,".")
			sSheetname = Replace(aTmp(0),"$","")
			sCellName = aTmp(1)
			oSheet = oDoc.Sheets.getByName(sSheetname)
		Else
			sCellName = Zelle
			oSheet = oDoc.CurrentController.ActiveSheet
		End If
		oCell = oSheet.getCellRangeByName(sCellName)
		sFormula = oCell.Formula
		If Instr(sFormula,"+") > 0 Then
			aTmp = Split(sFormula,"+")
			zaehler = UBound(aTmp) + 1
		End If
		anzahlSummanden = zaehler
End Function

[Enzol]

Danke Dir für die Hilfe DPunch.

Nur mal eine Frage zum Grundverständnis (da ich mit Basic offensichtlich Null
zurecht komme und das Forum noch nicht kannte):

Postet ihr grundlegende Ideen, die der kundige user dann noch eigenständig
vervollständigen sollte? (also z.B. diverse Dim-Zeilen einfügen o.ä.)
Oder sind die Vorschläge schon so konzipiert, dass sie an sich bereits
funktionieren sollten?

Jedenfalls:

1. (aus der Kurzlösung mit zu erwartender Fehlerrückgabe)

Code: Alles auswählen

If Instr(Formel,"=") > 0 Then

2. (aus der Langlösung)

Code: Alles auswählen

If Instr(Zelle,".") > 0 Then

Für beide Zeilen wirft er beim Probedurchlauf "Basic-Laufzeitfehler: Argument ist nicht optional" aus.
Habe beide Varianten trotzdem einfach mal bei aktivierten Makros getestet, wobei die erste einwandfrei
funktioniert (aber eben wie angekündigt N/V zurückgibt sobald keine Formel in der betreffenden Zelle ist).
Die zweite Variante, bringt (wie bei mir alles was in openoffice mit den Makros nicht läuft) das Programm
zum Komplett-lockdown -> task-manager-Holzhammer, um da wieder rauszukommen.

Code: Alles auswählen

=ANZAHLSUMMANDEN(ZELLE("Adresse";A2))

Angenommen ich möchte die Summanden in Zelle C5 zählen ... dann nehme ich an
wird "A2" durch "C5" ersetzt. Was hat es jedoch mit "Adresse" auf sich? ($C$5 ??)


Enzol

[DPunch]

Aloha

Postet ihr grundlegende Ideen, die der kundige user dann noch eigenständig
vervollständigen sollte? (also z.B. diverse Dim-Zeilen einfügen o.ä.)
Oder sind die Vorschläge schon so konzipiert, dass sie an sich bereits
funktionieren sollten?

Ich zumindest poste in der Regel lauffähigen Code, den der Hilfesuchende nur noch in Kleinigkeiten an seine Bedürfnisse anpassen muss.
Wenn es die Umstände zulassen, mache ich meistens auch noch einen kurzen Test, ob der Code fehlerfrei funktioniert, dabei lassen sich natürlich nicht immer alle theoretisch möglichen Fehler ausschliessen.

Für beide Zeilen wirft er beim Probedurchlauf "Basic-Laufzeitfehler: Argument ist nicht optional" aus.
Habe beide Varianten trotzdem einfach mal bei aktivierten Makros getestet, wobei die erste einwandfrei
funktioniert

Ich kann Dir nicht ganz folgen - gibt es nun eine Fehlermeldung bei der ersten Version oder läuft sie einwandfrei?
Die Fehlermeldung an sich kann ich mir nur dadurch erklären, dass Du möglicherweise beide Funktionen in ein Modul kopiert hast, und lediglich eine davon umbenannt hast (z.B. in anzahlSummanden2 o.Ä.).
Was mir aber tatsächlich als Fehler aufgefallen ist, ist, dass beim Laden des Dokuments der Code nicht funktioniert, was allerdings leicht erklärbar ist (die Details würden Dir wohl kaum weiterhelfen).

Angenommen ich möchte die Summanden in Zelle C5 zählen ... dann nehme ich an
wird "A2" durch "C5" ersetzt. Was hat es jedoch mit "Adresse" auf sich? ($C$5 ??)

F1 drücken für die OpenOffice-Hilfe -> Funktionen nach Kategorie -> Information
Eine solche Bezugnahme ist notwendig, damit die Funktion auch auf Änderungen in der entsprechenden Zelle reagiert. Zudem ist für einen fehlerhaften Betrieb auch notwendig, der Funktion mitzuteilen, auf welchem Tabellenblatt sich die referenzierte Zelle befindet
Und ja, Du musst lediglich den Bezug auf die gewünschte Zelle ändern, bzw in der wasserdichten Version beide Verweise

Code: Alles auswählen

=ANZAHLSUMMANDEN(ZELLE("Adresse";A6);ZELLE("Sheet";A6))

Schau Dir einfach mal die angehängte Beispieldatei an, die läuft bei mir soweit fehlerfrei und gibt auch die richtigen Ergebnisse aus.

Nachtrag: Ich bin leider überhaupt nicht vertraut mit Calc-Funktionen, möglicherweise gibt es auch eine einfachere Funktion, um Tabellenblatt und Zellname auszulesen.

[Karolus]

Hallo

Am Montag schrieb ich zuerst:

zu 2. [ siehe Edit ]
Funktioniert in Calc so nicht, weil aus einem Aufruf =ANZAHLSUMMANDEN(A1) kein vollständiges Zellobjekt übergeben wird, sondern nur der Zellinhalt als Text/Wert ohne Zugriff auf die zugrundeliegende Formel.

und berichtigte das später:

Edit zu 2.
Anscheinend ist die VBA-unterstützung doch schon weit genug, hier funktioniert auch:

Code: Alles auswählen

Option VBASupport 1
    Function anzahlSummanden(zelle As Range)
            Dim zaehler As Integer
            zaehler = 0
            If zelle.HasFormula  Then
               formel = zelle.Formula()           
               zaehler = ubound( split( formel, "+")) +1
            End If
            anzahlSummanden = zaehler
    End Function

Das hat zu diesem Zeitpunkt auch funktioniert, mit dem Aufruf aus Calc: =anzahlsummanden(A1)
( ich konnte auch mit mri( zelle ) im Code das Zellobjekt untersuchen )

Einen Tag später hab ich das nochmal getestet, und bekomme in der Zelle nur noch den Fehlercode #NULL! und in der Statusleiste: "Interner Syntaxfehler"
Diverse Versuche den Code doch nochmal zur Mitarbeit zu überreden scheitern bis jetzt.

( Testumgebung LO 3.3.2 64bit auf Suse 11.4 )

Gruß Karo

[Enzol]

Ok DPunch, das ist geradezu glorreiche Arbeit!

Vielen Dank für die Anschauungstabelle:
Hier funktioniert bei mir auch alles wie es sollte und ich werde das Ganze heute Abend mal in mein Projekt integrieren.
Ein entscheidender Punkt bei Makro-Version1, ist dass es auch kein N/V auswirft sobald überhaupt keine
Formel in der entsprechenden Zelle steht, sondern eben "0".
Wenn es erkennen würde, dass in einer Zelle keine eigentliche Formel samt "=" steht, sondern eben nur eine Zeichenkette,
wäre das noch das I-Tüpfelchen, aber ich gehe mal davon aus, dass die user das schon hinkriegen "2+2+2" von "6" zu unterscheiden.

Vielen Dank auch an Karolus für die Hilfe, den Trick mit dem Formel direkt holen werde ich mir auf jeden Fall
für zukünftige Anwendungen merken.

Enzol
(Komme wieder falls doch noch etwas schief gehen sollte. Das will ich nun aber mal stark nicht annehmen.)


-----

Aloha
Ich kann Dir nicht ganz folgen - gibt es nun eine Fehlermeldung bei der ersten Version oder läuft sie einwandfrei?
Die Fehlermeldung an sich kann ich mir nur dadurch erklären, dass Du möglicherweise beide Funktionen in ein Modul kopiert hast, und lediglich eine davon umbenannt hast (z.B. in anzahlSummanden2 o.Ä.).
Was mir aber tatsächlich als Fehler aufgefallen ist, ist, dass beim Laden des Dokuments der Code nicht funktioniert, was allerdings leicht erklärbar ist (die Details würden Dir wohl kaum weiterhelfen).

Abstruserweise beides:

Da wie gesagt ein fehlerhaftes Makro bei mir OO sofort in einer Art kann-Makro-nicht-ausführen-frage-user-ob-ok->"OK"->kann-Makro-nicht-ausführen-frage ... - Schleife verkantet, die sich nur durch minutenlanges Warten über TaskManager->sofort beenden
unterbrechen lässt, bin ich dazu übergegangen die Makros zunächst im Basic-Editor Probelaufen zu lassen.
Hier wurde eben in beiden Fällen (und übrigens auch in den aktuellen Versionen der Anschauungstabelle) ein
"Laufzeitfehler: Argument ist nicht optional" angegeben.
In der Anschauungstabelle (und der früheren Version1) führt das nicht sofort zur besagten Fehler-Schleife,
wohl aber in der früheren Version2. *Achselzuck"

Angenommen ich möchte die Summanden in Zelle C5 zählen ... dann nehme ich an
wird "A2" durch "C5" ersetzt. Was hat es jedoch mit "Adresse" auf sich? ($C$5 ??)

F1 drücken für die OpenOffice-Hilfe -> Funktionen nach Kategorie -> Information
Eine solche Bezugnahme ist notwendig, damit die Funktion auch auf Änderungen in der entsprechenden Zelle reagiert. Zudem ist für einen fehlerhaften Betrieb auch notwendig, der Funktion mitzuteilen, auf welchem Tabellenblatt sich die referenzierte Zelle befindet
Und ja, Du musst lediglich den Bezug auf die gewünschte Zelle ändern, bzw in der wasserdichten Version beide Verweise

Code: Alles auswählen

=ANZAHLSUMMANDEN(ZELLE("Adresse";A6);ZELLE("Sheet";A6))

Verstehe. Ich schätze die wasserundichte Version funktionierte in der Tat nicht, da meine Tabelle 3 sheets hat ...
da will er's schon genau wissen?

Hallo
Das hat zu diesem Zeitpunkt auch funktioniert, mit dem Aufruf aus Calc: =anzahlsummanden(A1)
( ich konnte auch mit mri( zelle ) im Code das Zellobjekt untersuchen )

Einen Tag später hab ich das nochmal getestet, und bekomme in der Zelle nur noch den Fehlercode #NULL! und in der Statusleiste: "Interner Syntaxfehler"
Diverse Versuche den Code doch nochmal zur Mitarbeit zu überreden scheitern bis jetzt.

( Testumgebung LO 3.3.2 64bit auf Suse 11.4 )

Gruß Karo

Typischer Fall von computerlicher Willkür. Ich muss aber zugeben, dass ich recht froh bin, dass es nun
bei Dir auch nicht mehr geht. Ansonsten müsste man sich ja sogleich zehn weitere Fragen stellen ...

[DPunch]

Aloha

Wenn es erkennen würde, dass in einer Zelle keine eigentliche Formel samt "=" steht, sondern eben nur eine Zeichenkette,
wäre das noch das I-Tüpfelchen

Dafür musst Du nur diesen Abschnitt

Code: Alles auswählen

		If Instr(sFormula,"+") > 0 Then
			aTmp = Split(oCell.Formula,"+")
			anzahlSummanden = UBound(aTmp) + 1
		End If

durch diesen ersetzen

Code: Alles auswählen

		If Instr(sFormula,"=") > 0 Then
			If Instr(sFormula,"+") > 0 Then
				aTmp = Split(oCell.Formula,"+")
				anzahlSummanden = UBound(aTmp) + 1
			End If
		End If

bin ich dazu übergegangen die Makros zunächst im Basic-Editor Probelaufen zu lassen.

Nun, eine Funktion, die Parameter erwaret, kann man nicht ohne weiteres (z.B. über den "Play"-Button) im Editor durchlaufen lassen - die Fehlermeldung wird besagen, dass das Argument nicht optional ist