Global Collections

0 votes

I have a collection that I have declared at the top of a module as

Public myCol as new Collection

I'm using a Sub to populate the collection, however, the data are lost when the Sub is terminated because it doesn't write to the global collection.

As the items were generated in the sub, I'm guessing they just have a local scope.

How can I populate the collection worldwide using a routine or function?

I would prefer to not have to pass objects if possible as I am populating multiple global collections in the single subroutine.

Apr 1, 2023 in Others by Kithuzzz
• 38,020 points
519 views

1 answer to this question.

0 votes

To store your public objects and expose them via properties, create a Standard module. The object can then be returned after the property getter has verified that it has been initialised.

See an example:

Private m_collection As VBA.Collection

Public Property Get GlobalCollection() As VBA.Collection
    If m_collection Is Nothing Then Set m_collection = New VBA.Collection
    Set GlobalCollection = m_collection
End Property

To call it:

YourModuleName.GlobalCollection.Add "Something"

or

GlobalCollection.Add "Something"
answered Apr 1, 2023 by narikkadan
• 63,720 points

Related Questions In Others

0 votes
1 answer

What is the impact of covid-19 on a global economy?

Covid-19 that is co-corona,vi-virus,d-development,19-In the year 2019.The ...READ MORE

answered Mar 19, 2020 in Others by Niroj
• 82,880 points
1,070 views
0 votes
0 answers

DynamoDB Transactional Write on Global Tables With DB Streams

How does DynamoDB handle TransactionalWrite on the ...READ MORE

Apr 5, 2022 in Others by Kichu
• 19,050 points
437 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
1,107 views
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
3,538 views
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 23,260 points
828 views
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,690 points
986 views
0 votes
1 answer

How can I create global macros and templates in jenkins job builder?

you can append the path to the ...READ MORE

answered Apr 2, 2018 in Jenkins by ajs3033
• 7,300 points
1,787 views
0 votes
1 answer

How to create a global filter

Assuming you have created a dashboard already, ...READ MORE

answered Apr 13, 2018 in Tableau by QueenBee
• 1,820 points
1,431 views
0 votes
6 answers

How can we define global variables in java?

To define Global Variable you can make ...READ MORE

answered Dec 15, 2020 in Java by Gitika
• 65,890 points
117,443 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP