Live-Forum - Die aktuellen Beiträge
Anzeige
Archiv - Navigation
1736to1740
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

@Interessenten [Teil1]: INDIREKT-Ersatz

@Interessenten [Teil1]: INDIREKT-Ersatz
28.01.2020 00:37:31
Luc:-?
Hallo, Leute;
ich habe mich einer 15 Jahre alten UDF erinnert, deren letzte Aktualisierung auch schon 10 Jahre zurückliegt. Diese kann die Xl-Fkt INDIREKT ersetzen.
Ein Hauptmangel von INDIREKT besteht ja in der Nichtanwendbarkeit auf geschlossene Dateien, ein anderer in ihrer Volatilität, die dann auch die Nichtanzeige von Ergebnissen im Fktsassistenten bewirkt. Den 1.Mangel weist auch mein Ersatz auf, den 2. kann man ggf aber vermeiden. Außerdem kann mein Ersatz auch Blatt­inter­valle in üblicher Schreibung verarbeiten und ausgeblendete Blätter innerhalb des Intervalls entfallen lassen.
Ich habe nun die aktuelle Version so bearbeitet, dass sie ebenfalls publiziert wdn kann, was hiermit geschieht:

Rem Ersatz f.Xl-Fkt INDIREKT: Umwandl 1es (Bezugs auf nur 1en) Bezugstext/s in 1 Bereichsbezug;
'   auch f.Mehrf'ausw unzushängd gleicher Zellbereiche mehrerer auf1andflgd ArbBl nur 1er Datei
'   (Arg1) unter Auslass/1bezieh (Arg2:fehlt|0/1) in d.ArbBlattFolge auftret vsteckter Blätter;
'   UDF kann b.Bed mittels Arg3 volatil (stete Neuberechn) gesetzt wdn (fehlt|0=FALSCH/1=WAHR).
'   Hinw: Ohne spez Berücksichtig d.SchnittmengenOperators (Intersect=LeerZ) v.Bereichsangaben!
'   Achtung! Die UDF ist in Abhängigkeit von Arg1 nur bedingt (passiv) matrixformelfähig!
'   Vs2.2b -LSr -cd:20050226 -1pub:20200127h -lupd:20200126n
Function TinRange(ByVal BezText, Optional ByVal unsichtBl, Optional ByVal volKalk As Boolean)
Const sz = ":;\[]'!"                        'mögl SonderZ in Zellbezügen
   Dim h As Integer, i As Integer, l As Integer, n As Integer, _
ma As Boolean, mz As Boolean, j As Long, k As Long, m As Long, _
bz$(2), zz As String, X, Y, z As Variant, w As Worksheet
Application.Volatile volKalk: On Error GoTo fx
If IsError(BezText) Or IsEmpty(BezText) Then TinRange = BezText: Exit Function
If IsMissing(unsichtBl) Then
unsichtBl = False
Else: unsichtBl = CBool(unsichtBl)
End If
If InStr(BezText, Right(sz, 1)) Then
bz(0) = Left(BezText, InStr(BezText, Right(sz, 1)) - 1)
End If
bz(0) = WorksheetFunction.Substitute(bz(0), Mid(sz, 6, 1), "")
zz = Mid(BezText, InStr(BezText, Right(sz, 1)) + 1)
ma = InStr(zz, Mid(sz, 2, 1))
mz = InStr(zz, Left(sz, 1))
l = 1
If ma Then
z = Split(zz, Mid(sz, 2, 1))
l = UBound(z) + 1 - LBound(z): ReDim X(l - 1)
For h = 0 To l - 1: X(h) = z(h): Next h
zz = X(0): z = Empty
End If
With ActiveWorkbook
If bz(0) = "" Then
Set TinRange = ActiveSheet.Range(zz)
If ma Then
For h = 1 To l - 1
Set TinRange = Union(TinRange, ActiveSheet.Range(X(h)))
Next h
End If
ElseIf InStr(bz(0), Left(sz, 1)) > 0 Then
bz(1) = Left(bz(0), InStr(bz(0), Left(sz, 1)) - 1)
bz(2) = Mid(bz(0), InStr(bz(0), Left(sz, 1)) + 1)
n = .Sheets(bz(2)).Index - .Sheets(bz(1)).Index
m = Range(zz).Cells.Count
If ma Or mz Then
n = (n + 1) * m * l - 1
End If
ReDim Y(n) As Variant
For Each w In .Sheets
If w.Index > .Sheets(bz(2)).Index Then Exit For
If w.Index >= .Sheets(bz(1)).Index Then
If mz Then
If ma Then
For h = 0 To l - 1
For j = 0 To m - 1
If w.Visible = xlSheetVisible Or unsichtBl Then
Set Y(i * m * l + h * l + j - k) = _
w.Range(X(h)).Cells(j + 1)
Else: k = k + 1
End If
Next j
Next h
Else
For j = 0 To m - 1
If w.Visible = xlSheetVisible Or unsichtBl Then
Set Y(i * m + j - k) = w.Range(zz).Cells(j + 1)
Else: k = k + 1
End If
Next j
End If
ElseIf w.Visible = xlSheetVisible Or unsichtBl Then
If ma Then
For h = 0 To l - 1
Set Y(i * l + h - k) = w.Range(X(h))
Next h
Else: Set Y(i - k) = w.Range(zz)
End If
Else: k = k + l
End If
i = i + 1
End If
Next w
ReDim z(n - k)
For i = 0 To n - k
z(i) = Y(i)
Next i
TinRange = z
Else: Set TinRange = .Sheets(bz(0)).Range(zz)
If ma Then
For h = 1 To l - 1
Set TinRange = Union(TinRange, .Sheets(bz(0)).Range(X(h)))
Next h
End If
End If
End With
Rem Fehlerbehandlung
fx: If CBool(Err.Number) Then TinRange = CVErr(Err.Number)
Set w = Nothing
End Function
Dazu dann mal ein einfaches Anwendungsbsp für die Wiedergabe von Daten 2er Blätter (natürlich kann das Intervall auch größer sein und die Bereichsangaben können dynamisch zusammengesetzt wdn; wobei sie auch diskontinuierlich sein dürfen → beides mit INDIREKT nicht oW möglich):
 QRSTUVWX
9
1212Einsatzbeispiele für die UDF TinRange341122  1. Tabellen aus beiden Blättern untereinander5634      1.1 nacheinander11223344Q9:R14: {=INDEX(TinRange("Tabelle2:Tabelle3!A1:B3";;1);(ZEILE(1:6)-1)*2334456                  +SPALTE(A:B))}55665566      1.2 abwechselnd© LSr.CyWorXxl 2005-2020S9:T14: {=INDEX(TinRange("Tabelle2:Tabelle3!A1:B3";;1);ZEILE(1:6)+REST(ZEILE(1:6)-1;2)*5-1+SPALTE(A:B))}121122  2. Tabellen aus beiden Blättern nebeneinander343344        Quellen sind an Zellfarbvariation erkenntlich.565566      2.1 nacheinander1.2 2005/02Q16:T18: {=INDEX(TinRange("Tabelle2:Tabelle3!A1:B3";;1);ZEILE(1:3)*2-1+GANZZAHL((SPALTE(A:D)-1)/2)*6+REST(SPALTE(A:D)-1;2))}111222        INDEX wird hier nur zur Verteilung der Daten des 333444        von TinRange gelieferten Kovektors benötigt.555666      2.2 abwechselnd2.2 2020/01Q20:T22: {=INDEX(TinRange("Tabelle2:Tabelle3!A1:B3";;1);ZEILE(1:3)*2-1+GANZZAHL((SPALTE(A:D)-1)/2)+REST(SPALTE(A:D)-1;2)*6)}
10
11
12
13
14
15
16
17
18
19
20
21
22
23
Auf die Idee, mich an diese alte UDF zu erinnern, kam ich durch folgenden Thread:
https://www.herber.de/forum/archiv/1732to1736/t1735499.htm#1735499
Das dortige Problem ließe sich damit (passend zum obigen Bsp) prinzipiell auch einfach so lösen:
=SUMME(TinRange("Tabelle2:Tabelle3!"&ADRESSE(3;ZEILE(1:1));;1))
Diskontinuierliche Bereiche zum obigen Bsp könnten so angegeben wdn:
=TinRange("Tabelle2:Tabelle3!"&ADRESSE(1;ZEILE(1:1))&";"&ADRESSE(3;ZEILE(1:1));;1)
Viel Erfolg beim Ausprobieren! Gruß, Luc :-?
„Die Intelligenzmenge ist auf diesem Planeten eine Konstante, die Bevölkerung nimmt aber zu!“ Auch deshalb informieren mit …

3
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Warum nicht Hatlan Grove's Pull Funktion?
28.01.2020 07:54:51
Sulprobil
Hallo Luc,
Einmal abgesehen vom unnötigen Verlust unserer Muttersprache in Deinen Kommentaren, warum verwendest Du nicht einfach Harlan Grove's Pull Funktion, d. h. was kann Deine Funktion besser als seine, bitte?
Links:
https://groups.google.com/forum/m/#!msg/microsoft.public.excel.worksheet.functions/l9ObQ9ku6Bk/_a2jdMD2SeIJ
https://stackoverflow.com/questions/7325335/vba-pull-function-speed-up
BTW: Ich frage lediglich aus freundlichem theoretischem Interesse. Der praktische Nutzen hierfür ist meiner unfreundlichen Ansicht nach nahe Null.
Viele Grüße,
Bernd P
Anzeige
Ich verweise auf den 1.Beitrag in deinem 2.Link, …
28.01.2020 12:22:08
Luc:-?
…Bernd;
Pull fktt wie INDIREKT, nur auch für geschlossene Dateien, aber eröffnet dafür eine neue Xl-Instanz.
Das macht meine UDF nicht, was ich auch geschrieben hatte, ermöglicht dafür aber anderes, was INDIREKT nachweislich nicht kann. Warum soll das nicht benötigt wdn? Die komplizierten Konstrukte mit INDIREKT (auch im verlinkten Thread) beweisen doch das Gegenteil!
Und immerhin ist meine UDF vom Ursprung her schon fast genauso alt wie Pull. Also bestand damals auch hier schon ein Bedarf an so etwas (damals ging's auch um das Weglassen ausgeblendeter Blätter im Intervall).
Was du mit dem Ausflug zu meinen Kommentaren und dem angeblichen Sprachverlust darin sagen willst, ist mir nicht klar, ist deine WebSite doch komplett in English. Ich habe also eher den Eindruck, dass dein „unfreundlich“ eher generell zu verstehen ist, zumal du schon einmal meine Arbeit seltsam kommentiert hattest…
Ansonsten scheinst du hier ja (neben mir und abgesehen von HWHs Vorarbeiten) der Einzige zu sein, der systematisch anspruchsvolle UDFs produziert. Ist meine Arbeit deshalb ein Problem für dich…?
Gruß, Luc :-?
Anzeige
AW: Muss man wohl systematisch vergleichen
29.01.2020 12:26:59
Sulprobil
Hallo Luc,
Danke für das Ausschreiben.
Mein "unfreundlich" richtet sich ausschließlich gegen die Funktion INDIREKT und seine Klone, weil ich glaube, dass deren Nutzung kein sinniges Design gestattet. Siehe mein Excel Don't #10 http://sulprobil.com/Get_it_done/IT/Excel_Fun/Excel_Don-ts/excel_don-ts.html
Es ist aber ok, wenn meine Kommentare Dir zuweilen seltsam anmuten. Mir geht es mit Deinem Programmierstil ebenso (nicht unfreundlich gemeint) :-)
Für das tiefere Verständnis der Materie muss man wohl Deine und Harlan's Funktion detaillierter vergleichen.
Viele Grüße,
Bernd P
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige