(gelöst) Benutzerdefinierte Funktion für eine Formel

[Freischreiber]

Hallo,

zur Ermittlung eines Preises abhängig von einem Eingabewert verwende ich diese Formel:

Code: Alles auswählen

Preis = INDEX(preistabelle.B:B;VERGLEICH(w-0,01;preistabelle.A:A;1)+1)

Für einen gegebenen Wert wird in einer Preistabelle, dort aus Spalte B, ein Preis ermittelt. Die Preise sind gestaffelt, also für Werte bis 100 (Spalte A) ein Preis von 10 (Spalte B) usw.

In der Tabelle funktioniert diese Formel sehr gut.

Diese Formel würde ich jetzt gerne als Funktion definieren über den Makroeditor, da ich sie mehrfach brauche. Das sieht bisher so aus:

Code: Alles auswählen

Function Preis(w)
Preis = INDEX(preistabelle.B:B;VERGLEICH(w-0,01;preistabelle.A:A;1)+1)
End Function

Leider hakt es bei den Semikolons:
"BASIC-Syntaxfehler. Unerwartetes Symbol: ;. "

Kommas statt Semikolons mag Calc aber auch nicht. Was mache ich falsch bzw. wie geht es richtig?

Danke und viele Grüße - und einen Guten Rutsch!

Freischreiber

[Lupo]

Mache Dich mit der BASIC-Syntax für das Verwenden von Tabellenformeln vertraut. Einfach so in den Code hineinschreiben ist nicht.

[craig]

Hallo Freischreiber,

hiermal ein Beispiel:

Code: Alles auswählen

REM  *****  BASIC  *****

REM https://wiki.openoffice.org/wiki/DE/Makro_Basic_Tutorial#Variablen
REM 	Für myIndex = Datentyp DOUBLE, weil → 	w-0.01
Function myIndex(w) as Double
Dim oDoc as Object		' Objektvariable
Dim oSheet as Object	' Objektvariable
Dim oColA  as Object	' Objektvariable
Dim oColB as Object		' Objektvariable

	' Referenz: das aktuelle Dokument
	oDoc=Thiscomponent
		' Referenz: die Tabelle "Preistabelle"
		oSheet=oDoc.Sheets().GetByName("Preistabelle")

			' Referenz: des Zellbereich Spalte A / begrenzt auf 1000 Zellen
			oColA =oSheet.getCellRangeByName("A1:A1000")	 '→ ggf. erweitern/ ändern

			' Referenz: des Zellbereich Spalte B / begrenzt auf 1000 Zellen
			oColB =oSheet.getCellRangeByName("B1:B1000")	 '→ ggf. erweitern/ ändern

' → → →		' Referenz: Zelle C1 = EingabeZelle → ggf. ändern
			oCell=oSheet.getCellRangeByName("C1")

REM ----------------------------------------------------------------------
REM 	deutsch		englisch
REM	Index		Index
REM 	Vergleich	Offset

REM 	WENN w = Zelladresse, DANN
REM 	ggf. 	statt 	w-0.01 	→ 		((ZELLE("ADRESSE";w)-0.01)

'Preis = INDEX(preistabelle.B:B;VERGLEICH(w-0,01;preistabelle.A:A;1)+1)
	oCell.Formulalocal="=Index(oColB;Vergleich(w-0.01;oColA;1)+1)"
		myIndex=oCell.Value

End Function

Beachte die Kommentare im Code!

Ob es funktioniert weiß ich nicht, da mir eine Musterdatei von Dir fehlt.

Der Aufruf der UDF erfolgt mit:
=myindex(C1)
wobei C1 = w ist.

Manchmal muss die Berechnung neu getriggert werden, dazu Shortcut [F9].

[Freischreiber]

Hallo Craig,

anliegend eine Musterdatei mit deinem Makro drin.

Indexfunktion.ods

(10.85 KiB) 201-mal heruntergeladen

Es wird immer 0 ausgegeben.

Den Aufbau mit dem C1 verstehe ich auch nicht ganz. Da wird in die Preistabelle die Formel reingeschrieben. Aber aufrufen will ich die Funktion ja für beliebige Eingabewerte auf der Eingabetabelle, und nicht mit myIndex(C1).

Viele Grüße
Freischreiber

[F3K Total]

Moin,
anbei ein Vorschlag zur Lösung ohne "Zwischenzelle" unter Verwendung der Calc-Funktionen
MATCH() entspricht VERGLEICH()
und
INDEX() deutsch und englisch gleich.

Code: Alles auswählen

Function MyIndex(w)
    Dim FunctionAccess as Object
    ServiceFunctionAccess = createUnoService("com.sun.star.sheet.FunctionAccess")
	oSheet = Thiscomponent.Sheets.GetByName("Preistabelle")
	oColA = oSheet.getcellrangebyName("A2:A1000")
	oColB = oSheet.getcellrangebyName("B2:B1000")
    nValue = ServiceFunctionAccess.callFunction( "MATCH",Array(w-0.01,oColA,1))
    MyIndex = ServiceFunctionAccess.callFunction( "INDEX",Array(oColB,nValue+1))
End Function

EDIT:
Wenn man den Funktionsaufruf wie in der Formel, in einer Zeile haben möchte, substituiert man nValue entsprechend:

Code: Alles auswählen

 MyIndex = ServiceFunctionAccess.callFunction( "INDEX",Array(oColB,ServiceFunctionAccess.callFunction( "MATCH",Array(w-0.01,oColA,1))+1))

EDIT2:
Anbei eine Beispieldatei, in der ich nur benannte Bereiche verwende, um die gewünschte Funktion zu erzielen, Kein MAKRO nötig. DIe Formel läßt sich herunter oder zur Seite ziehen, je nach Adressierung.

index.png (28.75 KiB) 2671 mal betrachtet

Gruß R

[Freischreiber]

Hallo R,

mit den benannten Bereichen komme ich gar nicht zurecht. Man sieht in der Zelle nicht, worauf verwiesen wird, und wenn man unter "Benannte Bereiche verwalten" einmal aus Versehen in die Tabelle klickt, ist die Formel weg und überall steht nur noch Fehler522.

Das Makro liefert bei mir leider gar nichts, nicht einmal 0.

Viele Grüße
Freischreiber

[F3K Total]

Das Makro liefert bei mir leider gar nichts, nicht einmal 0.

Dann hast du es nicht richtig eingebaut oder verwendet, anbei deine Datei mit dem Makro.
Funktioniert bei mir sowohl unter ApacheOpenoffice als auch unter Libreoffice 6.4.7 und 7.0.4, DE und EN

Gruß R

[Freischreiber]

Hallo R,

jetzt, ja... in Module1 stand vor deiner Funktion die gleichnamige Funktion von Craig. Das scheint sich zu stören.

Vielen Dank!

Viele Grüße
Freischreiber