Auto-Numbers in Query Column Version-2

Auto-Numbers in Query Column Version 2.

In January 2010, I published an article, “Function: QrySeq() – Auto-Numbering in Query Column,” on this website. Over the years, it has been well received by readers. While reviewing the article again, I thought the Function could be rewritten with less code and improved performance than a variant Array.

When the QrySeq() function is called for a record in the query, the program searches the Array of Unique Keys for the key value passed from the record as a Parameter. Once it finds a matching key, it retrieves the corresponding sequence number from the Array element and returns it to the calling record.

If the query contains a large number of records, this process can take considerable time because the program begins the search from the start of the Array each time it looks for a key value.

The New Version is Named: QryAutoNum()

Using a Collection Object instead of an Array.

You can find a detailed discussion of the Collection Object on the MS Access and Collection Object Basics Page.

Here, we will have a brief introduction to the Collection Object, including what it is and how it is used in VBA. The Collection Object is a versatile Object that can generally hold any type of value, including numeric or string values, Class Module Objects, or a collection of other Objects. The Collection Object is instantiated in VBA programs as follows:

'declare a Collection Object.
Dim ABC as Collection
'create an instance of Collection Object in Memory
Set ABC = New Collection 

'We can Add built-in data types: Numeric, Strings etc 
‘or Objects like Class Module Objects, 
‘or other Collection Object as Items to the Collection Object.
'Use the Add method to add a Collection Item to the Object.

ABC.Add 25
ABC.Add "ms-accesstips"

'When Collection Object Items added this way, 
‘it can be retrieved only in the added order.

For j = 1 to ABC.Count 'gets the count of Items
	
Debug.Print ABC(J)’ retrieve in Item index Order.

Next

'When ADDed an Item with a String Key 
'we can use the Key value to retrieve the Items Randomly.
'But, usage of Key is optional.

ABC.Add 25, "1"
ABC.Add "ms-Accesstips", "2"

x = "2"

Debug.Print ABC(x)

Result: ms-accesstips

We will use the Collection Object to store the Query Auto-numbers, with the corresponding Unique Key Values assigned as the Collection Object Keys. With this approach, we can retrieve the Auto-numbers directly, eliminating the need to work with Arrays and their more complicated data storage and retrieval steps.

The QryAutoNum() Function Code.

Option Compare Database
Option Explicit

Dim C As Collection

Public Function QryAutoNum(ByVal KeyValue As Variant, ByVal KeyfldName As String, ByVal QryName As String) As Long
'-------------------------------------------------------------------
'Purpose: Create Sequence Numbers in Query Column Ver.-2
'Author : a.p.r. pillai
'Date : Dec. 2019
'All Rights Reserved by www.msaccesstips.com
'-------------------------------------------------------------------
'Parameter values
'-------------------------------------------------------------------
'1 : Column Value - must be UNIQUE Numeric/String Type Values from Query Column
'2 : Column Name  - the Field Name in Quotes from where Unique Values taken
'3 : Query Name   - Name of the Query this Function is Called from
'-------------------------------------------------------------------
'Limitations - Function must be called with Unique Field Values
'            - as First Parameter
'            - Need to Save the Query, if changes made, before opening
'            - in normal View.
'-------------------------------------------------------------------
Static K As Long, Y As Long, fld As String
On Error GoTo QryAutoNum_Err

Y = DCount("*", QryName) ' get count of records for control purpose

'If KeyfldName Param is different from saved name in variable: fld
'or Value in K more than count of records in Variable: Y
'then it assumes that the QryAutoNum() is called from a different Query
'or a repeat run of the same Query. In either case the Control Variable
'and Collection Object needs re-initializing.
If KeyfldName <> fld Or K > Y Then
'initialize Control Variable
'and Collection Object
    K = 0
    Set C = Nothing
    'save incoming KeyfldName
    fld = KeyfldName
End If

'if KeyValue parameter is Numeric Type then convert
'it to string type, Collection Object needs it's Key as String Type.
If IsNumeric(KeyValue) Then
    KeyValue = CStr(KeyValue)
End If

K = K + 1
If K = 1 Then
Dim j As Long, db As Database, rst As Recordset
Dim varKey As Variant

Set C = New Collection

Set db = CurrentDb
Set rst = db.OpenRecordset(QryName, dbOpenDynaset)

'Add recordlevel AutoNumber with Unique KeyValue
'to Collection Object, in AutoNumber, KeyValue Pair
While Not rst.BOF And Not rst.EOF
    j = j + 1 ' increment Auto Number
    
    'Get key value from record
    varKey = rst.Fields(KeyfldName).Value
    
    'if numeric key convert it to string
    If IsNumeric(varKey) Then
      varKey = CStr(varKey)
    End If
    
    'Add AutoNumber, KeyValue pair to Collection Object
    C.Add j, varKey
    
    rst.MoveNext
Wend
    rst.Close
    Set rst = Nothing
    Set db = Nothing

'Retrieve AutoNumber from Collection Object
'using the KeyValue.  Works like Primary Key of Table
    QryAutoNum = C(KeyValue)
Else
    QryAutoNum = C(KeyValue)
End If

If K = Y Then 'All record level AutoNumbers are Returned
    K = K + 1 ' increment control variable
End If

QryAutoNum_Exit:
Exit Function

QryAutoNum_Err:
MsgBox Err & " : " & Err.Description, , "QryAutoNum"
Resume QryAutoNum_Exit

End Function

Sample Source Query SQL.

With the Northwind Products Table.

SELECT Products.ID, 
Products.Category, 
Mid([Product Name],18) AS PName, 
Sum(Products.[Standard Cost]) AS StandardCost, 
QryAutoNum([ID],"ID",
"Product_AutoNumQ") AS QrySeq
FROM Products
GROUP BY Products.ID, Products.Category, Mid([Product Name],18)
ORDER BY Products.Category, Mid([Product Name],18);

Review of VBA Code Line-By-Line.

In the Global Area of the Module, we have declared a Collection Object, the Object Variable C.

The QryAutoNum() Function declaration is the same as our earlier QrySeq() with three parameters.

1. Unique Key-Value, either Numeric or String, as the first Parameter.
2. The Key-Value Field’s Name in String Format.
3. The Query Name in String Format.

The returned AutoNumber is in a Long Integer format.

Three Static Variables,  K and Y, are declared as Long Integers, and fld was declared as a String Variable.

All three Variables control the Code execution Paths, and determine when to initialize Collection objects and control variables.

The DCount() Function takes a count of records in the Query in Variable Y.

If the KeyFldName differs from the previously saved field name in the fld variable, the function assumes that the call originates from a new Query Record. If the field name is the same, but the value of the variable K is greater than Y, the function assumes that the earlier Query is calling QryAutoNum() again as part of a repeated run. In either case, the control variable K is reset to zero, and the Collection Object containing the existing Items is cleared from memory. The new Key field name from the KeyFldName variable is then saved in the fld variable for subsequent validation.

Next, if the KeyValue parameter is numeric, it is converted to String format using the statement KeyValue = CStr(KeyValue). The Item Key of the Collection Object must be in String format.

The variable K is then incremented by one. When K = 1, the function assumes that this is the first call to the function, originating from the first record of the Query. In this case, the main processing of the function begins.

The local temporary Variables are declared here, and their values are not preserved between calls of this function from different records of the query.

The Collection Object declared in the Standard Module’s  Global area is instantiated in memory with the statement Set C = New Collection.

The Query recordset is opened to read records. The local variable J will create Auto-numbers and add them to the Collection Object for each record.  The Unique Key-Value, read from the recordset into variable varKey, is added to the Collection Object as its Key Value.

If the varKey variable value is Numeric Type, then it is converted to String format.

The Auto-Number Value in Variable J and the string value in variable varKey are added to the Collection Object in the following statement, as its Item value, Key pairs:

C.Add J, varKey

This process is repeated for all the records in the Query.  The Auto-Numbers are generated for all records and added to the Collection Object.  All this work is done during the first call of the function from the first record of the query.

Did you notice that we are reading the Unique Key value of each record directly from the record set within the While . . . Wend Loop to add them to the Collection Object?  After adding the Auto-Numbers for all records, the record set and Database Objects are closed.

Remember, we are still on the first call of the function from the first record of the query, and the first parameter variable KeyValue still holds the first record Key Value.

The next statement QryAutoNum = C(KeyValue) retrieves the Collection Object’s first Item Auto-Number Value 1, using the Unique Key Value in parameter variable KeyValue, and returns it to the function calling record. This will happen only once because the variable K will be greater than one on subsequent calls of this function.

Function calls from the second record onward take the Else path of the If K = 1 Then statement. They retrieve the AutoNumber from the Collection Object using the KeyValue passed as a parameter and return it to the corresponding record in the Query.

This process is very fast because the required Item can be retrieved directly using the Collection Object Key, rather than searching through the Array from the beginning each time to locate the required Key.

When the Auto-number values for all records have been returned, the value of the control variable K equals Y. The record count of the Query was obtained and stored in the variable Y at the beginning of the program. At this point, K is incremented by 1, making its value greater than Y. Because K and Y are Static Variables, their values are retained in memory after the last record call has completed. If the same Query is run a second time, these variable values can be tested to determine whether the variables need to be reset and the existing Collection Object cleared from memory, allowing the entire process to start afresh.

If the QryAutoNum() function is called from the same Query again, the Static Variables and Collection Object are cleared from memory, preparing for a fresh run of the Function for the same Query or for a different Query.

The sample Report image, generated using the above Query, is shown below for reference. 

You can use the Query as the source for a Report or Form. 

A sample demo database is attached. 

Download AutoNumQuery2007.zip

1. Auto-Numbering in Query Column
2. Product Group Sequence with Auto-Numbers.
3. Preparing Rank List.
4. Auto-Number with Date and Sequence Number.
5. Auto-Number with Date and Sequence Number-2.

Read More

Diminishing Balance Calc in Query

Diminishing Balance Calc in Query.

This is an offshoot of the earlier Function Running-Sum in Query Column.  With a few changes to the earlier Function RunningSum(), we can easily calculate the loan balance-to-pay amount after deducting the monthly amount paid at each record level.

The loan amount is payable in monthly installments.   Our simple task is to show the diminishing balance of the loan amount against each record-level installment amount in a separate Column of the Query.  The last record will have the remaining balance amount for payment.

Let us pretend that the Loan Repayable Total Amount is 1000.

Sample Query Recordset.

The sample installment payment detail records are taken from the earlier Post: Running-Sum in Query Column, as given below.

The Query SQL that calls the DiminishingBal() Function.

The SELECT Query SQL that calls the DiminishingBal() Function, in a separate Column of the Query.

SELECT Table_Units.ID, Table_Units.Units, DiminishingBal([ID],"ID","Units","DiminishingQ1") AS DiminishingBalance
FROM Table_Units;

The Query Recordset Image, with the result in the last column,  is given below:

We are using the same Query recordset as the source for the earlier RunningSum() Function here for demonstration purposes.  The Recordset should have a Unique value (Numeric or String) field and be used as the first parameter to the Function.

The Total Repayable Loan Amount is kept in a separate Table.

The Total Amount to be repaid to the Bank (1000) is kept in a separate Table with the following structure:

The DiminishingBal() Function VBA Code.

The VBA Code of the DiminishingBal() Function is given below:

Option Compare Database
Option Explicit

'Declare a Generic Object
Dim D As Object

Public Function DiminishingBal(ByVal IKey As Variant, ByVal KeyFldName As String, ByVal SumFldName As String, ByVal QryName As String) As Double
'-----------------------------------------------------------
'Function: DiminishingBal()
'Purpose : Calculate Diminishing Balance in a separate Column
'The Query can be used as source for other Processing needs,
'for Form View or Report
'-----------------------------------------------------------
'Author  : a.p.r. pillai
'Date    : December 2019
'Rights  : All Rights Reserved by www.msaccesstips.com
'-----------------------------------------------------------
'Parameter List, in the Order of it's placement
'1. Key Value Data Field
'2. Key-Field Name in String Format
'3. Field-Name for Calcuating Running Sum in String Format
'4  Query-Name in String Format
'-----------------------------------------------------------
'Remarks: The Key-Value Field should have Unique Numeric or
'String Values.
'-----------------------------------------------------------
Static K As Long, X As Double, fld As String, y As Long
Dim p As Variant

On Error GoTo DiminishingBal_Err

y = DCount("*", QryName)
'If the Function is not called by the same Query
'then initialize Dictionary Object and Variables
If SumFldName <> fld Or K > y Then
   fld = SumFldName
   Set D = Nothing
   K = 0
   X = 0
End If


K = K + 1
If K = 1 Then 'The major process of the function starts here
    Dim DB As Database, rst As Recordset
    
    'Create and instantiate the Dictionary Object
    Set D = CreateObject("Scripting.Dictionary")
    
    'Get Loan Repayable Amount
    X = DLookup("[LoanAmt]", "tblRepay", "[id] = 1")
    
    'Open the EMI Recordset
    Set DB = CurrentDb
    Set rst = DB.OpenRecordset(QryName, dbOpenDynaset)
    'Calculate cumulative record-level summary and
    'add the value into Dictionary Object as it's Item
    While Not rst.EOF And Not rst.BOF
    'read the record summary field value and add it to total
         X = X - rst.Fields(SumFldName).Value
    'read current record key field value
         p = rst.Fields(KeyFldName).Value
    'add the total value to dictionay object
    'as Key, Item pair
         D.Add p, X
    ' repeat this process for all records
         rst.MoveNext
    Wend
    
    'close recordset and remove the database objects
    rst.Close
    Set rst = Nothing
    Set DB = Nothing
    
    'Retrieve the first item from Dictionary,
    'using the first Key passed as parameter,
    'and return to the function calling record in the Query
    
   DiminishingBal = D(IKey)
Else
   'Subsequent calls with the record Key passed as parameter
   'will retrieve other record values from Dictionary and
   'returns to their corresponding records in the Query.
   
   DiminishingBal = D(IKey)
End If

'A control forcing to initialize the static variables
'when the program is rerun for the same query.
   If K = y Then
      K = K + 1
   End If

DiminishingBal_Exit:
Exit Function

DiminishingBal_Err:
MsgBox Err & ":" & Err.Description, vbOKOnly, "DiminishingBal()"
Resume DiminishingBal_Exit
End Function

How the Function Works.

In the Global declaration area of the VBA Module, Variable D is declared as an Object.

The DiminishingBal() Function needs four parameters:

1. A Unique Value Field (Numeric or String Values) is the first parameter.  The parameter is declared as a Variant data Type.
2. The Unique Value’s Field Name is the second parameter in String format.
3. The Loan Installment Value Field Name.
4. The Query Name is the fourth Parameter.

Four Static Variables K, X, fld, and y are declared.  They must be Static Variables to retain their values between repeated calls of the Function, from each record of the Query.  The Variable p is declared as a Variant Type normal variable to hold the Key-Value (either Numeric or String) of each record.

The DCount() Function takes the Query record count in Variable y. The Value in this Variable is used as a control to check when to reset the Static Variable Values to their initial Values and to remove the Dictionary Object from memory.  This control is necessary if the same Query is run more than once, consecutively.

1. If the control variable K value is more than the Query record count variable y, then the Static variables are reset to their initial values and the Dictionary Object is deleted from memory.
2. Or, if the installment value Field Name is different from the field name saved in Variable fld during the last call of the function,  then it assumes that the Function is called from a different Query Column and resets the Static Variable Values. The Dictionary object is deleted from memory.

Next, Variable K is incremented by 1.  When K=1, the main action of the Function starts.  The Database and Recordset Objects are declared in the DB and the rst variables, respectively.

In the next executable statement, Set D = CreateObject("Scripting.Dictionary") creates the Dictionary Object with the CreateObject() method and assigns it to the Object variable D, which was declared in the Global Area of the Module.

There are other ways to declare and use this Object by adding the Microsoft Scripting Runtime File to the Microsoft Access Reference Library List.  After that, you can create an instance of the Dictionary Object in the following way:

Dim D As Dictionary
Set D = New Dictionary

If you are new to the Dictionary Object and its usage, visit the Post: Dictionary Object Basics.  There are five Posts on this Topic, and you can find the links at the end of this Page.

Next, we need the value of the repayable total Loan Amount and retrieve it from its Table tblRepay with the dlookup() function in the statement: X = DLookup("[LoanAmt]", "tblRepay", "[id] = 1").  There is only one record in the Table with ID 1, so omit the criteria part.

The Query Recordset is open to read records one by one. The first record’s amount paid to the bank is deducted from the Loan Amount (1000) in Variable X.  The Unique Key value of the record is retrieved and saved in Variable p in the next statement.

The balance loan amount, calculated after deducting the repaid Amount, is added to the Dictionary Object with the Dictionary Object’s  Add Method as its Item Value, with the Unique Key field value in the variable p as Dictionary-Key in the statement: D.Add p, X. The Dictionary’s Add method always adds its Item value in Key, Item pairs.

Note: If the Key-Value is not unique, the Add method fails with Errors.

With the rst.MoveNext statement moves to the next record and adds the result value to the Dictionary Object.

This way, the individual record value is deducted from the remaining loan balance amount at that level and added to the Dictionary Object as its Item.

Note: Here, you may ask why the Dictionary Object is chosen to hold all the calculated values rather than in an Array.  Yes, it can be done, but that method needs more statements to store and retrieve the values in a two-dimensional array. It will become more complicated when the Query Record’s Unique Key Value is in String form.  The Dictionary Object allows you to retrieve the value in either sequential or random order based on its Key.  Here, the Random method works fine with the Key-Value Type in the Numeric or String form.

When all the record processing is complete, the record set and Database Objects are closed.

What you have to keep in mind at this point is that the value in variable K is still 1, and the first Parameter IKey retains the unique ID value.  At the first record-level function call, DiminishingBal() calculated all record-level balance loan amounts and added them as Dictionary Object Items. The function parameter IKey still holds the first record’s Unique ID value. That is why we have used a separate variable p for the individual record key during the processing of all records.

So, the entire record-level processing is done during the first call of the function, initiated from the first Query record, and all record-level result values are held in temporary storage in the Dictionary Object.

The next statement DiminishingBal = D(IKey) retrieves the first value, adds it to the Dictionary using the unique parameter value IKey,  and returns the value to the calling first record of the Query.

The next call from the second record of the Query increments the variable K by 1 (now K=2), and the program takes the ELSE path of the IF...Then the statement retrieves the second Item value from the Dictionary Object, using the IKey parameter, and returns it to the corresponding Query record.

The rest of the DiminishingBal() function call, from the remaining records, will route the program control only through the ELSE path because the value in Variable K is greater than one, retrieves the values from the Dictionary Item, and returns it to the calling record.

The Next If . . . Then statement checks whether the value in variable K = y.  Variable y holds the total record count of the Query.  If it is found to be True, then it assumes that the last call of the DiminishingBal() function has arrived.  At this point, the K variable is made greater than the control variable y.

This is necessary to initialize the Static Variables during the Query rerun. If any change is made to the Source Data before the rerun, it will not be reflected in the balance amount calculated earlier, because it will keep taking the ELSE route in the If ... Then statement and retrieve the old value from the Dictionary Object.

The Demo Database, with all the necessary Objects and the Code Module, is attached for your convenience to download and try it out straight away.

Download DiminishingBalance.zip
Download DiminishingBalance2003.zip




Dictionary Object Links.

1. Dictionary Objects Basics
2. Dictionary Object Basics- 2
3. Sorting Dictionary Object Keys and Items
4. Display Records from Dictionary
5. Add Class Objects as Dictionary Items
6. Update Class Object Dictionary Item

Read More

Running Sum in MS-Access Query

Running Sum in an MS Access Query.

We are familiar with creating Auto-Numbers in a Query Column through an earlier User-Defined Function Named QrySeq(), published on this Website with the Post Title: auto-numbering Query Column, in January 2010. Hope you come across that Post; if not, you may visit the Page by following the link above.

The RunningSum() Function is written using similar logic and matches a few QrySeq() Function Parameters.

Before going into details, let us take a look at some sample images, before and after a run of the new Function in a test Query Column.

A small Table with two Fields: Table_Units and a few records.

The SELECT Query: RunningSumQ1 record set in datasheet view, with summary values in a separate column, with the column name RunningSum, from which the RunningSum() Function is called.

The SQL Code of the RunningSumQ1 Query.

SELECT Table_Units.ID, Table_Units.Units, RunningSum([ID],"ID","Units","RunningSumQ1") AS RunningSum
FROM Table_Units;

A Report Designed using RunningSumQ1:

The Query Preparation Note.

Before diving deep into the VBA code, I want you to check the data preparation procedure for the RunningSum() Function.

1. A unique ID Field, like a primary key, is required in the Query, with either Numeric or String Data, and ensure there are no duplicates.
2. If this is not readily available in the Source-Data, you may join (concatenate) two or more field values (any type) together to create unique values in a separate column, as a Key Field in the Query.
3. If this method is followed, then create a Test Query similar to the sample one below, using the first Query as the source, to find out whether any duplicates still exist in the Source Query or not.

Sample ID Field Record-Count Test Query:

SELECT RunningSumQ2.ID2, Count(RunningSumQ2.ID2) AS CountOfID2
FROM RunningSumQ2
GROUP BY RunningSumQ2.ID2;

The CountOfID2 Column result value should be 1.



4. When all the ID Field values are unique, the CountOfID2 Column will have a value of 1 in all records.  A value greater than one in any record means that those records have duplicate keys and need to join some other field to eliminate duplicates.
5. Once you are sure that all records have unique IDs, you may add other required fields to the first Query for the purpose you plan to use, Form or Report Source.
6. Once you are ready with the Query data, then it is time to add the function in a new Column in the Query, like Summary: RunningSum([ID], ”ID”, ”Units”, ”MyQuery”).

The RunningSum() Function VBA Code.

Option Compare Database
Option Explicit

Dim D As Object

Public Function RunningSum(ByVal IKey As Variant, ByVal KeyFldName As String, ByVal SumFldName As String, ByVal QryName As String) As Double
'-----------------------------------------------------------
 'Function: RunningSum()
 'Purpose : Creates Running-Sum Value of a Field.
 'The Query can be used as Source for other Processing needs.
 '-----------------------------------------------------------
 'Author  : a.p.r. pillai
 'Date    : November 2019
 'Rights  : All Rights Reserved by www.msaccesstips.com
 '-----------------------------------------------------------
 'Parameter List, in the Order of it's placement
 '1. Key Value Data Field
 '2. Key-Field Name in String Format
 '3. Field-Name for Calculating Running Sum in String Format
 '4  Query-Name in String Format
 '-----------------------------------------------------------
 'Remarks: The Key-Value Field should have Unique Numeric or
 'String Values.
 '-----------------------------------------------------------
Static K As Long, X As Double, fld As String, y as Long
Dim p As Variant

On Error GoTo RunningSum_Err

y = DCount(“*”,QryName)

If SumFldName <> fld OR K > y Then
    fld = SumFldName
    Set D = Nothing
    K = 0
    X=0
 End If

K = K + 1
 If K = 1 Then
     Dim DB As Database, rst As Recordset

    Set D = CreateObject("Scripting.Dictionary")
 
    Set DB = CurrentDb
    Set rst = DB.OpenRecordset(QryName, dbOpenDynaset)
     
    While Not rst.EOF And Not rst.BOF
          X = X + rst.Fields(SumFldName).Value
          p = rst.Fields(KeyFldName).Value
          
          D.Add p, X
          
          rst.MoveNext
     Wend
     
     rst.Close
     Set rst = Nothing
     Set DB = Nothing
     
     RunningSum = D(IKey)
 Else
    RunningSum = D(IKey)
 End If

If K = y then
   K = K + 1
End If

RunningSum_Exit:
Exit Function

RunningSum_Err:
MsgBox Err & ":" & Err.Description, vbOKOnly, "RunningSum()"
Resume RunningSum_Exit
End Function

VBA Code Line by Line.

In the Global area of the Standard Module, an Object Variable is declared with the name D. 

The function RunningSum() is declared with four parameters.

1. The Unique Key Field Value.
2. The Key-Field Name in String format.
3. The Summary Field Name in String format.
4. The Query-Name in String format.

The returned value from the function is a double-precision number.

Four Static Variables are declared:

1. K – is a control variable.
2. X – to hold the Summary Values, added to it at a record level.
3. fld – is the control variable to keep the Summary Field Name as a flag to ensure that the function runs for the same Query.

The Static Variables will retain their values during repeated calls of the Function.

Variable p is to hold the IDKey value retrieved from the record.  It is declared as a Variant Type to accept either Numeric or String Key Values.

The Working Logic of the Function.

The statement If SumFldName <> fld Then checks whether the Key-Field name passed to the function is different from the last call of the Function.  If it is different, then it assumes that a different Query is passed to the function.

The Dictionary Object D is erased from memory, and other variables are initialized.

In the next step, the K Variable is incremented by one. When K=1, the function’s main task is initiated.  

The Database and Recordset Objects are declared.

The D Object variable is instantiated as a new Dictionary Object, with the Object creation statement: Set D = CreateObject(“Scripting.Dictionary”).

By default, the Dictionary Object Reference is not added to the list of Microsoft Access Library Files. If you add it manually, you can declare and instantiate a Dictionary Object, like the Class Module or Collection Object.

Note: If you are not familiar with the Dictionary, Class, or Collection Object, then we have all the information you need to learn the Fundamentals on this website.  The links are given at the end of this page. You may visit them to learn using sample code and Demo databases, as working models to download.

Adding Dictionary Object Reference File.

To add the Dictionary Object to your Database’s Library Files List, do the following:

On the VBA Window, select Tools -- > References… and look for the file: Microsoft Scripting Runtime in the displayed list, and put the check mark to select it.

Once you do this, you can declare and instantiate a Dictionary Object as given below.

Dim D As Dictionary
Set D = New Dictionary

If you do this, you have the added advantage of displaying its Properties and Methods when you type a dot (.) after its Object name, by IntelliSense.

Next, the database object DB is set to the active database, and the Query is opened at a record set in the rst object.

Within the  While. . .Wend Loop, the summary field and the unique key Field values are read from each record. The Summary field value is added to Variable X.  The Key value of the record is written as a Dictionary Object Key-Value, and the current Value in X is written as a Dictionary Object Item, as a Key-Item pair.

The Dictionary Object Items are always written in this way.  The Item can be a single value, an Array object, or a collection of objects. All of them should have a Unique Key Value to retrieve the Item values later.

The purpose of the Key in a Dictionary Object is similar to the function of the Primary Key in a Table.  We can retrieve any value, randomly or sequentially, from the Dictionary Object using the Key: A = D(Key) or  A = D.Item(Key).

In this way, the cumulative summary value, at each record level, is added to the Dictionary Object as its Item, with a unique Key. When all record-level processing is complete, the first record summary field value is returned to the function-calling record by executing the RunningSum = D(IKey) statement, from the first Dictionary Item.  All the above actions take place while the control-variable K=1.  

Subsequent function calls with the Key-Value parameter of each record retrieve the corresponding summary record value from the Dictionary Item and return it to the Query Column; that’s how it works.

Some Images of the sample run, done in the NorthWind Products table, are given below.

Sample Query Run (Key Values are String Type) Data on Form.

SELECT Trim(Str([ID])) & [Product Code] AS ID2, Products.[Product Code], Products.[Product Name], Products.[List Price], RunningSum([ID2],"ID2","[List Price]","RunningSumQ2") AS RunningSum
FROM Products;

The RunningSumQ2 Query is the Record Source of the Form.

The Sample Run Data of the Report.

The RunningSumQ2 Query is the Record Source of the Report.

Download the Demo Database.

Download ClsEmployees2007.zip

Download ClsEmployees2003.zip

CLASS MODULE

1. MS-Access Class Module and VBA
   
2. MS-Access VBA Class Object and Arrays
   
3. MS-Access Base Class and Derived Objects
   
4. VBA-Base Class and Derived Object-2
   
5. Base Class and Derived Object Variants
   
6. MS-Access Recordset and Class Module
   
7. Access Class Module and Wrapper Classes
   
8. Wrapper Class Functionality

COLLECTION OBJECT

1. MS-Access and Collection Object Basics
   
2. MS-Access Class Module and Collection Objects
   
3. Table Records in Collection Object

DICTIONARY OBJECT

1. Dictionary Objects Basics
   
2. Dictionary Object Basics-2
   
3. Sorting Dictionary Object Keys and Items
   
4. Display Records from Dictionary
   
5. Add Class Objects as Dictionary Items
   
6. Update Class Object Dictionary Item

MS-ACCESS EVENT HANDLING

1. Withevents MS-Access Class Module
   
2. Withevents and Defining Your Own Events
   
3. Withevents Combo List Textbox Tab
   
4. Access Form Control Arrays and Events
   
5. Access Form Control Arrays And Event-2
   
6. Access Form Control Arrays And Event-3
   
7. Withevents in Class Module for Sub-Form
   
8. Withevents in Class Module and Data
   
9. Withevents and Access Report Event Sink
   
10. Withevents and Report Line Hiding
    
11. Withevents and Report-line Highlighting
    
12. Withevents Texbox and Command Button
    
13. Withevents Textbox Command Button
    
14. Withevents and All Form Control Types

Read More

Date2Text and Text2Date Functions

Date2Text and Text2Date Functions.

We already have some frequently used Report Footer formatting simple functions, like Report Page Number formatting function =PageNo([page],[pages]),  output: Page: 1/20 –> Page: 20/20, Report Period Function =Period([StartDate], [EndDate]), output: Period: 15/09/2007 To 30/09/2007 and =Dated()  Function, output: Dated: 15/09/2007 on Report Footer.  Even though they are simple Report Header/Footer formatting functions, they save time on report design.  Check the following Links, if you have not yet come across those Functions earlier:

Useful Report Functions.

Continued on Page 2/- an indicator Label on the report page footer, on multi-page reports.

Our new Function formats the Date Value in the following sample Text form:

  Sunday, 27th October 2019.

The Date2Text() Function Code.

Public Function Date2Text(ByVal dt As Date) As String
Dim txt As String, num As Integer

num = Day(dt)

   Select Case num
       Case 1, 21, 31
          txt = "st "
       Case 2, 22
          txt = "nd "
       Case 3, 23
          txt = "rd "
       Case 4 To 20, 24 To 30
          txt = "th "
   End Select
   
   Date2Text = WeekdayName(Weekday(dt)) & "," & Day(dt) & txt & MonthName(Month(dt)) & " " & Year(dt)
   
End Function

Copy and paste the Code above into the Standard VBA Module, save the code, and compile it.

Let us try out the Code directly from the Debug Window. Press Ctrl+G to display the Debug Window if it is not already visible on the VBA editing Window.

Sample Test Runs.

D = #27-10-2019#

? Date2Text(D)
Result: Sunday, 27th October 2019

D=Cdate("22/10/2019")

? Date2Text(D)
Result: Tuesday, 22nd October 2019

D=DateValue("11/10/2019")

? Date2Text(D)
Result: Friday, 11th October 2019

? Date2Text(Date)
Result: Thursday, 31st October 2019

Weekday 1 to 7 is Sunday to Saturday. This depends on your Computer's Regional Settings. If it is not correct in your case, then change it in the Regional Settings on your Computer.

Scope of this Function.

The Date2Text() Function can be placed in a TextBox on the Report Header, used on a Date-Field Query Column, or on the Main Form (Main Switchboard or Control Screen, etc.) as general info.

The Text2Date() Function.

The Date2Text() Function’s complementary function, Text2Date() VBA Code is given below.

Public Function Text2Date(ByVal txtDate As String) As Date
Dim S, dt As String
    
    S = Split(txtDate, " ")
    dt = Str(Val(S(1))) & "-" & S(2) & "-" & S(3)
    Text2Date = DateValue(dt)

End Function

The Date converted to Text form can be changed back to a valid date format with the Text2Date() Function. There is no validation check performed, and it expects the parameter value to be in the correct input format, similar to the Date2Text() Function.

Example:

? Text2Date("Thursday, 31st October 2019")

Result: 31-10-2019

Caution: If the parameter value is entered manually, then there should not be more than one space between each segment of the date text.

1. Days in Month Function
2. Custom Calculator and Eval Function
3. Rounding Function MRound() of Excel
4. Proper Function of Excel

Read More

ROUNDDOWN Function of Excel

'RoundDown' Excel Function.

We introduced the Excel ROUNDUP(Number, num_digits) Function in Microsoft Access earlier.  It works like the ROUND() Function except that the number is always rounded up. 

Exmple-1: Greater than >0  (>0): ROUNDUP(3.14159, 3) = 3.142

Example-2: Equal to 0 (=0): ROUNDUP(3.2,0) = 4

Example-3: Less than <0 (<0):  ROUNDUP(31415, -2) = 31500

Visit the following link for more details on the ROUNDUP () Function:

The ROUNDUP() Function of Excel in Access.

The ROUNDDOWN() Function of Excel does the opposite of the ROUNDUP() Function.  When the number of digits specified is greater than 0 (zero), then the number is rounded down to the specified number of Decimal Places.  If the number of digits specified is 0 (zero), then the number is rounded down to the nearest integer.

Syntax:

ROUNDDOWN(Number, num_digits)

Number: Required,  Any Real Number.

num_digits: Required, Number of Digits the Number to Round Down to.

Positive Number (>0): ROUNDDOWN(3.149, 2) =  3.14

Zero (0): ROUNDDOWN(8.9, 0) = 8

Negative Number (< 0): ROUNDDOWN(258, -1) = 250

Public Function ROUNDDOWN(ByVal Num As Double, ByVal num_digits As Integer) As Double
'-------------------------------------------------
'ROUNDDOWN() Function of Excel Redefined in MS-Access
'Author: apr pillai
'Date  : Sept 2019
'Rights: All Rights Reserved by www.msaccesstips.com
'-------------------------------------------------

Dim S1 As Integer, S2 As Integer

On Error GoTo ROUNDDOWN_Err
S1 = Sgn(Num)
S2 = Sgn(num_digits)

Select Case S1
    Case 0
        ROUNDDOWN = 0
        Exit Function
    Case 1
    Select Case S2
        Case 0
            ROUNDDOWN = Int(Num) * S1
        Case 1
            ROUNDDOWN = (Int(Num * (10 ^ num_digits)) / 10 ^ num_digits) * S1
        Case -1
            num_digits = Abs(num_digits)
            ROUNDDOWN = Int(Num / (10 ^ num_digits)) * 10 ^ (num_digits) * S1
    End Select
    Case -1
    Select Case S2
        Case 0
            ROUNDDOWN = Int(Abs(Num)) * S1
        Case 1
            ROUNDDOWN = (Int(Abs(Num) * (10 ^ num_digits)) / 10 ^ num_digits) * S1
        Case -1
            num_digits = Abs(num_digits)
            ROUNDDOWN = (Int(Abs(Num) / (10 ^ num_digits)) * 10 ^ num_digits) * S2
    End Select
End Select

ROUNDDOWN_Exit:
Exit Function

ROUNDDOWN_Err:
MsgBox Err & " : " & Err.Description, , "ROUNDDOWN()"
Resume ROUNDDOWN_Exit
End Function

The ROUNDDOWN() Function is not field-tested for accuracy; use it at your own risk.

The Function is developed based on the sample output given in the Microsoft Help Document. The Microsoft Excel Help Document extract is reproduced below for your information.

Formula	Description	Result
=ROUNDDOWN(3.2, 0)	Rounds 3.2 down to zero decimal places.	3
=ROUNDDOWN(76.9,0)	Rounds 76.9 down to zero decimal places.	76
=ROUNDDOWN(3.14159, 3)	Rounds 3.14159 down to three decimal places.	3.141
=ROUNDDOWN(-3.14159, 1)	Rounds -3.14159 down to one decimal place.	3.1
=ROUNDDOWN(31415.92654, -2)	Rounds 31415.92654 down to 2 decimal places to the left of the decimal point.	31400

1. ROUNDUP Function of Excel in MS Access
2. Proper Function of Excel in Microsoft Access
3. Appending Data from Excel to Access
4. Writing Excel Data Directly into Access
5. Printing MS-Access Report from Excel
6. Copy and Paste Data From Excel to Access2007
7. Microsoft Excel Power in MS-Access
8. Rounding Function MROUND of Excel
9. MS-Access Live Data in Excel
10. Access Live Data in Excel-2
11. Opening Excel Database Directly
12. Create Excel Word File from Access

Read More

Passing Two Dimensional Array to Function

Passing a Two-Dimensional Array Function.

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

You can find some of the top-ranked blogs on a variety of subjects, RSS feeds, YouTube channels, top news websites, and other sources. Subscribe to blogs, news feeds, or topics in any other area of interest, and receive regular updates from www.blog.feedspot.com in your Inbox as they become available by providing your email address.

Returning to our VBA lessons, last week we briefly discussed passing arrays as parameters to a Function using the ByRef method. This allowed us to work directly with the original array inside the called function and sort its values in descending order.

In our example, we used array values for only five elements, but in practice, an array can hold many rows and columns of data.

The Re-Dimension (ReDim) Statement.

An array can be re-dimensioned multiple times during program execution to increase or decrease the number of rows if its size cannot be determined in advance. In such cases, you should omit the element count in the initial Dim statement.

Example:

'Cannot Re-dimension pre-defined Arrays
.
Dim Products(1 to 5) as String
.
or
.
Dim Products(5) as String'The number of elements are predefined

'Re-dimension this Array later for required  
'Number of elements, not known in advance. 
.
Dim Products() as String
.
'Re-Dimension 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
'
'OR
'In this case Array Index Number Range 1 to 5
ReDim Product(1 to 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

Note: The important point is that the array re-dimensioning should be done in the calling procedure itself—before passing it to the called function if a size change is required. Although arrays are passed by reference and the called function can work directly with the passed variable, it cannot re-dimension the array to increase or decrease its number of elements from within the called function.

Two-Dimensional Array as Function Argument.

Now that we are aware of the limitations of passing arrays to a called function, let’s try passing a two-dimensional array containing Sales data to a function and printing its values in the Debug Window. Each record in the Sales data array will contain the following values:

1. Product Name - Text

2. Quantity  - Integer

3. Unit Price – Double

4. Total Value  - Double (will be calculated in the called function)

The sales records contain fields with different data types. Normally, to pass these values individually to a called function, we would need four separate array variables with different data types — for example:

1. String for Product Name

2. Integer for Quantity

3. Double for Unit Price

4. Double for Total Price

Each field’s values would be stored in its own array variable and passed separately to the function.

However, we will do this differently. Instead of using four separate arrays, we will use a single variable: a two-dimensional array of the Variant data type, with four rows (each row representing one sales record) and four columns (each column representing a field). This way, we can pass all four fields of each record together as a single variable to the called function.

The Variant Data Type.

We are not storing the column names anywhere within the array. It is simply assumed that:

• The first column holds the Product Name

• The second column holds the Quantity

• The third column holds the Unit Price

• The fourth column holds the Total Price

Since we are using a Variant data type for the array, each array element can hold different data types. A Variant variable automatically adapts its data type to match the type of value assigned to it. This flexibility allows us to store text, integers, and floating-point numbers together in the same array.

Before writing the complete function, let’s first examine how the Dim statement defines the two-dimensional array and how the sales values are assigned to each array element.

' 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 (rows) in our table, and each record contains four fields (columns). Every cell in this two-dimensional array is identified by a pair of numbers—the row index and the column index—separated by a comma. The index number on the left represents the row index, and the number on the right represents the column index. Since our array has 4 rows and 4 columns, both index ranges go from 0 to 3.

Here is how the columns are arranged:

• Column 0: Product Name

• Column 1: Quantity

• Column 2: Unit Price

• Column 3: Total Value (to be calculated and assigned later)

We can pass the entire array to a function as a ByRef parameter, allowing the function to work directly with the original data.

If you are new to two-dimensional arrays, it can feel a bit confusing at first to understand how the values are arranged and how to refer to each cell. This becomes even trickier when performing calculations across multiple cells within the same row.

Fortunately, there is a better way to handle this complexity—by using User-Defined Variables. Yes, you can actually define your own variable type, in addition to the built-in types provided by VBA.

We will explore this concept in detail next week. Once you get familiar with it, you’ll find it much easier and more intuitive than juggling rows and columns. The best part is that this method scales effortlessly—you can work with 5 rows, 500 rows, or even 5000 rows using the same statements in your function.

Create the Product List Data.

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

VBA Code Line by Line

We have defined the Products variable as a Variant data type with 4 rows and 4 columns, so it can hold values of different data types in each cell.

In the next line, we declare three more variables:

• j and k as control variables for the For…Next loops

• strIdx as a String variable for building text to display the index numbers of each cell in the InputBox() prompt.

We then set up two nested 'For … Next' loops to control the index row and column numbers. The outer loop (j) controls the row index, while the inner loop (k) controls the column index.

Inside the inner loop, we use a Select Case…End Select structure to determine which field is being processed based on the current value of k:

• When k = 0, the InputBox() function prompts for the Product Name and assigns it to Products(j, 0).

• When k = 1, it prompts for the Quantity and assigns the value to Products(j, 1).

• When k = 2, it prompts for the Unit Price and assigns it to Products(j, 2).

• When k = 3, it initializes Products(j, 3) = 0 (this will later hold the calculated Total Price).

The outer loop repeats this process four times (for j = 0 to 3). For each row, the inner loop repeats four times (for k = 0 to 3), collecting input for each element.

The Output ProductPrint () Function.

When the control exits the loop, the ProductPrint() function is called, passing the Products variable as a parameter.

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 receives the Products Array reference (address) through the ByRef method.  If the ByVal or ByRef keyword is not explicitly specified before the parameter variable, then ByRef is the default. 

As in the earlier program, two integer variables, j and k, are declared as control variables for the outer and inner For…Next loops. These loops are required to navigate the array using its row and column index numbers. The loop starts at 0, and to determine the end value dynamically, we use the Ubound() (Upper Bound) function on the array dimension. In the previous example, we used values 0 to 3, but here we use Ubound() to make the program flexible. This ensures that if the array size changes later through ReDim statements, the loop still calculates the correct number of rows and columns.

Usage: UBound() Function to get Two-Dimensional Index Numbers.

UBound(Array,1).

The Ubound(List, 1) function returns the number of rows in the array, which is 4. However, array row indexes start from 0 in memory, so the valid index numbers are 0 to 3. The second argument (1) in the Ubound() function specifies that we want the upper bound of the first dimension (rows) of the array. Because the row index starts at 0, we subtract 1 from the total number of rows (4 − 1) when using these indexes in a For…Next loop.

UBound(Array,2).

The UBound(List, 2) function returns the number of columns in the array. The second parameter is optional—if omitted, UBound will return only the upper bound of the first dimension (rows). For single-dimensional arrays, this second parameter is never used.

The statement immediately following the first For…Next loop — List(j, 3) = List(j, 1) * List(j, 2) — calculates the total price of each item and stores it in the rightmost cell. These updated values are then printed in the Debug window during the next 'For… Next' loop, which outputs the complete sales record for each item.

Controlling the Print-Head

Placing a comma at the end of a 'Debug.Print' statement aligns the next item in the 14th column on the same line, following the previously printed item.

An empty 'Debug.Print' statement, placed immediately after the inner Next statement (without a trailing comma), moves the print cursor back to the first column of the next line. This ensures the output for the next sales record begins at the correct position.

If a semicolon (;) is placed at the end of a 'Debug.Print' statement, the print cursor advances to the very next character position, without leaving any space between the printed items.

Next week, we will explore user-defined variables that can hold mixed data types. With them, we can assign meaningful names to each collection element instead of array position, as we did in the examples above. This will make it much easier—and more intuitive—than trying to memorize each array element based solely on its index.

• Filter by Character and Sort
• Proper Function of Excel in Access
• Writing VBA Code with VBA
• Positioning Pop-up Forms
• Control Setfocus on Tab-Page Click

Read More

Function Parameter Array Passing

Function Parameter Array Passing.

Last week, we explored the use of ByVal (By Value) and ByRef (By Reference) in function parameters. These keywords determine how data is passed from the calling function to the called function—either as the value stored in a variable (ByVal) or as the memory address (reference) of the variable (ByRef). If you have not yet read that article, you can find the link below:

• Function Parameter ByVal and ByRef usage.

Now, we will learn:

1. How to pass the location address of a single element of an Array to the called Function and change its value?

2. How to pass the Location Address of an array and sort the values in the array in Descending Order.

Two Test Programs.

Function ArrayArg_Test1().

First, let us write two small programs for the first example. The first Program Code is given below.

Public Function ArrayArg_Test1()
Dim NumArray(1 To 5) As Integer, j As Integer

'load array with numbers 1 to 5
For j = 1 To 5
   NumArray(j) = j
Next

'pass a single element value
Call ArrayArg_Test2(NumArray(4))

'Print the Array values after change
For j = 1 To 5
  Debug.Print j, NumArray(j)
Next

End Function

'Result:
1
2
3
20
5

In the first line of code, we define an array variable (NumArray()) with five elements to store integer values. We also declare a control variable j for use in the For…Next loop.

Inside the 'For… Next' loop, the array is populated with values from 1 to 5 — for example, NumArray(1) = 1, NumArray(2) = 2, and so on, up to the fifth element.

Next, we call the ArrayArg_Test2() function, passing NumArray(4) as a parameter. The number 4 inside the brackets refers to the index position of the element being passed—not its value. In this case, the element at index 4 holds the value 4. The ArrayArg_Test2() function receives this argument either as the value itself or as a reference to its memory location, depending on how the function’s parameter is defined.

If the parameter is defined with ByVal, the value of the element is copied into a local variable within the function, leaving the original array element unchanged. If the parameter is defined with ByRef, the function works directly on the original element, without creating a copy. If neither ByVal nor ByRef is explicitly specified, ByRef is assumed. In our example, we will use this default behavior and omit the ByRef keyword in the function’s parameter definition.

In the next For … Next loop, the Array mcontents are printed in the Debug window. If the ArrayArg_Test2() function has made any changes to the Array, those changes will be reflected in this printed list. Since we already know that the array elements 1 to 5 initially contain the values 1, 2, 3, 4, and 5, we did not print them before calling the second function.

Function ArrayArg_Test2().

The ArrayArg_Test2() Function VBA Code is given below:

Public Function ArrayArg_Test2(NA As Integer) 'The word ByRef is omited
'multiply NumArray(4) value * 5 = 20
    NA = NA * 5
End Function

The Variable NA is assigned to the NumArray’s 4th element location address.  The ArrayArg_Test2() picks the value from NumArray(4) itself, multiplies it by 5, and stores the result back in the same location.

This was working with a single element of an Array.  What about passing the full Array’s location address and working with hundreds of elements of this array in the called Function? 

Get the Array Sorted and Print.

We will now pass the same array used in the previous example to a sorting function, which will arrange its values in descending order.

The modified version of the First Function Code is given below. 

Public Function ArrayArg_Test3()
Dim NumArray(1 To 5) As Integer, j As Integer

For j = 1 To 5
   NumArray(j) = j
Next

'Pass the array to the called function
Call ArrayArg_Test4(NumArray())

'Print the Sorted Array
For j = 1 To 5
  Debug.Print j, NumArray(j)
Next

End Function

Check the function call statement. NumArray() is passed without the number of elements, as we did in the earlier example. The parentheses are required along with the array name to indicate that the parameter is an array, not a single variable.

When control is returned from the ArrayArg_Test4() function, the sorted list of numbers is printed in the debug window. The value printed on the left side is the array element number, and on the right the value itself,  sorted in descending order.

Sort the Array in Descending (Z-A) Order.

The Data Sorting Program is given below:

Public Function ArrayArg_Test4(apple() As Integer)
Dim j As Integer, k As Integer, Temp As Integer

'Bubble Sort the Array in Descending order
' 1st loop runs maximum array elements minus 1 times

For j = 1 To 5 - 1 ' in place of 5-1 you may use Ubound(apple)-1

   ' inner loop starts with outer loop's current value + 1
   ' and runs to the maximum number of array elements times

      For k = j + 1 To 5 ' replace 5 with Ubound(apple)

     If apple(k) > apple(j) Then 'if second value is greater
     
        Temp = apple(j) 'copy 1st value to Temp Variable
        
        apple(j) = apple(k) 'move greater value up
        
        apple(k) = Temp ' move the smaller value down
        
     End If
    Next k ' compare next two elements
Next j
End Function

To sort the values in ascending order, the only change needed in the program is to replace the greater-than (>) comparison operator with the less-than (<) operator. In our example, the numbers were already loaded into the array in ascending order.

To make the program work with arrays of any size, replace the fixed constant 5 - 1 in the first For… Next loop with UBound(apple) - 1, and replace 5 in the second loop with UBound(apple). This way, the program can handle arrays without requiring further changes.

Also, note that we omitted the ByRef keyword in the parameter definition of the called ArrayArg_Test4() function. Since ByRef is the default in VBA, the parameter is automatically treated as a ByRef Variable.

• Autonumber with Date and Sequence Number
• Overlaying Sub-Forms in Real-Time
• Opening Multiple Instances of Form
• Conditional Formatting in Continuous Form
• Control Setfocus on Tab-Page Click

Read More