Global public variables (defined on Excel worksheet) get reset after a call to ActiveSheet.OLEObjects is made

Posted on 2005-02-25
Medium Priority
Last Modified: 2008-02-01
I came across a bug in Excel. When a call is made to ActiveSheet.OLEObjects, it seems to wipe out (set to initialization values) the global variables. To simulate this behavior I defined tmpStr in Sheet1.
I initialized tmpStr in Activate(), Clicked TradeList button which invokes ReadTradeList_Click() and PopulateTradeInfo(call to OLEObjects is made). After that I hit Update button, btnUpdate_Click() gets executed. But in this routine 'Debug.print tmpStr'  prints blanks. If I comment out calls to OLEObjects, in PopulateTradeInfo, 'Debug.print tmpStr' prints correct value in  btnUpdate_Click().  

Any help to work around this will be very much appreciated. Thanks.

The relevant code is as follows.

Sequence of events is Activate(), ReadTradeList_Click() and then Sub btnUpdate_Click().

Public tmpStr As String

Private Sub Worksheet_Activate()

    Dim rng As Range, cl As Range

    tmpStr = "INITIALIZED"

End Sub

Private Sub ReadTradeList_Click()

    Debug.Print "Here :" & Sheet1.tmpStr

    'The above statement prints "INITIALIZED" as expected


End Sub

Public Sub PopulateTradeInfo()
    Application.Cursor = xlWait    
    Worksheets("ENV").Range("ALL_OUT").Value = ""

    lineNo = 1
    For i = 1 To cnt
        Dim j As Integer
        j = 1
        For j = 1 To 5
        Debug.Print "Before :" & Sheet1.tmpStr
        ''The above statement prints "INITIALIZED" as expected
        ' ActiveSheet.OLEObjects.Add is called in the following function AddCombo

        comboboxNm = AddCombo(Worksheets("ENV").Range("OUT").Cells(lineNo, 11))
        Debug.Print "After :" & Sheet1.tmpStr
        'The above statement prints "INITIALIZED" as expected

        ActiveSheet.OLEObjects(comboboxNm).ListFillRange = "ScrapWork!A1:A16"

        ActiveSheet.OLEObjects(comboboxNm).LinkedCell = Worksheets("ENV").Range("OUT").Cells(lineNo, 11).Address

        lineNo = lineNo + 1

    Next i
    Application.Cursor = xlDefault
End Sub

Private Sub btnUpdate_Click()

    Debug.Print "Here :" & Sheet1.tmpStr

    'The above statement prints blanks instead of "INITIALIZED"


End Sub

Public Function AddCombo(rTarget As Range) As String
With rTarget
AddCombo = ActiveSheet.OLEObjects.Add _
(ClassType:="Forms.ComboBox.1", _
Link:=False, _
DisplayAsIcon:=False, _
Left:=.Left, _
Top:=.Top, _
Width:=.Width, _
End With

End Function


Question by:anandcha
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Accepted Solution

Xygxag earned 2000 total points
ID: 13406500
Anandcha -

You shouldn't initialize the variables in the Activate event, as it gets called every time the object receives focus.  Use the Load event instead, or if you MUST use the Activate event, set a module-level variable that can check whether initialization has occurred yet:

If bInitialized = false then
   bInitialized = true
   '  initialize your variables here

~~ Mark.    

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Suggested Courses
Course of the Month12 days, 4 hours left to enroll

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question