Solved

2 dimension array and lookup

Posted on 2015-02-02
11
70 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
  • 4
  • 2
  • 2
  • +2
11 Comments
 
LVL 12

Assisted Solution

by:jkaios
jkaios earned 100 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:Michael74
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
 
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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 23

Assisted Solution

by:Michael74
Michael74 earned 100 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 300 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 45

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 45

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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

746 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now