Freitag, 19. Oktober 2007

Globale Variablen in Excel verlieren ihren Wert

Ein bekanntes Problem scheint zu sein, dass Excel (ich hatte das Problem mit Excel 2003) während der Laufzeit aus verschiedenen Anlässen die Werte globaler Variablen "vergisst".

Lt. Informationen aus dem Web passiert das wenn

  • der Code neu compiliert wird
  • ein Laufzeitfehler auftritt
Wenn Änderungen am Code durchgeführt werden ist es nachvollziehbar, dass neu compiliert werden muss.
Die Werte gehen aber auch verloren wenn keine Änderungen gemacht wurden und keine Laufzeitfehler aufgetreten sind (ist mit "Unterbrechen bei Fehlern" auf "Bei jedem Fehler" gesetzt leicht überprüfbar).

In meinem Fall dürfte die Ursache daran gelegen sein, dass ich zur Laufzeit per Programmcode Buttons hinzugefügt und entfernt habe (siehe Beitrag Buttons dynamisch zur Laufzeit hinzufügen ist in Excel leicht realisiert, aber ...).
Dadurch entsteht die Notwendigkeit den Programmcode neu zu kompilieren und das führt zum hier beschriebenen Phänomen.

Nach langer erfolgloser Suche im Web bin ich auf folgende Lösung gekommen, die in meiner Situation das Problem gelöst hat

Ich habe eine neue leere Arbeitsmappe erstellt und in ein Codemodul folgenden Code eingefügt

   1 Option Explicit
2
3 Public Co4COMObj As SWK5_P1.Co4COM
4
5 Public Function Co4COM() As SWK5_P1.Co4COM
6 If Co4COMObj Is Nothing Then
7 Set Co4COMObj = New SWK5_P1.Co4COM
8 End If
9
10 Set Co4COM = Co4COMObj
11 End Function

und die Arbeitsmappe als "Microsoft Office Excel-Add-In (*.xla)" gespeichert.

Bei diesem Beispiel geht es darum, dass eine Instanz einer COM-Komponente (SWK5_P1.Co4COM) erstellt wird und diese über einen längeren Zeitraum verfügbar bleiben soll.

Nach dem Prinzip des Singleton-Patterns wird geprüft ob die Variable Co4COMObj schon zugwiesen wurde.
Wenn ja wird sofort eine Referenz zurückgeliefert andernfalls wird zuvor mit New eine neue Instanz zugewiesen.

In der Arbeitsmappe, in der die globale Variable verwendet wird, wird diese *.xla (in diesem Fall Co4COM.xla) über das "Menü Extras/Add-Ins..." eingebunden.

Eine Prozedur kapselt den Zugriff darauf:

   1 Public Function Co4COM() As SWK5_P1.Co4COM
2 ' to trick the damned Excel loosing the value of global variables occassionally,
3 ' the public variable holding the COM component was moved to an AddIn
4 Set Co4COM = Application.Run("Co4COM.xla!Co4COM")
5 End Function

Hieraus wird ersichtlich, dass der Zugriff auf die Prozedur im Add-In, die eine Referenz auf die gewünschte Variable zurückliefert, etwas umständlicher ist als gewohnt.

Es können meines Wissens auch keine Variablen im Add-In direkt angesprochen werden sondern es müssen entsprechende Getter- und Setter-Prozeduren bzw Functions bereitgestellt werden.

Kommentare:

Elmar hat gesagt…

Unter Office 2002 (XP) muss das AddIn ins globale Microsoft - AddIn Verzeichnis kopiert werden und dann im Excel über "AddIns" angeklickt werden - bei mir gings nur manuell.

Unter Standard XP ist das hier:
C:\Documents and Settings\USERNAME\Application Data\Microsoft\AddIns

Zoechi hat gesagt…

In meinem Fall war, wie es scheint,
das Hinzufügen/Entfernen der Buttons zur Laufzeit
die Ursache für das Neukompilieren und in weiterer Folge das Zurücksetzen der globalen Variablen.