AXISHK
asked on
2 dimension array and lookup
Any example for defining a 2 dimension array in VBA and retrieve a particular value, eg When user input US, 98.25 will be returned, Tks
VB 12.5
IT 5.7
US 98.25
VV 122.4
VB 12.5
IT 5.7
US 98.25
VV 122.4
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
How about if the array is defined as (3,2) which the second item will contain the description. Can it be solved with Dictionary Collection ?
The number of row will be dynamic and new item will be added to the array in case it can't find a record. How can the code handle it ?
Tks
The number of row will be dynamic and new item will be added to the array in case it can't find a record. How can the code handle it ?
Tks
The beauty and significance of arrays compared to Collections are that they're fast. Collections (ie, intrinsic collection objects, dictionary objects, etc.), on the other hand, although effective, are slow and affect performance if dealing with huge data and lenghty loops.
To risize the array, simply use the ReDim statement.
ReDim someData(3, UBound(SomeData)+1)
ReDim someData(3, UBound(SomeData)+1)
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Can the column be different data type, say string, integer.
How to initialize the array ?
How to pass the whole array through a Function in case my array consist of different data type ? Tks
How to initialize the array ?
How to pass the whole array through a Function in case my array consist of different data type ? Tks
Use a Variant data type for the array.
Are the keys you want to search string data or anything (date, numbers, strings, etc.)?
Are the keys you want to search string data or anything (date, numbers, strings, etc.)?
What is the context (bigger picture and scope) for this problem?
You are asking a HOW question and the best answer may depend on the WHAT problem you are actually trying to solve.
You are asking a HOW question and the best answer may depend on the WHAT problem you are actually trying to solve.
My first and original example was based on variant data type, especially for that purpose of making the array "all-purpose" type. BTW, I thought the question has already been answered where it was basically asking for a simple example of using arrays.
https://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/A_3391-Using-the-Dictionary-Class-in-VBA.html
A Dictionary Collection allows you to input a key value and retrieve its value without having to know the Index eg
MyDict.Item("SomeKey") will return the value associated with SomeKey