@Interessenten [Teil1]: INDIREKT-Ersatz
28.01.2020 00:37:31
Luc:-?
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 Blattintervalle 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):Q | R | S | T | U | V | W | X | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
9 |
| |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
10 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
11 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
12 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
13 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
14 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
15 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
16 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
17 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
18 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
19 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
20 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
21 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
22 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
23 |
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