Mitteilung bzgl Ol-Xl/CP-Forum-Links

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

Rem Erzeugt ggf 1 unzusammenhängd Bereich (MehrfachAuswahl) ' aus Bezug lt Arg1 ohne darin uU enthaltene FehlerWerte; ' hilfreich b.Fktt, d.nur Bereiche ohne FWerte, aber auch ' unzusammhängende verarbeiten können, bspw TEILERGEBNIS; ' Datenfeld-verarbeitde Fktt kommen so idR ohne MxFmlForm ' aus; Arg1 aus nur 1 Zelle wird unbehandelt übnommen, b. ' VektorForm wird auch b.solitärer FktsVerwendg in MxFmln ' richtig Ergebnis geliefert, b.MatrixForm müssen d.Werte ' idR m.INDEX/Var2 aus d.unzushgd ErgebnisBereich gelesen ' wdn; m.Arg2=WAHR/0 wird d.Ergebn auf sichtbare Zellen ' beschränkt, wobei dies b.Ausblenden d.Zelle m.der d.Fkt ' enthaltenden Fml dort Anzeige d.StandardFWerts bewirkt; ' Achtung! Fkt benötigt b.ggf verlangter AutoAktualisierg ' d.Nicht-Berücksichtigg ausgeblendet Zellen ExternAnstoß ' in mindest 1er sie enthaltenden Fml - Arg2 dort ggf als ' 1^JETZT() o.ä. notieren -, damit sie auch unabhängig v. ' evtl and Berechngg auf [F9] bzw Edit 1er beliebig Zelle ' reagiert, was generell v.Arg2 abhängt: WAHR/0 -> par- ' tielle Volatilität zur Erzielg dss speziell Verhaltens. ' Vs1.2 -LSr.CyWorXxl -cd:20150710 -1pub:20150721herber(/11oxCp) -lupd:20150715n Function NoErrRange(Bereich As Range, Optional ByVal nurVisZ As Boolean) As Range Dim xZ As Range Application.Volatile nurVisZ If Bereich.Cells.Count = 1 Then Set NoErrRange = Bereich: Exit Function For Each xZ In Bereich If Not IsError(xZ) Then If Not NoErrRange Is Nothing Then If nurVisZ Then If Not (xZ.EntireRow.Hidden Or xZ.EntireColumn.Hidden) Then _ Set NoErrRange = Union(NoErrRange, xZ) Else: Set NoErrRange = Union(NoErrRange, xZ) End If ElseIf nurVisZ Then If Not (xZ.EntireRow.Hidden Or xZ.EntireColumn.Hidden) Then _ Set NoErrRange = xZ Else: Set NoErrRange = xZ End If End If Next xZ End Function

…würden, gäbe es noch eine Möglichkeit, die ohne Ergänzung des 2.Arguments mit einer volatilen StandardFkt und ohne [F9] bzw DoppelKlick auskommt. Diese basiert auf dem Ablauf manuellen¹ Aus-/Einblendens. Dabei müssen ja auch Zellen ausgewählt wdn, erst die auszublendenden und dann idR irgendeine andere. In beiden Fällen wird das Worksheet_SelectionChange-Ereignis² ausgelöst, das hierfür genutzt wdn kann. Da man dabei nicht unbedingt ganze Zeilen oder Spalten markieren muss, ist das Tool etwas länger geworden als sonst erforderlich gewesen wäre. Wer nicht gewohnt ist, ganze Zeilen bzw Spalten für diesen Zweck zu markieren, sollte eine benannte Konstante des jeweiligen Blattes³ definieren, die die Zahl der mindestens auszuwählenden Zellen angibt, bei der die Aktion ausgelöst wird, damit das nicht bei jedem Klick aufs Blatt geschieht. Anderenfalls fktioniert das nur bei Auswahl ganzer Zeilen bzw Spalten!
¹ Wer eine VBA-Prozedur dafür benutzt, sollte in diese an geeigneter Stelle ein .Calculate o.Ä. einbauen!
² Das ist auch mit dem Workbook_SheetSelectionChange der Mappe möglich, was man dann für alle oder nur ausgewählte Blätter einrichten kann; das muss sich derjenige aber selber so einrichten (für die ganze Application mit AuswahlMöglichkeit nur für bestimmte Mappen/Blätter ist auch möglich)!
³ Diese kann für alle infrage kommenden Blätter gleich heißen; wichtig ist aber, dass sie für das jeweilige Blatt angelegt wird! Im Falle der unter ² genannten anderen Möglichkeiten, kann sie auch für die jeweilige Mappe angelegt wdn.
Die nachfolgenden Pgmm gehören in das Dokument-Klassenmodul des jeweiligen Blattes. Dabei wurde die EreignisProzedur bewusst kurz gehalten, damit darin ggf auch anderes untergebracht wdn kann. Die AufrufBedingungen für die eigentl Prozedur können nach Bedarf angepasst wdn:

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Const adRelBer$ = ""    'Anm: Hier ggf GeltungsbereichsAdresse eintragen!
Dim relBer As Range
If adRelBer = "" Then Set relBer = Me.UsedRange Else Set relBer = Me.Range(adRelBer)
If Not Intersect(Target, relBer) Is Nothing Then Call HiddenRange(Target)
Set relBer = Nothing
End Sub
Rem Unterstützung d.UDF NoErrRange b.Aus/Einblenden v.Zeilen/Spalten
'   Vs1.1 -LSr:CyWorXxl -cd:20150721 -1pub:20150722herber -lupd:20150721n
Private Sub HiddenRange(ByVal Ziel As Range)
Const naSelMod$ = "minBlendZAnz"    'Anm: Anderen Namen hier übernehmen!
Static isReact(1) As Boolean, anzVorZ(1) As Long, adVorZiel$, selMod As Variant
On Error GoTo fx ': selMod = Empty  'Anm: Aktivieren ggf nur zum Testen, sonst 
If IsEmpty(selMod) Then                 ' wird nur b.1.Aufruf/Sitzg ermittelt!
On Error Resume Next
If IsError(Me.Names(naSelMod)) Then
On Error GoTo fx: selMod = 0
Else: On Error GoTo fx: selMod = Mid(Me.Names(naSelMod).Value, 2)
If IsNumeric(selMod) Then selMod = CLng(selMod) Else selMod = 0
End If
End If
With Ziel
If Not (isReact(0) Or isReact(1)) Then
isReact(0) = .Address = .EntireColumn.Address Or _
.Address = .EntireRow.Address
If CBool(selMod) And Not isReact(0) Then
isReact(1) = .Cells.Count >= selMod
If isReact(1) Then
Application.EnableEvents = False
adVorZiel = .Address
anzVorZ(0) = .SpecialCells(xlCellTypeVisible).Count
Application.EnableEvents = True
End If
End If
ElseIf isReact(1) Then
On Error Resume Next
Application.EnableEvents = False
anzVorZ(1) = Me.Range(adVorZiel).SpecialCells(xlCellTypeVisible).Count
Application.EnableEvents = True
On Error GoTo fx: isReact(1) = anzVorZ(0)  anzVorZ(1)
If isReact(1) Then Me.Calculate
GoTo ex
Else: isReact(0) = False: Me.Calculate
End If
End With
If CBool(Err.Number) Then
fx:     MsgBox Err.Description, vbCritical, "Fehler " & CStr(Err.Number)
Aplication.EnableEvents = True
ex:     isReact(0) = False: isReact(1) = False: adVorZiel = ""
anzVorZ(0) = 0: anzVorZ(1) = 0
End If
End Sub

Da hier mit Aus-/Einschalten der EreignisReaktion gearbeitet wird, ist es besser, wenn man sich sicherheitshalber noch eine kleine Routine zum Wiedereinschalten derselben zulegt → im Folgenden als Umschalten des aktuellen Zustands (FlipFlop) angelegt:

Sub EventFF()
With Application: .EnableEvents = Not .EnableEvents: End With
End Sub

Viel Erfolg! Luc :-?

Wem das alles zu umständlich erscheinen mag, der kann eine Neuberechnung aller volatilen Fktt, so auch der Fmln mit UDF NoErrRange, auch mit einer Lösung erzwingen, die ursprünglich für einen anderen Zweck gedacht war (und kürzlich hier von mir vorgestellt wurde).
Nachteil: Die Neuberechnung wird bei jedem Wechsel der Auswahl veranlasst.
Vorteil: Unter dem angegebenen bzw beliebig geänderten Namen wird stets auf die gerade ausgewählte Zelle verwiesen. Daran kann man diverse „Spielchen“ mit der BedingtFormatierung oder auch Anderes festmachen, wie bspw Adress- oder WertAusgabe. Dabei kann ein ZirkelBezugsfehler zB mit folgenden Fmln vermieden wdn:
ZellAdressAngabe: =ADRESSE(ZEILE(AktZelle);SPALTE(AktZelle);4)
ZellWertAusgabe: =INDEX(AktZelle;1)
Soll das nur für ein Blatt gelten, muss der Name auch für dieses Blatt definiert wdn und im DokumentKlassenmodul des Blattes wird folgender PgmCode notiert:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Const naAktZAdr$ = "AktZelle" '→ Name -auch hier- beliebig wählbar!
    Me.Names(naAktZAdr).Value = "=" & Target.AddressLocal
End Sub

Soll es aber für alle (oder mehrere bestimmte) Blätter gelten, muss im DokumentKlassenmodul der Mappe die EreignisProzedur Workbook_SheetSelectionChange (mit den StandardParametern ByVal Sh As Object, ByVal Target As Range) angelegt wdn. Der Name wird dann für die Mappe definiert. Über den 1.Parameter kann dann ggf zusätzlich eine BlattAbfrage vorgenommen wdn.
Fmln, mit der durch ≠0-Angabe ihres 2.Arguments volatilen Version der UDF NoErrRange, wdn nach dem Aus-/Einblenden idR (außer bei Zeilen in Kombi mit TEILERGEBNIS) ebenfalls nicht sofort neu berechnet, aber nach erneutem Wechsel der ZellAuswahl (auch ohne [F9]!).
Diese Alternative wäre zu bevorzugen, falls der ständig aktualisierte Verweis auf die jeweilige ZellAuswahl (nur die 1.Zelle - nicht die FolgeZellen - wird berücksichtigt!) tatsächlich benötigt wird. Ansonsten wäre die 1.AutomatisierungsVariante wahrscheinlich günstiger, da mit geringerer Berechnungsfrequenz.
Wer das zusätzliche UDF-Feature - Nicht-Berücksichtigung ausgeblendeter Spalten (bzw Zeilen) - nicht nutzen will, benötigt keine der Automatisierungsvarianten, anderenfalls ist evtl aber auch der ursprüngl Vorschlag ([F9] bzw Klick in beliebige Zelle in Verbindung mit einer 1malig dem 2.Argument hinzugefügten volatilen StandardFkt) ausreichend.
Luc :-?

Beliebteste Forumthreads (12 Monate)

Mitteilung bzgl Ol-Xl/CP-Forum-Links

7 Beiträge zum Forumthread Beiträge zu diesem Forumthread

Beliebteste Forumthreads (12 Monate)

Beliebteste Forumthreads (12 Monate)

7
Beiträge zum Forumthread

Beiträge zu diesem Forumthread