Microsoft Excel

Herbers Excel/VBA-Archiv

VBA: Engl.Funktionsnamen in Bedingte Formatierung


Betrifft: VBA: Engl.Funktionsnamen in Bedingte Formatierung
von: NoNet
Geschrieben am: 09.11.2010 12:58:23

Hallo VBA-Spezialisten,

ich habe eine kleine Frage zur Verwendung der "Bedingten Formatierung" in VBA.
Zur Kennzeichnung redundanter Daten in einer Spalte möchte ich diese per Bedingte Formatierung farblich hervorheben.
Manuell würde ich dazu den Bereich markieren (z.B. A2:A10) und folgende Funktion als Bedingung eingeben : =ZÄHLENWENN($A$2:$A$10;$A2)>1

Das funktioniert grundsätzlich auch unter VBA, der Code könnte entsprechend aussehen :

Sub BedingteFormatierungMitFunktion()
    With Range("A2:A10")
        .Range("A1").Select 'erste Zelle des Bereiches markieren =>A2
        .FormatConditions.Delete 'Zuerst alle Bedingungen löschen
        .FormatConditions.Add Type:=xlExpression, _
            Formula1:="=ZÄHLENWENN($A$2:$A$10;$A2)>1"
        .FormatConditions(1).Interior.ColorIndex = 40
    End With
End Sub
Was mich nun stört, ist, dass die Funktion hier im Code hardcodiert mit deutschem Funktionsnamen ZÄHLENWENN enthalten ist. Ich vermute, dass der Code in nichtdeutschen (z.B. englischen, franz., spanischen) Excel-Versionen nicht funktionieren wird.

Versuchsweise habe ich den Code in die englische Schreibweise übersetzt :
(=COUNTIF($A$2:$A$10,$A2)>1 bzw. anstelle , auch ;), doch das akzeptiert die deutsche EXCEL-Version nicht.

Als "Workaround" fällt mir nur ein, die englische Version per .Formula temporär in eine Zelle (B2) zu schreiben (dort wird sie im Excel-Tabellenblatt dann in der regionalisierten Sprachversion - also z.B. in deutsch - übersetzt und angezeigt) und mit .FormulaLocal im Code der Bedingten Formatierung wieder auszulesen :
Sub BedingteFormatierungMitRegionalisierterFunktion()
    [B2].Formula = "=COUNTIF($A$2:$A$10,$A2)>1" 'englische Version
    
    With Range("A2:A10")
        .Range("A1").Select 'erste Zelle des Bereiches markieren =>A2
        .FormatConditions.Delete 'Zuerst alle Bedingungen löschen
        .FormatConditions.Add Type:=xlExpression, _
            Formula1:=[B2].FormulaLocal 'Hier die regionalisierte Version auslesen
        .FormatConditions(1).Interior.ColorIndex = 40
    End With
    
    [B2].ClearContents 'Inhalt der temp. Zelle B2 wieder löschen
End Sub
Das funktioniert zwar einwandfrei, erscheint mir jedoch ziemlich unprofessionell !

Kennt jemand eine elegantere Möglichkeit, eine Funktion sprachenunabhängig (also : englisch) in der Bedingten Formatierung per VBA zu verwenden ?

Vielen Dank für eure Unterstützung,

Gruß NoNet

  

Betrifft: In anderen Ländern ...
von: Holger
Geschrieben am: 09.11.2010 13:36:25

Hi Nonet,

zu dem eigentlich Problem kann ich leider nichts sagen, aber vielleicht hilft dir
folgende Erfahrung weiter. Wir haben eine MasterDatei erstellt mit diversen Formeln
z.B Summe, Max, Mittel et cetera. Diese haben wir zur Anpassung an verschiedene
Länder verteilt und dort gingen die Formeln soweit ich weiß ohne Anpassung.

Vielleicht wird das intern immer gleich gespeichert und der Formelname ist nur eine Art Variable,
je nach Systemsprache, die automatisch übersetzt wird!?

Gruß
Holger


  

Betrifft: Das ist bei Funktionen im TABELLENBLATT so..
von: NoNet
Geschrieben am: 09.11.2010 14:39:12

Hallo Holger,

das Verhalten bei "normalen" STANDARD-Funktionen ist bekannt. In Tabellenblättern werden die STANDARD-Funktionen immer in der regionalisierten Sprachversion dargestellt, da sie intern nur als Funktionsnummer (bzw. -Index) gespeichert werden.
Damit wird erreicht, dass die Funktion =SUMME(...) in einer englischen Excel-Version mit =SUM(...) dargestellt wird und in einer französischen Version mit =SOMME(...) etc.

Bei Funktionen, die aus Makros/AddIns stammen ist das nicht der Fall, dazu muss die Funktion explizit im Code deklariert sein (z.B. als UDF, also FUNCTION ....).

Im vorliegenden Fall ist das aber nicht einmal eine UDF, sondern ein STRING - und dieser wird garantiert in keiner Sprachversion "umgewandelt" bzw. "übersetzt" !
Konkret habe ich das mit der (originalen) englischen Excel2003-Version getestet !

Danke dennoch für Deinen Hilfeversuch ;-))

Gruß, NoNet


  

Betrifft: Das müsste eigentl gehen,...
von: Luc:-?
Geschrieben am: 09.11.2010 15:51:40

…NoNet,
mir ist momentan nur kein Ansatz ggwärtig. Zumindest kann man ja die Fmln auch in Originalsprache ausgeben, wenn man weiß wie… Gelegentl passierte das bei mir spontan und unbeabsichtigt bei Anwendung fmlauslesender udFktt…
Ein Fremdansatz (also nicht von mir, sondern wohl von Bernd [„cu“]) war es, das Original-Fml-Auslesen über Namenserstellung zu versuchen, was aber in Fktt Probleme bereitet und deshalb 'ne Sackgasse ist. Andersherum, so wie du es brauchst, wäre es ggf 'ne Möglichkeit…
Ich sehe aber noch mal nach, ob sich aus meinen Lesemethoden auch eine direkte Schreiblösung in Originalsprache ergibt. Melde mich dann noch mal…
Gruß Luc :-?


  

Betrifft: Da du so etwas ja mit einer Subroutine...
von: Luc:-?
Geschrieben am: 09.11.2010 18:03:36

…machen musst, NoNet,
ist die Lösung mit temporärer Namensvergabe gangbar und fktt auch. Den Namen kannst du entweder dauerhaft erzeugen (und ggf verbergen) oder immer wieder löschen und neu anlegen — ganz nach Belieben…
Gruß Luc :-?

PS: Ich hatte mich bisher nur mit dem Ggteil beschäftigt. Bei einer udF-Lösung wäre da die Namensvergabe nicht vorteilhaft (Instabilität!), aber es geht dabei auch anders… ;-)


  

Betrifft: AW: Das müsste eigentl gehen,...
von: bst
Geschrieben am: 10.11.2010 08:50:08

Morgen,

um eine Formel vom englischen ins deutsche zu übersetzen geht hier in xl2003 auch so etwas.

cu, Bernd

P.S. cu ist eine Abkürzung und steht für See You ;-)
--

Option Explicit

Sub Translate()
    Names.Add "myFormula", RefersTo:="=COUNTIF($A$2:$A$10,$A2)>1"
    Debug.Print Names("myFormula").RefersToLocal
    Names("myFormula").Delete
End Sub




  

Betrifft: ...Das weiß ich doch, Bernd, der Zusatz...
von: Luc:-?
Geschrieben am: 10.11.2010 15:35:21

…sollte nur „Bernd“ eindeutiger machen, da du der einzige mir bekannte Bernd bist, der das verwendet. Deshalb stand es auch in An-/Ausführungszeichen. Hoffte so, diese „Belehrung“ vermeiden zu können, aber wie man sieht… ;->>
Und ziemlich genau das, was du schreibst, habe ich gemeint, oder was hast du gedacht…? Nur habe ich NoNet zugetraut, das selber hinzubekommen… ;-)
Gruß Luc :-?

PS: Hast du denn nun schon mal einen Weg für das umgekehrte Problem gefunden, nämlich das Auslesen der BedingtFml in Englisch, Bernd?


  

Betrifft: AW: Auslesen der bed. Formel
von: bst
Geschrieben am: 11.11.2010 08:23:42

Morgen Luc,

und hmm, ja, das hätte ich mir denken können...

Zum PS: Das habe ich doch schon damals realisiert?

Der umgekehrte Weg A1 (deutsch) -> A1 (englisch) geht nicht, da M.E. Names.Add da 'buggy' ist.

Sub DEtoUS_not_work()
    Names.Add "myFormula", RefersToLocal:="=ZÄHLENWENN($A$2:$A$10;$A2)>1"
    Debug.Print Names("myFormula").RefersTo
End Sub
sollte M.E. nun schon funktionieren, tut das aber nicht :-(

Allerdings funktioniert das wenn man die Formel in Z1S1 Notation vorgibt:
Sub DEtoUS_works()
    Names.Add "myFormula", RefersToLocal:="=ZÄHLENWENN(Z2S1:Z10S1;Z(1)S1)>1"
    Debug.Print Names("myFormula").RefersTo
End Sub
Die beiden R1C1-Varianten funktionieren übrigens auch:
Sub UStoDE_R1C1()
    Names.Add "myFormula", RefersToR1C1:="=COUNTIF(R2C1:R10C1,R[1]C1)>1"
    Debug.Print Names("myFormula").RefersToLocal
End Sub

Sub DEtoUS_Z1S1()
    Names.Add "myFormula", RefersToR1C1Local:="=ZÄHLENWENN(Z2S1:Z10S1;Z(1)S1)>1"
    Debug.Print Names("myFormula").RefersTo
End Sub
Da in der bedingten Formatierung die Formeln in lokaler A1-Schreibweise liegen reicht es also aus kurz auf R1C1-Schreibweise umzuschalten und dann wie oben zu konvertieren.

cu, Bernd
--
Option Explicit
Sub x()
   ' Testformel in Zelle B1
   Range("B1").FormatConditions.Delete
   Range("B1").FormatConditions.Add xlExpression, , "=ZÄHLENWENN($A$2:$A$10;$A2)>1"
   Range("B1").FormatConditions(1).Interior.ColorIndex = 3
   
   ' Diese anzeigen -> deutsch A1-Notation
   MsgBox Range("B1").FormatConditions(1).Formula1
   
   ' auf R1C1 umschalten
   Application.ReferenceStyle = xlR1C1
   
   ' Diese anzeigen -> deutsch Z1S1-Notation
   MsgBox Range("B1").FormatConditions(1).Formula1
   
   ' Diese ins englische A1 übersetzen
   Names.Add "myFormula", RefersToR1C1Local:=Range("B1").FormatConditions(1).Formula1
   MsgBox Names("myFormula").RefersTo
   
   ' zurück in A1
   Application.ReferenceStyle = xlA1
   
   ' aufräumen
   Names("myFormula").Delete
End Sub



  

Betrifft: Ja, gut, aber das kam für mich nicht infrage,...
von: Luc:-?
Geschrieben am: 11.11.2010 17:52:26

…Bernd,
weil ich eine udFkt schreiben wollte. Denn darauf aufbauende Lösungen kann man gut im TabBlatt gebrauchen, wie man hier und anderswo fast täglich lesen kann…
Die Lösung ist eigentl ganz einfach, aber bis ich darauf gekommen bin, musste ich so manchen Umweg abschreiten → eine kleine Fkt mit Rekursion über Selbst-Evaluate
Für die Anwendung in einer udF müssen dann natürl noch ggf vorhandene relative Adressbezüge der Fml berichtigt wdn, bei Anwendung in einer Subroutine sollte die Selektion der ausgelesenen Zelle ausreichen. Eine Namensvergabe ist also in diesem Fall nicht erforderlich!
Gruß Luc :-?


  

Betrifft: AW: Ja, gut, aber das kam für mich nicht infrage,...
von: bst
Geschrieben am: 11.11.2010 19:57:57

Abend Luc,

Die Lösung ist eigentl ganz einfach, aber bis ich darauf gekommen bin, musste ich so manchen Umweg abschreiten → eine kleine Fkt mit Rekursion über Selbst-Evaluate…
Zeigst Du mal bitte ein Stück Code her, das würde mich denn schon sehr interessieren...

cu, Bernd


  

Betrifft: Danke @Luc und @Bernd
von: NoNet
Geschrieben am: 11.11.2010 21:02:56

Hey Luc, hey Bernd,

ja, diese "Lösung" mit dem definierten Namen hatte ich auch schon in Erwägung gezogen.
Mangels echter Alternative werde ich diese wohl verwenden, auch wenn diese ebenfalls irgendwie "von hinten durch die Brust ins Auge" ist. Aber zumindest benötigt diese Methode keine "Hilfszellen" und funktioniert auch in geschützten Tabellenblättern (die Formatierung des Bereiches muss freilich erlaubt sein !).

Hier nun meine funktionierende Methode :

Sub ConditionalFormatCountIf()
    Const strCondFormula = "tmpCondFormula" 'temporary name for conditional format formula
    
    Dim strFormula As String
    
    [A2].Select
    ActiveSheet.Names.Add strCondFormula, RefersTo:="=COUNTIF($A$2:$A$20,$A2)>1"
    strFormula = ActiveSheet.Names(strCondFormula).RefersToLocal
    strFormula = Replace(Replace( _
        strFormula, ActiveSheet.Name & "!", ""), "'" & ActiveSheet.Name & "'!", "")
    
    With Range("A2:B20")
        .FormatConditions.Delete
        .FormatConditions.Add Type:=xlExpression, _
            Formula1:=strFormula 'regional version of formula
        .FormatConditions(1).Interior.ColorIndex = 40
    End With
    
    ActiveSheet.Names(strCondFormula).Delete
End Sub

Vielen Dank nochmal an Bernd ("cu" hatte wohl jeder verstanden ;-) und an Luc !

Gruß, NoNet


  

Betrifft: Ja, morgen! Gruß owT
von: Luc:-?
Geschrieben am: 11.11.2010 22:00:45

:-?


  

Betrifft: Na dann so,...
von: Luc:-?
Geschrieben am: 12.11.2010 02:21:03

…Bernd…

Rem Interne Basisfkt f.d.Ermittl v.BedFmtFmln Orig/Lokal o.Adressbericht wg Unvträglk m.AList
'   Fkt ist b.Arg3=WAHR indirekt rekursiv (über Evaluate) u.fktt deshalb nur in dieser Form!
'   Vs1.0a -LSr -cd:20100315 -lupd:20100314n
Function GetFCond(Bezug As Range, ByVal BFNr As Integer, Optional ByVal Orig As Boolean)
    If Orig Then
        GetFCond = Evaluate("GetFCond(" & Bezug.Address & "," & BFNr & ")")
    Else: GetFCond = Bezug.FormatConditions(BFNr).Formula1
    End If
End Function
Anmerkung: Adressberichtigung scheint ab xl12 nicht notwendig zu sein, vorher schon.
Endlos lang dürfen die Fmln allerdings nicht sein!
Viel Spaß, Luc :-?


  

Betrifft: Owt: Danke.
von: bst
Geschrieben am: 12.11.2010 09:30:35

cu, Bernd