CODEWITHSUNDEEP
arraysdatabaseexcelms-wordvba
loremipsum
loremipsum

Reputation: 25

What is the Best Method for Storing Data

I am creating a Word userform using VBA. I store several configuration using array in the program code, such as the following:

Public arrConfiguration[2, 3] as Integer

where index 2 represent type 0 to 2, and index 3 represent properties 0 to 3 for each type.

However, I planned to modify the program for larger amount of data (such as for 100 different types of data and 50 properties for each data). My question is, should I keep storing the data using array in the program, so that it will be

Public arrConfiguration[99, 49] as Integer

or store it in an Excel file, and make the program open the Excel file and access the cells repeatedly? Which one is better?

Thank you.

Upvotes: 1

Views: 464

Answers (3)

Ben
Ben

Reputation: 355

Depending on how you see things evolving, you might want to consider accessing your Excel data via ADO, rather than OLE Automation. That way, if you decide to change your storage system to Access, SQL Server or something else, you will have less work to do.

How To Use ADO with Excel Data from Visual Basic or VBA (Microsoft)
https://support.microsoft.com/en-gb/kb/257819

Read and Write Excel Documents Using OLEDB (Codeproject)
http://www.codeproject.com/Tips/705470/Read-and-Write-Excel-Documents-Using-OLEDB

Upvotes: 0

iDevlop
iDevlop

Reputation: 25262

You can simplify @skkakkar code: 

dim x as variant
x = range("A1").CurrentRegion

No Redim, no loops.

Upvotes: 0

skkakkar
skkakkar

Reputation: 2828

Please prefer excel. Sample example data image is appended here-under.

  • For cre­at­ing two dimen­sional dynamic array in excel, fol­low the steps below:

◾Declare the two dimen­sional Array

◾Resize the array

◾Store val­ues in array

◾Retrieve val­ues from array

Array sample example

Sub FnTwoDimentionDynamic()

   Dim arrTwoD()

   Dim intRows

   Dim intCols

    intRows = Sheet1.UsedRange.Rows.Count

    intCols = Sheet1.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) = Sheet1.Cells(i, j)

      Next

   Next

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

End Sub

In the Message Box you will get the following output.

enter image description here

Further To visualize a two dimensional array we could picture a row of CD racks. To make things easier, we can imagine that each CD rack could be for a different artist. Like the CDs, the racks would be identifiable by number. Below we'll define a two dimensional array representing a row of CD racks. The strings inside of the array will represent album titles.

2D Array Illustration

For multidimensional arrays it should be noted that only the last dimension can be resized. That means that given our example above, once we created the array with two CD racks, we would not be able to add more racks, we would only be able to change the number of CDs each rack held.

Upvotes: 1

Related Questions