?
Solved

2 dimension array and lookup

Posted on 2015-02-02
11
Medium Priority
?
80 Views
Last Modified: 2016-02-10
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
Comment
Question by:AXISHK
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
  • +2
11 Comments
 
LVL 12

Assisted Solution

by:jkaios
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

Open in new window

0
 
LVL 23

Expert Comment

by:Michael Fowler
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

by:AXISHK
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 12

Expert Comment

by:jkaios
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

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

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

Assisted Solution

by:Michael Fowler
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

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
 
LVL 4

Accepted Solution

by:
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)

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
 

Author Comment

by:AXISHK
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 46

Expert Comment

by:aikimark
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 46

Expert Comment

by:aikimark
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

by:jkaios
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

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

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

Join & Ask a Question