• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 86
  • Last Modified:

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
0
AXISHK
Asked:
AXISHK
  • 4
  • 2
  • 2
  • +2
3 Solutions
 
jkaiosCommented:
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

Open in new window

0
 
Michael FowlerSolutions ConsultantCommented:
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
 
AXISHKAuthor Commented:
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
jkaiosCommented:
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
 
jkaiosCommented:
To risize the array, simply use the ReDim statement.

ReDim someData(3, UBound(SomeData)+1)
0
 
Michael FowlerSolutions ConsultantCommented:
@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

Open in new window

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

Open in new window

0
 
Monika BhartiCommented:
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)

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

Open in new window


Output
4.jpg
0
 
AXISHKAuthor Commented:
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
 
aikimarkCommented:
Use a Variant data type for the array.

Are the keys you want to search string data or anything (date, numbers, strings, etc.)?
0
 
aikimarkCommented:
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
 
jkaiosCommented:
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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 4
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now