[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

2 dimension array and lookup

Posted on 2015-02-02
11
Medium Priority
?
85 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

This article describes a serious pitfall that can happen when deleting shapes using VBA.
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 demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
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…

649 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