Public static collections in excel vba RRS feed

  • Question

  • Hello

    Is it possible to create a "public static collection"?

    i.e. create a collection in a procedure/module and declare it as static so the variables don't change until the code runs again, and then use the items from this collection in a different procedure in a different module? Just declaring the collection as a public variable creates run time error 91, and declaring the collection as static in the CreateColl procedure makes it only visible to this procedure.

    This code is wrong but should show what I'm trying to do:

    Public Static MyColl as collection 'this is wrong but should show what  need

    sub CreateColl()

    Static MyColl as new collection

    ....populate collection

    end sub

    sub UseColl()

    Dim MyNewVar as variant


    end sub



    Sunday, February 16, 2014 8:18 PM

All replies

  • You can use

    Public MyColl As Collection

    Or even

    Public MyColl As New Collection

    In the latter example, you don't have to use Set MyColl = ...

    But you shouldn't declare MyColl in CreateColl, for that only defines a local variable MyColl that won't be valid outside CreateColl. You want to use the public variable MyColl inside CreateColl too.

    Regards, Hans Vogelaar (

    Sunday, February 16, 2014 9:00 PM