In this lesson, we explore how to store and manage groups of information using Data Structures. We compare the three most common ways to hold data in VBA Arrays, Collections, and Dictionaries so you can choose the right tool for your specific automation task.
Key Takeaways:
- Fixed Arrays: Store a set number of items using specific index positions.
- Dynamic Arrays: Change size during code execution using the ReDim keyword.
- Preserve Data: Use ReDim Preserve to keep existing data when resizing.
- VBA Collections: Store items flexibly without needing to manually resize them.
- One-Based Indexing: Remember that Collections start counting at 1, not 0.
- Scripting Dictionaries: Prevent duplicate entries by using unique “Keys” for data.
- Fast Retrieval: Use Dictionaries for high-speed searching in large data sets.
- Early Binding: Reference “Microsoft Scripting Runtime” for better coding support (IntelliSense).
