Link to home
Start Free TrialLog in
Avatar of AXISHK
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
SOLUTION
Avatar of jkaios
jkaios
Flag of Marshall Islands image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
If you will not have duplicate keys have a look at the Dictionary collection
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
Avatar of AXISHK
AXISHK

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 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)
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of AXISHK

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
Avatar of aikimark
Use a Variant data type for the array.

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.
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.