Microsoft Excel

Herbers Excel/VBA-Archiv

UDF Rückgabe Betrag, soll als Zahl dargestellt w. | Herbers Excel-Forum


Betrifft: UDF Rückgabe Betrag, soll als Zahl dargestellt w. von: Peter
Geschrieben am: 14.10.2008 15:59:10

Guten Tag
Mit Hilfe einer UDF ermittle ich die Summe des absoluten Wertes von numerischen Zellen, die eine bestimmte Vorgabe erfüllen und erhalte somit einen Wert, der NULL oder einen positiven Wert beträgt. Ich habe die entsprechende Variable mit Currency dimensioniert.
Das klappt so weit gut. Allerdings wird der Betrag linksbündig ausgegeben und nicht rechtsbündig, wie die Zahlen standardmässig dargestellt werden.

Wie erhalte ich eine Rückgabe, die als Betrag erkannt wird und dann rechtsbündig dargestellt wird?

Danke für eine Antwort.

Gruss, Peter

  

Betrifft: Code??? owT von: Rudi Maintaire
Geschrieben am: 14.10.2008 16:02:52




  

Betrifft: AW: Code??? owT von: Peter
Geschrieben am: 14.10.2008 16:42:51

Hallo Rudi
Hier ist der Code.
Zusätzlich würde mich noch interessieren, was ich ändern muss, dass die ausgeblendeten Zellen ohne Tabellenname zurückgegeben werden.

Danke und Gruss, Peter

Public Function verborgene_Zahl(rngZelle As Range, Bereichsnamen As String, dummy As Date) As  _
String
'dank Beitrag von Gerd L
'gibt Zellen innerhalb eines definierten Ranges zurück, die ausgeblendet sind und Zahlen  _
enthalten
'sind keine Zellen ausgeblendet, ist die Rückgabe "keine Zellen ausgeblendet
'sind Zellen mit anderen Inhalten ausgeblendet, ist die Rückgabe "keine Werte ausgeblendet"
  Dim Wks As Worksheet, Zelle As Range, rng As Range, strVersteckte As Boolean
  Set Wks = rngZelle.Parent
    With Wks
    For Each Zelle In .Range(Bereichsnamen)
      If .Rows(Zelle.Row).Hidden = True Or .Columns(Zelle.Column).Hidden = True Then
        strVersteckte = True
        If IsNumeric(Zelle.Value) Then
          If Zelle.Value <> 0 Then
            If rng Is Nothing Then
                Set rng = Zelle
            Else
                Set rng = Union(rng, Zelle)
            End If
          End If
        End If
      End If
    Next
  End With
  verborgene_Zahl = IIf(strVersteckte = True, "keine Werte ausgeblendet", "keine Zellen  _
ausgeblendet")
  If Not rng Is Nothing Then verborgene_Zahl = Split(rng.Address(False, False, xlA1, True), "]") _
(1)
End Function




  

Betrifft: AW: Code??? owT von: Jürgen V.
Geschrieben am: 14.10.2008 17:12:31

Hallo Peter,

Du hast den Datentyp der Function als "String" festgelegt - folgerichtig erhälst Du den Wert als Text. Du kannst aber den Datentyp nicht einfach ändern auf z.B. Double ändern, weil Du auch Text ausgeben möchtest, wenn keine verborgenen Zellen vorhanden sind.

Du musst Dich also entscheiden: Möchtest Du weiterhin Klartext von der Function zurückerhalten, musst Du damit leben, dass auch das mögliche Berechnungsergebnis als Text zurückgegeben wir. Möchtest Du die Zahl als Zahl erhalten, musst Du statt der Klartexte nummerische Werte zurückgeben. Da Du Absolutwerte berechnest, wäre z.B. "-1" für "keine Werte ausgeblendet" und "-2" für "keine Zellen ausgeblendet".

Nebenbei, kannst Du das gewünschte Ergebnis nicht mit Teilergebnis() erzielen?

[cut]
Und nun, da ich Dein Beispiel durchgelesen habe und mir die Mühe gemacht habe, eine halbwegs vernünftige Antwort zu verfassen, beschleicht mich nach erneutem Lesen Deiner Eingangsfrage und des geposteten Codes der heftige Verdacht, dass das eine nicht zum anderen passt - zumindest ermittelt Deine Funktion keine Summe sondern Zelladressen!?!

Gruß, Jürgen


  

Betrifft: AW: Das hat schon Klarheit gebracht von: Peter
Geschrieben am: 14.10.2008 17:24:17

Hallo Jürgen
Danke für die schnelle Antwort. Jetzt ist mir die Angelegenheit klar. Übrigens habe ich zwei ganz ähnliche UDFs, und ich habe die falsche gepostet, da jedoch beide einen String zurück geben, hat die Antwort weitergeholfen.

Vielleicht kannst du mir auch noch die zweite Frage beantworten. Mir ist nicht klar, wie die unten aufgeführte Codezeile funktioniert. Wenn rng nicht Nothing ist erhalte ich beispielsweise
Auswertung'!H13:H14 zurück.
Könnte ich den Tabellennamen auch unterdrücken, dass ich nur H13:H14 zurückerhalte? Zudem konnte ich nicht ausfindig machen, was das "(1) am Ende der Zeile bedeutet.

Vielen Dank und Gruss, Peter

If Not rng Is Nothing Then verborgene_Zahl = Split(rng.Address(False, False, xlA1, True), "]") (1)


  

Betrifft: AW: UDF Rückgabe Betrag, soll als Zahl dargestellt w. von: Peter Feustel
Geschrieben am: 14.10.2008 16:03:03

Hallo Peter,

ich würde nicht As Currency, sondern As Double nehmen und ggf. das Währungszeichen selbst setzen.

Gruß Peter


  

Betrifft: AW: UDF Rückgabe Betrag, soll als Zahl dargestellt w. von: Peter
Geschrieben am: 14.10.2008 16:46:00

Hallo Peter
Habe ich umgestellt, hat jedoch noch nichts bewirkt.

Gruss, Peter


  

Betrifft: AW: UDF Rückgabe Betrag, soll als Zahl dargestellt w. von: Gerd L
Geschrieben am: 14.10.2008 19:33:15

Hallo Peter,

zur Erläuterung siehe die Ausführungen von Jürgen.

If Not rng Is Nothing Then verborgene_Zahl = rng.Address(False, False, xlA1, False)
liefert die lokale(n) Zelladresse(n).

P.S.: So wie Du die Funktion zeigst, haben m.W. noch Erich G. u. Rudi Maintaire daran mitgewirkt.

Gruß Gerd


  

Betrifft: AW: UDF Rückgabe Betrag, soll als Zahl dargestellt w. von: Peter
Geschrieben am: 15.10.2008 07:55:40

Hallo Gerd
Danke für die Antwort. Irgendwie klappt das mit der lokalen Adresse noch nicht. Zudem habe ist mir immer noch nicht klar, was bei der Codezeile hinten das "(0)" bedeutet. Ich habe eine kleine Beispieldatei hochgeladen.

https://www.herber.de/bbs/user/56027.xls

'''Tabellenname wird mitgeliefert
If Not rng Is Nothing Then verborgene_Zahl = Split(rng.Address(False, False, xlA1, True), "]")(1)

'' 'nur lokale Adressen (Zellen) - funktioniert noch nicht
' If Not rng Is Nothing Then verborgene_Zahl = Split(rng.Address(False, False, xlA1, False), "]") (1)

Danke für eine Antwort und freundlicher Gruss, Peter


  

Betrifft: AW: UDF Rückgabe Betrag, soll als Zahl dargestellt w. von: Jürgen V.
Geschrieben am: 15.10.2008 09:59:19

Hallo Peter,

vorneweg: wenn Du von den freiwilligen Helfern eine präzise Antwort haben möchtest, solltest Du beim Stellen der Fragen ausreichende Aufmerksamkeit walten lassen und sie präzise formulieren. So bleibt mir nur zu vermuten, dass Du eine Erklärung für das (1) und nicht für (0) haben möchtest (und ich erinnere mich an das falsch gepostete Codebeispiel - Grummel, Grummel...).

Nun zur Erläuterung: rng.Address(False, False, xlA1, True) liefert eine Bereichsadresse als Text, der letzte Paramter entscheidet, ob der Tabellenname + Blattname mitgeliefert werden soll (True) oder nicht (false). Mit Split wird dieser Text anhand eines definierten Trennzeichens in ein Datenfeld aufgeteilt, in Deinem Codebeispiel anhand des "]". Mit dem nachgestellten (n) wird das n-te Element des Datenfelds angesprochen. Da es 0-basiert ist, erhält man mit (1) das zweite Element, in Deinem Codebeispiel also das, was nach dem "]" in der Zelladresse steht.

Wenn Du Dir den Aufbau der von .Address zurückgegebenen Zelladresse ansiehst, erhälst Du bei "True" als 4. Parameter etwas wie:

[Dateixy]Tabelle1!B2

Ich denke, nun kannst Du nachvollziehen, warum in Deiner ersten Codezeile der Tabellennamen mit ausgegeben wird (nämlich alles nach dem "]"). Und Du kannst nachvollziehen, warum Deine zweite Codevariante auf einen Fehler läuft: rng.Address(False, False, xlA1, False) liefert nur die Zelladresse, damit keinen Dateinamen, damit kein "]", das Aufsplitten erzeugt nur ein statt zwei Element(e) und der Zugriff auf das zweite Element mit (1) schlägt folglich fehl.

Wenn Du also nur die Zelladresse haben möchtest, ohne Datei und Tabellennamen, dann geht das z.B. so:

If Not rng Is Nothing Then verborgene_Zahl = rng.Address(False, False, xlA1, False)

Gruß, Jürgen


  

Betrifft: AW: Danke von: Peter
Geschrieben am: 15.10.2008 12:14:13

Hallo Jürgen
Vielen Dank für die ausführliche Beschreibung - ich versuche schon die notwendige Aufmerksamkeit walten zu lassen, jedoch leider nicht immer mit durchschlagendem Erfolg.
Aus der Beschreibung habe ich abgeleitet, dass wenn ich von [Dateixy]Tabelle1!B2 nur die Zelle oder die Zellen zurück haben will, ich anstelle von "]" einfach mit "!" zum gewünschten Ergebnis komme.

Gruss, Peter

If Not rng Is Nothing Then verborgene_Zahl = Split(rng.Address(False, False, xlA1, True), "!")(1)