Microsoft Excel

Herbers Excel/VBA-Archiv

Informationen und Beispiele zum Thema MsgBox
BildScreenshot zu MsgBox MsgBox-Seite mit Beispielarbeitsmappe aufrufen

Array mit Namen und Inhalten erstellen

Betrifft: Array mit Namen und Inhalten erstellen von: Barbara
Geschrieben am: 02.08.2014 23:47:21

Hallo
habe ein Excel-Datei mit vielen Namen, die sich auf einzelne Zellen beziehen und einige Namen, die sich auf größere Bereiche beziehen.

Nun würde ich gerne ein zweispaltiges Array erstellen, das alle vorkommenden Namen, die sich auf EINE Zelle beziehen und daneben deren Inhalt speichert. Namen, die mehrere Zellen darstellen, sollen dabei ignoriert werden.

Doch es gelingt mir nicht. Bitte um Hilfe.
Es geht irgendwie mit ActiveWorkbook.Names(i).value, aber den Namen selbst? Und ins array?

Dieses Array will ich dann einem anderen Makro übergeben, wo der Wert einer Zelle eines bestimmten Namens ermittelt wird, aber aus diesem Array, nicht aus der Tabelle.

LG,
Barbara

  

Betrifft: AW: Array mit Namen und Inhalten erstellen von: Oberschlumpf
Geschrieben am: 03.08.2014 01:35:40

Hi Barbara

Das hier ist meine VBA-Idee (getestet)

Sub sbNames()

    Dim lnaNames As Name, larArr(), liIdx As Integer, lstrMsg As String
    
        For Each lnaNames In ActiveWorkbook.Names
            If InStr(lnaNames.RefersTo, ":") = 0 Then
                ReDim Preserve larArr(1, liIdx)
                larArr(0, liIdx) = lnaNames.Name
                larArr(1, liIdx) = lnaNames.RefersToRange.Text
                liIdx = liIdx + 1
            End If
        Next
        For liIdx = 0 To UBound(larArr, 2)
            lstrMsg = "Zellname: " & larArr(0, liIdx) & vbCrLf
            lstrMsg = lstrMsg & "Zellwert: " & larArr(1, liIdx)
                If MsgBox(lstrMsg, vbYesNo, "weiter?") = vbNo Then Exit Sub
            lstrMsg = ""
        Next
        
End Sub

Ich hab in der Testdatei 4 Zellnamen definiert + Werte eingetragen:
Zelle A1 = eins = ZelleA1
Zelle A2 = zwei = ZelleA2
Zelle A3 = drei = ZelleA3
Zellen B1:C3 = B1C3 = ohne Werte

Im ersten Code-Teil (For Each...) werden die definierten Namen in dein gewünschtes 2-spaltiges Array eingelesen.
Es werden nur die Zellnamen ins Array übergeben, die sich auf nur eine Zelle beziehen.
Der Zellname B1C3 wird, wie gewünscht, nicht berücksichtigt.
Im zweiten Code-Teil (For liIdx = ...) werden mit Hilfe einer MsgBox die zusammengehörigen Array-Paare angezeigt.

Hilfts?

Ciao
Thorsten


  

Betrifft: AW: Array mit Namen und Inhalten erstellen von: Barbara
Geschrieben am: 03.08.2014 13:56:26



Hallo Oberschlumpf

habe Dein Makro etwas abgeändert. Siehe hier:
https://www.herber.de/bbs/user/91851.xlsm

Mir ist aufgefallen, dass Namen ohne Bezug zu einer Fehlermeldung führen. Vielleicht kann man das an geeigneter Stelle prüfen. Vielleicht, ob ein Bezug zu einer konkreten Zelle besteht.

Jetzt habe ich ein On Error Resume Next eingebaut. Und das sollte aber den unbekannten Fehlern vorbehalten sein.

Danke jedenfalls für Dein Beispiel, ich habe einiges Neues gelernt und kann es gut gebrauchen, ebenso Deine Erklärung dazu ist für mich hilfreich.

Erich, Dir auch vielen Dank, auch wenn ich derzeit dafür keine Anwendung habe. Aber ich behalte es im Hinterkopf.

LG,
Barbara


  

Betrifft: AW: Array mit Namen und Inhalten erstellen von: Oberschlumpf
Geschrieben am: 03.08.2014 14:12:12

Hi Barbara,

ändere diese Zeile

If InStr(lnaNames.RefersTo, ":") = 0 Then

um in diesen Code
If InStr(lnaNames.RefersTo, ":") = 0 And _
   InStr(lnaNames.RefersTo, "#") = 0 Then

So kannst du die If-Bedingung erweitern, wenn noch weitere Zustände ausgeschlossen werden sollen.

Hilfts?

Ciao
Thorsten


  

Betrifft: AW: Array mit Namen und Inhalten erstellen von: Barbara
Geschrieben am: 03.08.2014 20:09:56

danke Oberschlumpf. So gehts natürlich, dachte, dass man irgend wie schlüssig erkennen kann, aus wie vielen Zellen der Bereich besteht.

Aber eine andere Frage hätte ich noch, und zwar zu:

Dim lnaNames As Name
Was bedeutet "As Name", ist das eine offizielle Bezeichnung? Denn mit "As Object" geht es auch. Oder geht es so irgend wie besser?

LG,
Barbara


  

Betrifft: AW: Array mit Namen und Inhalten erstellen von: Barbara
Geschrieben am: 03.08.2014 20:25:03

Noch was: Sieht so aus, larArr() ist als Integer definiert. Ist es aber nicht. Es ist ein Feld, das neben Zahlen auch Strings enthalten kann, je nach Inhalt.

Könntest Du vielleicht erklären, was es damit auf sich hat?

LG,
Barbara


  

Betrifft: AW: Array mit Namen und Inhalten erstellen von: Oberschlumpf
Geschrieben am: 03.08.2014 21:35:13

verstehe nicht ganz, was du meinst.

a) die Variable larArr() ist nicht als Integer, sondern als Variant deklariert.
b) in deiner Bsp-Datei steht in Zelle B7 die Zahl 14 und wird bei Makrostart auch im Array mit aufgenommen. Und auch die anderen String-Werte und der einzige Boolsche Wert wird im Array mit aufgenommen.

lnaNames hab ich deshalb als Name deklariert, weil ich ja in der For Each-Schleife nur die Namens-Einträge überprüfe.
Ob nun als Object oder Names deklariert, ich weiß nicht, was besser ist, finde Names hier nur passender.
Diese Erklärung is sicher nich hilfreich. Kanns aber nich besser erklären. (außer a)+b) )

Ciao
Thorsten


  

Betrifft: AW: Array mit Namen und Inhalten erstellen von: Barbara
Geschrieben am: 03.08.2014 23:43:54


Ja, da hast du natürlich recht.

Ich verstehe alles, was du geschrieben hast.

Und wenn nicht, melde ich mich wieder.

Danke für Deine Unterstützung und Hilfe, auch den anderen.
Bin nun wieder ein Eck gescheiter geworden. Mal seh'n, ob das bis morgen anhält.

LG, Barbara


  

Betrifft: noch ein Versuch von: Erich G.
Geschrieben am: 04.08.2014 11:34:32

Hi,
da ich vermute, dass mein Dictionary-Vorschlag durchaus praktisch bei deiner derzeitigen Aufgabe wäre,
habe ich mal Thorstens und meinen Code analog zusammengestellt:

Sub sbNames()                                                        ' von Thorsten
   Dim lnaNames As Name, larArr(), liIdx As Integer, lstrMsg As String

'   On Error Resume Next               nicht nötig, möglichst vermeiden
   For Each lnaNames In ActiveWorkbook.Names
      With lnaNames
                        ' Hier sollte geprüft werden, ob ein Bezug zu einer
                        ' einzelnen existierenden Zelle besteht:
         If InStr(.RefersTo, ":") + InStr(.RefersTo, "#") = 0 Then
            ReDim Preserve larArr(2, liIdx)
            larArr(0, liIdx) = .Index
            larArr(1, liIdx) = .Name
                           'Ein Name ohne Bezug erzeugte hier einen Fehler:
            larArr(2, liIdx) = .RefersToRange.Text
            liIdx = liIdx + 1
         End If
      End With
   Next
   For liIdx = 0 To UBound(larArr, 2)
      lstrMsg = "Index: " & larArr(0, liIdx) & vbCrLf
      lstrMsg = lstrMsg & "Zellname: " & larArr(1, liIdx) & vbCrLf
      lstrMsg = lstrMsg & "Zellwert: " & larArr(2, liIdx)
      Sheets(3).Cells(liIdx + 1, 2) = lstrMsg
   Next
End Sub

Sub sbNames2()                                                       ' von Erich
   Dim oDic As Object, oNam As Name, ii As Long, arrNamen
   Set oDic = CreateObject("Scripting.Dictionary")
                                          ' Sammeln von Namen und Werten in oDic
   For Each oNam In ActiveWorkbook.Names
      With oNam
         If InStr(.RefersTo, ":") + InStr(.RefersTo, "#") = 0 Then _
            oDic.Add .Name, .RefersToRange
      End With
   Next
                                          ' Ausgabe in 2 Spalten in Blatt 3
   arrNamen = oDic.Keys                         ' Namen aller Namen
   For ii = 0 To UBound(arrNamen)
      Sheets(3).Cells(ii + 1, 4) = arrNamen(ii)       ' Name des Namens
      Sheets(3).Cells(ii + 1, 5) = oDic(arrNamen(ii)) ' Wert der benamten Zelle
   Next ii
End Sub
Da kannst du die beiden leichter miteinander vergleichen.

Zu deiner Frage bzgl. "Object" versus "Name":

Wenn du deklarierst
Dim myNam as Name
dann sagst du VBA damit, dass myNam ein Name sein soll.
Damit ist auch bekannt, welche Eigenschaften und Methoden zu myNam gehören,
z. B. die Eigenschaft "RefersTo".

Wenn du schreibst
Dim myNam as Object
ist das nicht falsch. Jeder Name ist auch ein Objekt. Aber VBA weiß nicht, zu welcher Art/Klasse/Typ myNam gehört.
myNam kann z. B. auch ein Workbook oder eine Pivottable sein.
Über Eigenschaften und Methoden weiß man hier nichts.

Je genauer man deklarieren kann, umso besser. Such mal nach "IntelliSense".
Wenn du in de angehängten Mappe in der Prozedur aTest unter dem Dim tippst:
xxxx.
dann dut sich nichts, wenn du aber
yyyy.
tippst, wird dir alles angeboten, was zu dem Namen yyyy passt.

Rückmeldung wäre nett! - Grüße aus Kamp-Lintfort von Erich


  

Betrifft: BeiSpielMappe von: Erich G.
Geschrieben am: 04.08.2014 11:42:39

Hi,
sorry - vergessen - hier die Mappe: https://www.herber.de/bbs/user/91872.xlsm

Rückmeldung wäre nett! - Grüße aus Kamp-Lintfort von Erich


  

Betrifft: AW: BeiSpielMappe von: Barbara
Geschrieben am: 04.08.2014 21:50:03

Hallo Erich,

Dein Beispiel zur Deklaration "Name" konnte ich nachvollziehen. - Danke.

Dein Dictionary-Vorschlag hat durchaus Vorteile:
- Das Befüllen geht in einer Zeile
- Das Finden des zugehörigen Items eines Keys, was ich später auch benötige, ist auch ganz einfach: oDic("Beruf")

Nur: Im Überwachungsfenster sehe ich immer nur die Keys. Die Items bleiben verborgen. Das hat mich zunächst sehr verwundert und ich habe Deinen Vorschlag schon verworfen. Aber dann, nach Internet-Recherche und genauerem Betrachten Deines Beispiels, habe ich gesehen, dass die Keys sehr wohl abrufbar sind. Verwundert bin ich trotzdem, dass das Überwachugsfenster das nicht zeigt.

Danke für Deine Hilfe. Wieder was gelernt.

LG, Barbara


  

Betrifft: AW: Variablendeklaration As Object vs. As Name von: fcs
Geschrieben am: 06.08.2014 09:43:54

Hallo Barbara,

"Object" ist der allgemeinere Variablentyp und kann bei allen Objekten unter VBA verwendet werden.

Spezifiert man Objekt-Variablen genauer entsprechend dem Objekttyp, der der Variablen per Set-Anweisung oder auch in einer Zeile wie
For Each objVar in ObjektListe
zugewiesen werden soll, dann erleichtert dies die Programmierung.
Bei genau deklarierten Objekt-Variablen wird, wenn man nach der Variablen oder innerhalb eines With-Blockes den Punkt eingibt, im VBA-Editor die Liste der zulässigen/möglichen Methoden und Eigenschaften angezeigt. Vorausetzung: Im VBA-Editor sind unter Extras die Optionen entsprechend gesetzt. Man vermeidet Fehler im Code und auch die Fehlersuche wird vereinfacht.

Gruß
Franz


  

Betrifft: AW: Variablendeklaration As Object vs. As Name von: Barbara
Geschrieben am: 06.08.2014 17:11:48

Franz, Erich,

Thank you, Thank you, Thank you.

LG,
Barbara


  

Betrifft: Namen und Werte in einem Dictionary von: Erich G.
Geschrieben am: 03.08.2014 12:17:57

Hi,
mit einem Dictionary gehen die Erstellung und auch die späteren Zugriffe einfacher und schneller.
Probier mal:

Option Explicit

Sub Steu()
   Dim myDic As Object
   
   NamenWerteDic myDic  ' Hier wird die Liste in als Dictionary erstellt.

   Call Zugriff(myDic)  ' Hier wird beispielhaft auf die Liste zugegriffen.
End Sub

Sub NamenWerteDic(oDic As Object)
   Dim oNam As Name
'  Dim arNamen, arValues         ' überflüssig, vielleicht zum Testen

   Set oDic = CreateObject("Scripting.Dictionary")
   For Each oNam In ThisWorkbook.Names
      With oNam
         If InStr(.RefersTo, ":") = 0 Then oDic.Add .Name, .RefersToRange
      End With
   Next
'  arNamen = oDic.Keys           ' überflüssig, vielleicht zum Testen
'  arValues = oDic.Items         ' überflüssig, vielleicht zum Testen
End Sub

Sub Zugriff(mDict As Object)
   MsgBox "Wert der Zelle mit dem Namen xxxx:" & vbLf & vbLf & mDict("xxxx")
   MsgBox "Wert der Zelle mit dem Namen Tabelle2!abc:" & vbLf & vbLf & mDict("Tabelle2!abc")
End Sub
Rückmeldung wäre nett! - Grüße aus Kamp-Lintfort von Erich


 

Beiträge aus den Excel-Beispielen zum Thema "Array mit Namen und Inhalten erstellen"