Zugriff auf benamste Zellen in anderen Mappen

Laika · 11. November 2019 um 20:30

Hallo,

ich habe in der Personl.xls benamste Zellen. Den Wert dieser Zellen will ich in einer VBA-Prozedur lesen. Dazu steht da

var = [Personl.xls!name]

Dann kommt ein Fehler „Typen unverträglich“. var ist Integer, die Zelle als Zahl formatiert.
Ziehe ich den Wert mit

='[PERSONL.XLS]Modulname'!name

in die aufrufende Mappe und schreibe in VBA

var = [name]

dann geht’s.

Was kann da sein?

In den Verweisen ist Personl.xls referenziert, die Sicherheitsstufe niedrig.

Danke
Laika

Thomas_Ramel · 11. November 2019 um 20:30

Grüezi Laika

…dein Ärger hindert dich vermutlich daran, klare Aussagen zu machen…

ich habe in der Personl.xls benamste Zellen. Den Wert dieser
Zellen will ich in einer VBA-Prozedur lesen. Dazu steht da

var = [Personl.xls!name]

…warum mit Evaluate statt als direkten Zugriff/Bezug?

Dann kommt ein Fehler „Typen unverträglich“. var ist Integer,
die Zelle als Zahl formatiert.
Ziehe ich den Wert mit

=’[PERSONL.XLS]Modulname’!name

in die aufrufende
Mappe

‚Ziehen‘ tust Du das in einer Zelle?
Was genau ist denn der Modulname hier - eine benannte Zelle ist entweder global mappenweit gültig oder nur lokal im Tabellenblatt.

Mit einem Modul - welches wäre das denn genau - hat das eigentlich nichts zu tun.
Ausser Du hast in dem Modul eine Public Variable deklariert, die Du so nun abfragst…

und schreibe in VBA

var = [name]

dann geht’s.

Was kann da sein?

So direkt aus der Hüfte geschossen kann ich nix konkretes dazu sagen, mir ist da, wie Du siehst, noch so das eine oder andere nicht ganz klar.

BTW:
Warum willst Du aus VBA heraus auf benannte Zellen in der personl.xls zugreifen?
Die ist doch eigentlich ‚nur‘ als Container für Code gedacht?

Mit freundlichen Grüssen

Thomas Ramel

MVP für MS-Excel -

Thomas_Ramel · 11. November 2019 um 20:30

Grüezi Laika nochmals

ich habe in der Personl.xls benamste Zellen. Den Wert dieser
Zellen will ich in einer VBA-Prozedur lesen. Dazu steht da

var = [Personl.xls!name]

Dann kommt ein Fehler „Typen unverträglich“. var ist Integer,
die Zelle als Zahl formatiert.
Was kann da sein?

So direkt aus der Hüfte geschossen kann ich nix konkretes dazu
sagen, mir ist da, wie Du siehst, noch so das eine oder andere
nicht ganz klar.

Sodele, ich habe mir das Ganze nun nochmals angesehen und stelle folgendes fest (leider hilfts dir vermutlich nicht wirklich weiter):

Das hier:

Public Sub tr\_TestName()
Dim myVar As Integer
 myVar = [personal.xlsb!myName]
 MsgBox myVar
End Sub

Gibt mir zuverlässig den Wert der Zelle aus, die in der personal.xlsb (heisst ab xl2007 so) mit ‚myName‘ benamst ist.

…was bei dir da nicht läuft ist daher unklar.
Hast Du das Ganze schon mal in einer neu erstellten Mappe getestet?

Mit freundlichen Grüssen

Thomas Ramel

MVP für MS-Excel -

Laika · 11. November 2019 um 20:30

Also, letzte Antwort heute abend, sonst schmeckt mein Bier gleich nicht mehr.

Public Sub tr_TestName()
Dim myVar As Integer
myVar = [personal.xlsb!myName]
MsgBox myVar
End Sub

Gibt mir zuverlässig den Wert der Zelle aus, die in der
personal.xlsb (heisst ab xl2007 so) mit ‚myName‘ benamst ist.

Exakt so habe ich es gemacht. Habe Deinen Code in eine neue Mappe kopiert und nur myName durch meinen Zellnamen ersetzt … wie gehabt, „Typen unverträglich“.

Was ich will: Ich habe einige Mappen, die auf die COM-Schnittstelle zugreifen (könnte ja aber auch irgendwas anderes sein). Die Deklarationen der DLL und einige Werte - Schnittstellennr., Pufferlänge, Delays usw. - möchte ich für alle diese Mappen zentral hinterlegen, z.Zt. eben in der Personl.xls. Die DLL-Deklarationen und einige Hilfsroutinen sind in einem Modul, die Werte in einem Tabellenblatt, da dann sowohl Tabellen als auch Prozeduren in anderen Mappen darauf zugreifen können.
Das ist alles. Eigentlich was ganz Simples - oder?

Nebenbei: Kennst Du Schnittstellenroutinen für COM-Zugriffe? Ich betreibe einen Microcontroller als „Hausleitsystem“ (Heizung, Warmwasser, Fenster-, Türenüberwachung, Rollladensteuerung u.a.), mit dem ich kommunizieren möchte, um z.B. Daten auszulesen Ich habe jetzt die RSCOM.DLL und RSAPI.DLL. Die sind, so scheint’s mir, etwas merkwürdig. Es gibt auch keinerlei Beschreibung dazu - habe mir schon die Finger wund gegoogelt.
Gibt es was VBA-internes?

Schönen Abend/Sonntag
Laika

Markus_K_ln · 11. November 2019 um 20:30

Hi Laika,

dankeschön für diese Anregung. Ich selbst würde zwar kein Hausleitsystem betreiben (Bude ist einfach zu klein), aber für meinen Beruf war das eine tolle Anregung!

Und zwar deshalb: in den meisten Firmen gibt es ein Programm, mit denen der Geschäftsbetrieb abgewickelt wird (nennen wir es zentrale EDV; früher lief sie meist auf Großrechnern, nach Einführung der PCs wurden Terminalprogramme benutzt, bis das Neuprogrammieren für PC-Netze endlich durchgeführt war [bei der GEZ ~2005 für deren Programm aus 1977]).

An jedem PC-Arbeitsplatz gibt es aber zusätzlich noch Aufgaben, die automatisiert werden könnten. Wenn diese Aufgaben richtig zeitaufwendig sind, muß der PC her. Wenn diese Aufgaben aber nicht so häufig auftreten, ist das Erweitern der HanSA zu teuer. Weil Office sowieso auf jedem PC läuft, wird diese Aufgabe dann in Office abgebildet.

Und jetzt kommt’s, warum Dein Ansatz so genial ist:

Für die meisten Aufgaben sind viele Excel-Tabellen notwendig. Diese Tabellen benötigen fast immer Angaben, die über Monate konstant sind, sich aber dennoch ändern können (z.B.: Mehrwertsteuersatz, Wechselkurs, Preiszuschlag aus den AGB, u.v.a.).
Diese Angaben sind natürlich in der zentralen EDV hinterlegt und werden dort auch zeitgerecht eingepflegt. Aber in den hunderten Excel-Tabellen auf einem Arbeitsplatz-PC bildet sich eine Änderung nicht automatisch ab. Was tun?

Ich würde niemals jedem Benutzer erlauben, ständig auf die zentrale EDV zuzugreifen. Aber die Idee, diese „Halbvariablen“ (ein besserer Name fällt mir nicht ein) in die Personl.xls einzulesen und alle anderen Tabellen darauf zugreifen zu lassen, finde ich einfach klasse, denn man kann diese Halbvariablen beim Programmstart aktualisieren (AutoOpen). Das reicht, denn Mehrwertsteuersätze ändern sich nicht im Laufe eines Tages .

Danke Dir und

schönes Wochenende

Markus

Laika · 11. November 2019 um 20:30

Hallo Markus,

dankeschön für diese Anregung.

Bitte, freut mich, hier zu helfen, dazu ist solch ein Forum ja da. Ich spinxe ab und zu auch mal durch, um mir Anregungen zu holen.

Und jetzt kommt’s, warum Dein Ansatz so genial ist:

Na ja, schönen Dank, aber genial ist’s nicht, einfach nur praktisch.

Aber die Idee, diese „Halbvariablen“
(ein besserer Name fällt mir nicht ein) in die Personl.xls
einzulesen …

Da könnte dann an jedem Arbeitsplatz auch eine platz- oder benutzerspezifische Personl.xls installiert sein.

Ebenfalls schönen (leider verregneten ) Sonntag
Laika

Reinhard_fb8cb7 · 11. November 2019 um 20:31

Hallo Laika,

var = [Personl.xls!name]

Dann kommt ein Fehler „Typen unverträglich“. var ist Integer,
die Zelle als Zahl formatiert.

sehr seltsamer Fehler, aber es ist korrekt, der kommt.

Ziehe ich den Wert mit

=’[PERSONL.XLS]Modulname’!name

in die aufrufende
Mappe

was hat denn ein Modulname mit einer benamten Zelle zu tun?

In den Verweisen ist Personl.xls referenziert,

ups, wie geht denn das, muß da mal schauen.

Sub tr\_TestName()
Dim myVar 'As Integer
Call benannteZelleA1
myVar = Workbooks("personl.xls").Worksheets("Tabelle1").Range("myName").Value
MsgBox myVar
End Sub

Sub benannteZelleA1()
With Workbooks("personl.xls")
 .Names.Add Name:="myName", RefersToR1C1:="=Tabelle1!R1C1"
 .Worksheets("Tabelle1").Range("myName").Value = 17
End With
End Sub

Gruß
Reinhard

Reinhard_fb8cb7 · 11. November 2019 um 20:31

Codeerweiterung
Hallo Laika,

with a little help from my friends, in dem Fall Danke an Tino:

Sub tr\_TestName()
Dim myVar1 As Integer, myVar2 As Integer, myVar3 As Integer
Call benannteZelleA1
myVar1 = Workbooks("personl.xls").Worksheets("Tabelle1").Range("myName").Value
myVar2 = Range("'[personl.xls]Tabelle1'!myName").Value
myVar3 = ['[personl.xls]Tabelle1'!myName].Value
MsgBox myVar1 & vbCr & myVar2 & vbCr & myVar3
End Sub

Sub benannteZelleA1()
With Workbooks("personl.xls")
 .Names.Add Name:="myName", RefersToR1C1:="=Tabelle1!R1C1"
 .Worksheets("Tabelle1").Range("myName").Value = 17
End With
End Sub

Gruß
Reinhard

Reinhard_fb8cb7 · 11. November 2019 um 20:33

personl.xlsb und nicht personl.xls
Hallo Laika,

natürlich hat Thomas Recht mit der Endung xlsb bei XL2007.

Gruß
Reinhard

Thomas_Ramel · 11. November 2019 um 20:33

Grüezi Markus

Ich würde niemals jedem Benutzer erlauben, ständig auf die
zentrale EDV zuzugreifen. Aber die Idee, diese „Halbvariablen“
(ein besserer Name fällt mir nicht ein) in die Personl.xls
einzulesen und alle anderen Tabellen darauf zugreifen zu
lassen, finde ich einfach klasse, denn man kann diese
Halbvariablen beim Programmstart aktualisieren (AutoOpen).

Für solche Firmen-spezifischen Daten eignet sich IMO aber auch ein zentral gespeichertes AddIn äusserst gut (gerade auch aus administrativen Gründen).
Im Ornder XLSTART eines jeden Users muss dann nur ein Link auf diese Datei stehen.

Das hat dann zusätzlich den Vorteil, dass eine neue Version nur einmal zentral angepasst werden muss und beim nächsten Start von Excel stehen die neuen Daten (und auch Funktionen und Makros, wenn das von Interesse ist) dem User zur Verfügung.

Mit freundlichen Grüssen

Thomas Ramel

MVP für MS-Excel -