Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


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
1 Comment

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
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…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Suggested Courses
Course of the Month13 days, 11 hours left to enroll

580 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