[365] Überlauf per VBA ansprechen

[RPP63]

Moin!

Ich benutze recht häufig dynamische Formeln, um Beispieldateien zu erstellen.
Als Beispiel:
Um 1.000 zufällige Daten dieses Jahres auszugeben, nutze ich (in A1)
=DATUM(2021;1;ZUFALLSMATRIX(1000;;1;365;1))

Ziel:
Ich möchte mittels Makro diesen Bereich (hier A1:A1000) allgemeingültig ansprechen.

Range("A1").CurrentRegion fällt aus, weil z.B. auch in B etwas stehen kann.
Range("A1#") ebenfalls, weil mir die Startzelle nicht namentlich bekannt sein soll.
Range(ActiveCell.Address & "#").Address läuft in LZF 1004

Folgendes funktioniert:
Range(ActiveCell.Address(0, 0) & "#").Address
aber auch nur, wenn sich tatsächlich eine dynamische Formel in der aktiven Zelle befindet.

Konkrete Frage:
Gibt es (ähnlich wie für verbundene Zellen mit Range().MergeCells) eine Eigenschaft, mit der ich das Vorhandensein einer dynamischen Formel abfragen kann?

Gruß Ralf

[LCohen]

Hallo RPP63,

da es Dir um die 1. Spalte des dynB geht, würde ich die halt auch ansprechen (mittels Äquivalent zu INDEX, also wohl Range("A1#").CurrentRegion.Resize(, 1) oder so). Klappt dann auch mit Namen.

Das mit .CurrentRegion gilt ja nicht nur in VBA.

Da ich nicht so der VBA-Fuchs bin, vermute ich wie bei dem neuen ...Formula2... auch anderweitig Spracherweiterungen Richtung dynF, um die Art der Formel auslesen zu können.

Zu bedenken gebe ich folgendes: Wenn ich dynF verwende, habe ich korrekterweise nichts anderes südlich oder östlich davon. Oder mit anderen Worten: Ich weiß, dass es eine dynF ist, weil ich sie so geplant habe.

[RPP63]

Ja! Neee!

Allgemeiner:
=ZUFALLSMATRIX(z;s;1;365;1)
liefert eine Matrix mit z Zeilen und s Spalten, die ich ansprechen möchte.

Vielleicht noch Hintergrundinfos:
Mir geht es darum, die ausgegebene Formel-Matrix mittels Shortcut in Werte umzuwandeln.

Kopiere ich nur die Formelzelle und füge den Wert ein, so erhalte ich #ÜBERLAUF!

Ich behelfe mir momentan mit → Formelzelle → Strg+Umschalt+↓ (+ evtl. Strg+Umschalt+→)

Wohlgemerkt:
Range(ActiveCell.Address(0, 0) & "#") funktioniert ja,
aber nur, wenn ich auch eine dynamische Formel in der Zelle habe.

Selbstverständlich könnte ich mir ein meinem Shortcut-Makro ein Error-Handling einbauen, "schöner" wäre es aber, wenn die dynamische Formel eine Eigenschaft hätte, die ich abfragen könnte.

Konkret geht es mir um das "Aufbohren" dieses Formel-gegen-Wert-Makros in meiner Personal.xlsb

Sub FormelInWert()

'Shortcut Strg+Umschalt+W 

Dim x As Range

If TypeName(Selection) = "Range" Then

  With Application

    .ScreenUpdating = False

    For Each x In .Selection.Areas

        x.Copy: x.PasteSpecial xlPasteValues

    Next

    .CutCopyMode = False

    .Goto ActiveCell

  End With

End If

End Sub

[RPP63]

Auflösung! 
Meine gesuchte Eigenschaft ist .HasSpill
https://docs.microsoft.com/de-de/office/...e.hasspill

Um den "Überlauf"-Bereich anzusprechen, ginge mein sehr umständliches 
Range(ActiveCell.Address(0, 0) & "#")
aber sehr viel einfacher ist das entsprechende
ActiveCell.SpillingToRange

Falls man eine beliebige Zelle des Überlaufs markiert hat, muss man erst auf den .SpillParent zurückgreifen, um .SpillingToRange anzusprechen.

Zusatz: Um eine dynamische Formel in einen Range per VBA einzutragen, braucht es die von LCohen angesprochene .Formula2-Eigenschaft.

Heißt für mein Makro:

Sub FormelInWert()
'Shortcut Strg+Umschalt+W 
Dim x As Range
If TypeName(Selection) = "Range" Then
  With Application
    .ScreenUpdating = False
    For Each x In .Selection.Areas
        If x.HasSpill Then
          With x.SpillParent.SpillingToRange
            .Copy: .PasteSpecial xlPasteValues
          End With
        Else
          x.Copy: x.PasteSpecial xlPasteValues
        End If
    Next
    .CutCopyMode = False
    .Goto ActiveCell
  End With
End If
End Sub

War doch ganz einfach … 
(und ich überlege gerade, ob ich einen Workshop für den Beispielbereich VBA erstelle)

Gruß Ralf

[LCohen]

Wäre eine gute Idee! Vielleicht können Dir die CEF-ler dafür auch zeitlich unbegrenztes EDIT gewähren (für den Bereich). Um es jedoch wie ein Buch zu strukturieren, müsste es gleich endgültig (Endfassung) sein, wenn externe Links direkt auf Inhalte zeigen können sollen. Das ist ein Nachteil von Foren.

Anders wäre es, wenn das ganze Buch auf einer Seite wäre (oder auf wenigen, bei denen man Teilinhalte nicht von einem zum anderen schiebt) und dann Inhalte über Lesezeichen (Textmarken) erreichbar wären (dann ohne Gliederungssystematik).