Looping through array variables and assigning data

[wackywoo105]

I want to generate multiple array variables (to hold strings and number) and loop through them. I was thinking something like:

For x = 1 to 9
Dim myarray_x(3) as variant
Next x

This doesn’t work but I guess can be overcome using:

Dim myarray(3, 8) as variant

The problem now is I want to enter a line of values like I can with a single dimension array:

myarray_1 = Array("Test", 3, 14, 4)

Is there a way to enter this data into a row of the multidimensional array rather than having to specify each column of each row separately? Also do I have it correct that the first number is column and the second is row?

myarray(0, 0) = “Test”
myarray(1, 0) = 3
myarray(2, 0) = 14
myarray(3, 0) = 4

 

[The_Doc_Man]

As to column vs row, that is a "mapping" issue within your mind. I.e. you picture it that way. From a strict standpoint, an array holds dimensional data and the interpretation of which dimension is which is up to you. If you WANT to think of "row" as the first dimension and "column" as the second dimension, you can. But you could just as easily have done it the other way, too.

If your data set is REALLY multi-formatted, I don't know an easy way to store stuff in the elements of an array in a bulk operation. IF they were all part of a delimited text string, you can assign values using the SPLIT function.

Split function (Visual Basic for Applications)

Office VBA reference topic

docs.microsoft.com

This would turn the delimited string into an array but since the inputs are somewhat restricted, you can only do this with strings.

 

[MajP]

Is there a way to enter this data into a row of the multidimensional array rather than having to specify each column of each row separately?

There is no native way to do this, but you could write code to have a generic function so that you could add or create based on a statement like.
myarray_1 = MultiDimArray("Test", 3, 14, 4)

I would download this

VBA Arrays

.
which would make building a function real easy.

 

[wackywoo105]

Thanks all. I will read up more on arrays but for now I have gone with this:

ar1 = Array(Title, 0, 0, 4)                                               'Title line 0
ar2 = Array([First Names], 8, 0, 27)                                            'Firstname Middlename line 0
ar3 = Array(Surname, 0, 1, 35)                                                  'Lastname line 1
ar4 = Array("", 0, 2, 35)                                                             'Previous lastname line 2
ar5 = Array(Address_1 & ", " & Address_2, 0, 3, 35)                 'Address line 1 line 3
ar6 = Array(City, 0, 4, 24)                                                          'Address line 2 line 4
ar7 = Array(usecode, 28, 4, 7)                                                   'Postcode line 5
ar8 = Array(Format(Birthdate, "DDMMYYYY"), 0, 5, 8)                 'DOB line 6

OuterArray = Array(ar1, ar2, ar3, ar4, ar5, ar6, ar7, ar8)

For y = 0 To 7
For x = 1 To min(OuterArray(y)(3), Len(OuterArray(y)(0)))

*/ do stuff/*

Next x
Next y

Is there a function that will return the total number of arrays in OuterArray? And is there a a way I can add all the ar# arrays into OuterArray without having to type them all out. This is just in case I want to add more later.

Also do you have to Dim the arrays or is this unnecessary? It still works if I don't.

 

[jdraw]

Just curious --is there some rationale for using arrays rather than table(s)? Perhaps you could tell us about the issue you're trying to resolve.

 

[arnelgp]

use table/recordset.
otherwise, create a Class for your object.

 

[The_Doc_Man]

To know the number of elements in an array, use UBound. This allows you to know the number of rows, columns, or whatever else you call your array dimensions since you can ask for the UBound of the 1st, 2nd, 3rd, ... up to the maximum of 60 dimensions on a VBA array.

UBound function (Visual Basic for Applications)

Office VBA reference topic

docs.microsoft.com

There is also an LBound in the case that you were passed an array by reference and it had strange boundaries. You can look that up easily enough if it applies to your needs. Doesn't sound at the moment like you need that one, but mentioning UBound means for completeness I should mention LBound.

Here is what Microsoft has to say about arrays:

Using arrays (VBA)

Office VBA reference topic

docs.microsoft.com

This article would perhaps be the source of your information on the Array function and the idea of which dimension is a row vs. which is a column. Some articles do in fact push the row, column concept. As long as you have only two dimensions, that makes sense.

I would be terribly remiss in my obligation if I didn't warn you that using memory-based arrays works but makes your system a resource hog quickly if you are not careful. If you create and delete arrays a lot within subroutines, you will use a piece of your process virtual memory to hold the so-called variable descriptors and a much bigger piece to hold the actual arrays. But when you exit the subroutine, locally declared arrays go onto the (garbage) heap and cannot be re-used easily until the program exits and releases ALL of its memory in one big flush. If this doesn't represent a huge frequency of occurrence or if the arrays never really get big, then it is probably not an issue. But if this ever grows in frequency of use, you run into the problem that this style of operation is potentially messy.

Let's talk about why you are doing this. It could lead to a Catch-22 situation. If you have only a few - or a few dozen - items to hold in an array, you will hardly notice the difference in speed by doing some kind of JOIN query vs. the memory-array-based data presentation. And if you have a large enough number of entries in this array that you WOULD see a difference, you would be rapidly approaching the point at which this method would start to strain your system's virtual memory.

If you were doing this on an IBM 1620 Mod II, I would say "Keep it in arrays" because of the speed differential on an old beast like that between memory and disk. But on modern machines, you are talking milliseconds of difference between memory and disk methods. Not to mention the programming headaches.

 

[wackywoo105]

Thanks again. The */ do stuff /* bit is this:

For y = 0 To UBound(OuterArray, 1)

horizpos = horizstart + (OuterArray(y)(1) * horizincrement)
vertpos = vertstart + (OuterArray(y)(2) * vertincrement)
party = OuterArray(y)(0)

For x = 1 To min(OuterArray(y)(3), Len(OuterArray(y)(0)))

Set ShpTB = DocPub.Pages(1).Shapes.AddTextbox _
(Orientation:=pbTextOrientationHorizontal, _
Left:=horizpos, Top:=vertpos, _
Width:=15, Height:=18)

DocPub.Pages(1).Shapes("Text Box " & textboxcount).TextFrame.TextRange.Text = UCase(Mid([party], x, 1))
horizpos = horizpos + horizincrement

textboxcount = textboxcount + 1
Next x
Next y

I'm using data from a table and creating a publisher document, so it can be printed onto a form that has individual letter input boxes pre printed. With this code I can get it to line up with the boxes by altering a few numbers. I also want it to be easy to add in other elements in future. I tried with excel but excel has an issue with exact cell spacing by changing the values I enter so they no longer line up correctly (something to do with pixel or amount of a zero visible spacing?).

I don't have much experience but can a similar thing be achieved in the reports function in access? I have previously designed reports by hand but wondered if it the layout/format can be produced programmatically?

 

[The_Doc_Man]

Although it can be tedious to get it right, Access can ABSOLUTELY produce reports which would be printable on a pre-defined document layout. The problem you mentioned regarding Excel alignment is related to what happens with Access when it has to deal with things that don't quite fit. Access and Excel don't always do the same exact thing.

In Access, when faced with more text than will fit, either (a) you give your text box permission to expand (the specific property is called .CanGrow) or (b) you recognize that some kind of truncation will occur. The exact method of responding to piling 2 pounds of "stuff" into a 1 pound bag depends on how you define the text box's internal alignment properties (vertical center, left, right justify, and corresponding horizontal settings). It also depends on the font characteristics you use. Sometimes you can "cheat" and change something from, say, "Arial" to "Arial Narrow" or some similar adjustment. Other times, something just flat out won't fit.

Producing a layout programmatically can be difficult but there IS such a thing as adjusting the Height and Width of a textbox and asserting the position of the Top Left corner (which is the anchor point for most controls). In the report, each section has opportunities for Event Handler routines including an "OnFormat" routine (called before you print/display) that could adjust things. But this often falls flat unless you take into account that there will be round-off limitations.

An occasional "gotcha" is that you have to take into account that everything is in SECTIONS and that you need to watch these sections to be sure they don't "creep." If you have a page header section, or a group header sections, or a report header section, each of these occupies space. But you just want the detail section to be aligned. What you ask would be possible but very tedious to get right.

Note that you will be working in what Access calls "twips" which are length units similar to "points" that you see in terms of newspaper type. One inch is 1440 twips. If I recall correctly, one printed "point" is the same as 20 twips. That number was chosen because it allows adjustment to 1/32nd of an inch (45 twips) and if you recall, one inch is 72 points in print. This is an INTEGER that gives you the ability to space something to 1/1440th of an inch, but remember that doing something like 1/4th inch is easy; some fractions are NOT easy. Since factors for 1440 are 2, 3, and 5, you CAN express 1/3rd of an inch or 1/5th of an inch. But if you need exactly 1/7th, it will be rounded to the closest TWIP.

 

[Pat Hartman]

Looking at your array, it looks like you might want to use a table instead.

 

[Isaac]

I tried with excel but excel has an issue with exact cell spacing by changing the values I enter so they no longer line up correctly (something to do with pixel or amount of a zero visible spacing?)

Years ago I used Excel automation to print data points onto pre-defined templates for state government vehicular title Lien Releases. I agree with Doc_man that your biggest problem comes in the challenge of Excel not automatically fitting things - growing, shrinking, wrapping, etc. If there is any "one" technique that can be used to address this, it would probably be using Wrap Text, and then frequently utilizing Rows' & Columns' AutoFit method, which will overcome the problem to some extent, although you could still reach insurmountable challenges if you are trying to put "huge" amounts of text in cells. Combine those techniques with well-planned page breaks and a totally automated Page/Print Layout routine, and it might be enough to solve the problems.
In contrast, Access reports more easily overcome these problems. However, they come with plenty of baggage and complications of their own, so it is really a trade off - you might try a simple version on each platform and start to get a feel for the respective obstacles and solutions and difficulties.