Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Passing Two Dimensional Array to Function

First of all, I have some good news for you, our website: LEARN MS-ACCESS TIPS AND TRICKS has been selected by https://blog.feedspot.com panellist as one of the Top 5 Microsoft Access Blogs on the Web and awarded the Badge given below.

Microsoft Access Blogs

You can find some of the top ranked Blogs on a variety of subjects, RSS Feeds, Youtube Channels,  top News Websites and others.  Subscribe to Blogs/News Feeds, or topic of any other area of  interest, and get regular updates from www.blog.feedspot.com to your Inbox, as and when it happens, by providing your E-mail address.

Coming back to our VBA lessons, last week we have briefly touched the topic of passing Arrays as Parameter to Function ByRef method.  We were able to work with the singly dimensioned Array in it's original location within the called function, to sort the values in descending order.  For this example we have loaded the array with values for only five elements but the array can have many rows of values.

An Array can be re-dimensioned, for more rows or less rows later on in the program more than once, if we cannot determine the length of the array in advance. In that case you should not specify the number of elements in advance in the initial Dimension Statement.

Example:

'These Arrays cannot be redimensioned
'Array has predefined number of elements
.
Dim Products(1 to 5) as String
.
or
.
Dim Products(5) as String'The number of elements are predefined

'This Array can be redimensioned
'Number of elements required are not known in advance. 
.
Dim Products() as String
.
'ReDimension the Array for required number of elements
'Remember the array index numbers will be 0 to 4, total 5 elements
ReDim Products(5) As String
.
'later on in the program
'all the values assigned to first 5 elements will be lost.
.
ReDim Products(7) As String 
.
or
.
ReDim Products(Ubound(Products)+2) As String
.
'To preserve the values already assigned to first 5 elements
.
ReDim Preserve Products(7) As String

The important point to note here is that the ReDimensioning should take place in the calling program itself, if need arises in the called function, before passing the Array to the called program.  Even though the Array is passed to the called function ByRef and we are able to work with the passed variable directly, we cannot re-dimension the array to increase/decrease the number of elements in the array, from within the called function.

Now, we are aware of the limitation of the array when passed to the called function, we will try to pass a two dimensional array of Sales data to a function and print the values on the Debug Window.  The Sales data array have the following values in a record:

  1. Product Name - Text
  2. Quantity  - Intiger
  3. Unit Price – Double
  4. Total Value  - Double (will be calculated in the called function)

The sales record shows that the data fields have different data types.  To pass each field values separately to the called function we need four different array Variables with different data types (1. String type for Product Name, 2. Integer for Quantity, 3. Unit Price & 4. Total Price with Double precision number) and load each field data into separate variables.

We are going to do it differently here.  We will be using only one Variable to pass all four field values to the called function.  We will define a single Variable as a two dimensional Array of Variant data type, with four rows (each row represents a single record) and four columns (each column is a field).

We are not storing the above column names anywhere in the array and it is assumed that the first column is Product name, next column to the right of the first one is Quantity, next column Unit Price and the last column is Total Price.  Since, the Variant Data Type variable has a peculiar behavior that the data type of a cell changes automatically to match with the data type is assigned to it.

Before writing it as a complete function we will  look at the dimension statement and how the sales values are assigned into each element of the array. 

' variant Variable can hold different data types in each element 
Dim Product(4,4) as Variant 
'
Product(0, 0) = "Hard Disk": Product(0, 1) = 5: Product(0, 2) = 125.5: Product(0, 3) = 0

Product(1, 0) = "Keyboard": Product(1, 1) = 2: Product(1, 2) = 25.25: Product(1, 3) = 0

Product(2, 0) = "Mouse": Product(2, 1) = 3: Product(2, 2) = 13.75: Product(2, 3) = 0

Product(3, 0) = "DVD Writer": Product(3, 1) = 10: Product(3, 2) = 30: Product(3, 3) = 0

In the above example we have only four records (or four rows or lines of data) in the Table.  There are four fields (four columns) in each record.  Each cell is numbered with two numbers (row index number, column index number), separated by a comma.  Left-side number is the row index number of the column and the number to the right side of comma is the column index number.  Both number range is 0 to 3 (4 rows and 4 columns).  First column (column index 0) is Product Name, 2nd Column  (column index 1) Quantity, 3rd Column (index number 2) Unit Price and the last one is (index number 3) Total Value, which will be calculated and assigned later.

The entire array of these values can be passed to a function as ByRef parameter and we can work with the array directly from within the called function.  If you are new to two dimensional arrays it will be little confusing at first to comprehend the arrangement of values and as how to address each cell to work with it.   This become more difficult when there are calculations involving cells of the same row. 

We have a better way to deal with this problem with User-Defined Variables.  Yes, you heard me correctly we can define our own Variable Type,  besides the built-in variables with default data types.  We will explore this topic further in next week and I am sure you will be happier with this new idea, after struggling with these rows and columns set up.  Believe me this is a very powerful feature once you are familiar with these kind of data arrangements.  You can work with 5 rows, 500 rows or 5000 rows with the same statements in the function.

Public Function ProdList()
Dim Products(4, 4) As Variant
Dim j As Integer, k As Integer, stridx As String
' 0 = Description
' 1 = Quantity
' 2 = Unit Price
' 3 = Total Price to be calculated
'Array elements index numbers are 0 to 3
For j = 0 To 3
 For k = 0 To 3
    stridx = "(" & j & "," & k & ")"
    Select Case k
        Case 0
          Products(j, k) = InputBox("Product Name" & stridx)
        Case 1
          Products(j, k) = InputBox("Quantity" & stridx)
        Case 2
          Products(j, k) = InputBox("Unit Price" & stridx)
        Case 3
          Products(j, k) = 0 'total value will be calculated
    End Select
    Next k
Next j

Call ProdPrint(Products)

End Function

We have defined the Products variable as Variant data type with 4 rows, 4 columns for assigning values of different data types in them.   Next line is setting up three more variables: j & k as control variable for For…Next loops, variable stridx for building a string to display the index numbers of cells when displayed  in the InputBox() function Prompt text.

Two nested For…Next loops are set up to control the Variable index numbers of rows and column values.  The outer loop controls the row number and the inner loop with k control variable  is used for column index number.

Next we have used the Select Case…..End Select statements runs several other statements depending on value in the control variable j. If value in variable k=0 (and j=0) then the Inputbox() function runs below the Case 0 test and gets the Product Name and assigns it to Products(0,0) cell.  When k=1 then the InputBox() gets the value of Quantity and assigns it into Products(0,1) cell. When k=2 gets Unit Price and in the next step assigns Products(0,3)=0. The outer loop with control variable run only once with zero value as row index number.

This action repeats 3 more times  for the outer For…Next loop to control the row index number and each time the inner For…Next loop runs four times to control the column numbers to get values from the User for each cell for the row number in j control variable.

When control comes out of the loop the ProductPrint() Function is called by passing the Products variable as parameter to the function.

Public Function ProdPrint(List As Variant)
Dim j As Integer, k As Integer

'Ubound() function will get the
'total rows in the array - first value in the Dim statement
For j = 0 To UBound(List, 1) - 1
      List(j, 3) = List(j, 1) * List(j, 2)
    For k = 0 To UBound(List, 2) - 1 'get second value in Dim statement
        Debug.Print List(j, k),
    Next k: Debug.Print
Next j

End Function

The ProductPrint() function takes the Products Array's location address (ByRef) method. If you omit ByVal or ByRef key word before the Parameter variable it assumes that Variable List holds location reference of the Products (parameter passed ByRef).

As in the earlier program two integer variables j & k  are defined as control variables for outer and inner For…Next loops.  We need these For…Next loops to control the index numbers (rows & columns) to access each element of the array.  The starting value of the Loop is 0 but to calculate the end value we have used another function Ubound() (get Upper Boundary) value of the Array dimension.  In the first program we have written the control value as 0 to 3.  Here also we could do that but here we have used the Ubound() function to find the row and column numbers. This will calculate the Array size correctly, if the Array size is changed through ReDim statements.

Ubound(List, 1) gets the number of rows value, which is 4. But the row index numbers starts from 0 in memory so we have used index numbers 0 to 3 in the For…Next loop. The second value 1 in bracket of Ubound() function asks for the numbers of rows in the array.  Since, the row index number starts from 0 we are subtracting 1 from the number of rows value (4-1).

UBound(List, 2) gets the number of columns value.  The second parameter value is optional, if it is omitted it will only get the row value.  If the variable is a singly dimensioned array then the second value is never used.

The statement immediately after the first For…Next loop ‘List(j, 3) = List(j, 1) * List(j, 2)’ calculates the Total Price of each item and assigns it at the right most cell before printing the values of the Sales record item within the next For…Next loop, on the debug window.

The comma at the end of the Debug.Print statement positions the next item on the 14th column on the same line after the item printed earlier.

The empty Debug.Print statement, immediately after the inner Next statement without a comma at the end brings the print control back to the first column of the next line, positions correctly to start printing the next Sales Record.

If we place a semi-colon (;) at the end of the Debug.Print statement the print-head positions to the next character position, without leaving any space between the items printed.


Next week we will explore the User-Defined Variable with mixed data type. We can give appropriate names for each element of the array rather than assuming names as we did in the above examples. I am sure it will be very interesting comparing the difficulty we had in memorising each array elements logical names according to their array position.


Share:

8 comments:

  1. Thanks for adding the date to your posts :-)
    The n°1 in the top five blogs seems outdated as Access does not have pivot tables anymore http://www.accessrepairnrecovery.com/blog/complete-information-about-ms-access-pivot-table#more-64146

    ReplyDelete
  2. Thank you groveli for your observation on Pivot tables in Access. Even though I have references on this subject in my blog www.msaccesstips.com also the blog post you are referring to is not one of my post.

    ReplyDelete
  3. I know it isn't, I mentioned it because accessrepairnrecovery.com/blog is one of the five top blogs.

    This post about arrays is great, after exploring User-Defined Variable with mixed data type, could it be further refined through using classes?

    ReplyDelete
    Replies
    1. Yes. But, we cannot ignore the fact that lot of earlier version Access users exists. Microsoft brings out new version of Ms-Access every two years or so, after discarding many powerful features, like Security, inherent in earlier versions. Applications developed painstakingly under these versions and shared by several Users in a Network are not that easy to change all of them overnight and jump into the new version.

      Delete
  4. Classes have been used in Access since the 1997 version

    ReplyDelete
    Replies
    1. I am not mistaken about your deep knowledge in Ms-Access. In fact I came across Access Version 2.0 in 1996, when dBase, Foxbase were the popular DBMS at that time.

      Delete
    2. So will you produce an article about using classes with arrays?

      Delete
    3. Subscribe to the RSS Feed, you will get it as soon as it is out.

      Delete

Translate



PageRank
Your email address:

Delivered by FeedBurner

Search

Infolinks Text Ads

blog.feedspot.com Microsoft Access Blogs

Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.

Labels

Forms Functions How Tos MS-Access Security Reports msaccess forms Animations msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Queries External Links msaccess reports msaccess tips Menus and Toolbars Accesstips MsaccessLinks Process Controls Art Work Downloads msaccess How Tos Graph Charts msaccessQuery List Boxes Command Buttons Emails and Alerts Query Combo Boxes Custom Wizards DOS Commands ms-access functions msaccess functions msaccess graphs msaccess reporttricks Array msaccessprocess security advanced Access Security Custom Functions Data Macros Menus Property Report Top Values VBA msaccess email msaccess menus progressmeter Access2007 Auto-Number Calculation Command Button Copy Data Type Field Type Fields Form Join Microsoft Numbering System Records Security Split SubForm Table Utility Variables Workgroup database msaccess wizards Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Conditional Formatting Controls Data Filtering Defining Pages Diagram Disk Dynamic Lookup Error Handler Excel Export Expression External Filter Form Instances Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Methods Monitoring Object Reference Objects Octal Numbers Operating System Paste Primary-Key Product Rank Reading Recordset Rich Text Sequence SetFocus Summary Tab-Page Tables Time Difference Union Query User Users Water-Mark Word automatically commands function hyperlinks iSeries Date iif ms-access msaccess msaccess alerts pdf files reference restore switch text toolbar tutorial updating upload vba code

Featured Post

Passing Two Dimensional Array to Function

First of all, I have some good news for you, our website: LEARN MS-ACCESS TIPS AND TRICKS has been selected by https://blog.feedspot.com pa...

Labels

Blog Archive

Recent Posts