Solved

2 dimension array and lookup

Posted on 2015-02-02
Medium Priority
89 Views
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
0
Question by:AXISHK
• 4
• 2
• 2
• +2

LVL 12

Assisted Solution

jkaios earned 400 total points
ID: 40585507
This is a two-dimensional array, 1st dimension has 4 elements, 2nd dimension has 2, and to increase the number of elements in a dimension, simply change the subscript. The first dimension represents rows, and the second represents columns.

``````Dim SomeData(3,1)
' row 1
SomeData(0,0) = "VB"
SomeData(0,1) = 12.5
' row 2
SomeData(1,0) = "IT"
SomeData(1,1) = 5.7
' row 3
SomeData(2,0) = "US"
SomeData(2,1) = 98.25
' row 4
SomeData(3,0) = "VV"
SomeData(3,1) = 122.4

Dim sInput as String
Dim ii as Long
sInput = InputBox("Please enter something")
For ii = 0 to 3
If SomeData(ii,0) = "US" Then
MsgBox "data matching US: " & SomeData(ii,1)
Exit For
End If
Next
``````
0

LVL 23

Expert Comment

ID: 40585539
If you will not have duplicate keys have a look at the Dictionary collection
http://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
0

Author Comment

ID: 40585541
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
0

LVL 12

Expert Comment

ID: 40585542
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.
0

LVL 12

Expert Comment

ID: 40585548
To risize the array, simply use the ReDim statement.

ReDim someData(3, UBound(SomeData)+1)
0

LVL 23

Assisted Solution

Michael Fowler earned 400 total points
ID: 40585560
@jkaios. Dictionaries are slow compared to arrays if you need to iterate through them but much faster if you need to extract values for given keys or check for the existence of keys.

@AXISHK The key value pair in the Dictionary can be any consistent type you like with the only restriction being that you cannot have duplicate keys.
To add items you use
``````MyDictionary.Add Key, Item
``````
To check if a key already exists and create an entry if not you can use
``````If Not  MyDictionary.Exists(SomeKey) Then .Add SomeKey, SomeValue
``````
0

LVL 4

Accepted Solution

Monika Bharti earned 1200 total points
ID: 40585565
Hi,

For creating two dimensional dynamic array in VBA, follow the steps mentioned below:

Declare the two dimensional Array
Re size the array
Store values in array
Retrieve values from array

Declare the two dimensional Array
Dim arrTwoD()
Resize the array
ReDim Preserve arrTwoD(1 To 2, 1 To 2)
Store values in array
arrTwoD(i, j) = Sheet9.Cells(i, j)
Retrieve values from array
arrTwoD(5, 2)

``````Function FnTwoDimentionDynamic()

Dim arrTwoD()

Dim intRows

Dim intCols

intRows = Sheet9.UsedRange.Rows.Count

intCols = Sheet9.UsedRange.Columns.Count

ReDim Preserve arrTwoD(1 To intRows, 1 To intCols)

For i = 1 To UBound(arrTwoD, 1)

For j = 1 To UBound(arrTwoD, 2)

arrTwoD(i, j) = Sheet9.Cells(i, j)

Next

Next

MsgBox "The value is B5 is " & arrTwoD(5, 2)

End Function
``````

Output
0

Author Comment

ID: 40585743
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
0

LVL 47

Expert Comment

ID: 40586304
Use a Variant data type for the array.

Are the keys you want to search string data or anything (date, numbers, strings, etc.)?
0

LVL 47

Expert Comment

ID: 40586312
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.
0

LVL 12

Expert Comment

ID: 40587588
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.
0

Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment Already a member? Login.

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Suggested Courses
Course of the Month14 days, 7 hours left to enroll

600 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.