Auto-Numbers in Query Column Version-2

Introduction

In January 2010 I published a Function: QrySeq() - Auto-Numbering in Query Column on this website and was well-received by the readers all through these years.  While going through it again, I thought it can be written with less Code and improve its performance by using a better approach, other than a variant Array.

When the function QrySeq() is called from a query record the program searches through the Array of Unique Keys and looks for the matching key, passed from the record as a parameter, finds it, and returns the sequence number, from the Array element to the calling record.

If the Query has a large volume of records this process may take more time because every time the program looks for the key value from the beginning of the Array.

The New Version is with the Name: QryAutoNum()

Using Collection Object instead of Array.

You can find a detailed discussion of Collection Object, on Ms-Access and Collection Object Basics Page.

Here we will have a brief introduction to know what it is and how it is used in VBA.  The Collection Object is a versatile Object that can hold, in general terms, any Values, Numeric or String Values, Class Module Objects, or a collection of other Objects.  The Collection Object is instantiated in VBA programs in the following manner:

'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

So, we will use the Collection Object to add the Query Auto-Numbers with the Unique Key Values as Collection Object Key.  With this approach, we can retrieve the Auto-Numbers directly, rather than struggling with Arrays and complicated storing/retrieving 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.

On the Global Area of the Module, we have declared a Collection Object with 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 Auto-Number is in a Long Integer format.

Three Static Variables,  K and Y declared as Long Integers, and fld was declared as 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 is different from the saved name fld, then it assumes that the function call is from a new Query Record.  If the field name is the same, but the value in variable K is greater than Y  then the earlier Query is calling the function QryAutoNum() for a repeat of the earlier run.   In either case, the control variable K is reset to zero, and the Collection Object with existing Items is cleared from memory.  The new Key field name received in the KeyFldName variable is saved in the fld variable for later validation check.

Next, if the KeyValue parameter value is numeric then it is converted to String format in the statement: KeyValue = Cstr(KeyValue). The Collection Object Item Key must be in string format.

Next, the variable K is incremented by one.  When the value in K=1 it assumes that this is the first call of this function, from the first record of a Query.  When this is the case the main process of this function starts.

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 Standard Module’s  Global area is instantiated in memory, with the statement Set C = New Collection.

The Query record set is opened to read records one by one. The local variable J is for creating Auto-numbers and adding 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 the records and added to the Collection Object, one after the other.  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 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.

So, the Function calls from the second record onwards will take the ELSE path of the If K=1 Then statement and retrieve the Auto-Numbers from Collection Object, using the KeyValue passed as Parameter, and returns it to respective records in the Query.

It works very fast because we can directly pick the item value, using the Collection Object Key, rather than searching for the Key, through the Array from the beginning to find the one we want.

When all the record Auto-Number values are returned, the value in the control variable K = Y. We have already taken the count of records of the Query, in Variable Y at the beginning of the program. At this point we increment the value in variable K, by 1 to make it more than the value in Variable Y.  Since, K and Y are Static Variables their values are not lost after the last record call is over and remains in memory.  If the same Query is run a second time the test for these variable values can determine whether we need to reset the variable values and clear the earlier Collection Object from memory for a fresh start of the process all over again.

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

The sample Report Image using the above Query as Source is given below for reference. 

You can use the Query as Source for Report or Form. 

A sample demo database, with all the Objects and VBA Code, is attached for Downloading and for trying out the Code.

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

Introduction.

This is an offshoot of the earlier Function Running-Sum, in Query Column.  With few changes in the earlier Function RunningSum() we can easily calculate and find the loan balance-to-pay amount, after deducting the monthly paid amount, 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 payment amount.

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 record set used as a source for the earlier RunningSum() Function and used here also 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 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 record count of the Query 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 value in the control-variable K is more than the Query record count in variable y then resets the Static variables 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 repayable total Loan Amount and retrieves it from it’s 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 number 1 and you may 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 from the record 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 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 then the Add method fails and will end up with Errors.

With the rst.MoveNext statement takes the next record for the same sequence of processing and adds the result value to the Dictionary Object.

This way individual record value is deducted from the remaining balance loan 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 the value retrieved 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 still the value in variable K=1 and the first Parameter IKey retains the first records Unique Id Value.  At the first record level call of the function DiminishingBal() itself, we have calculated all the record level balance loan amount values, one by one, and added the result to the Dictionary Object as its Items. The function parameter IKey still holds the first record’s Unique ID value. That is the reason why we have used a separate variable p for individual record key values while processing all the records.

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

The next statement DiminishingBal = D(IKey) retrieves the first value and added 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 to the ELSE path of the IF. . .Then statement, retrieves the second Item value from the Dictionary Object, using the IKey parameter, and returns it to the respective record of the Query.

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 Dictionary Item, and returns it to the function calling record.

The Next If . . . Then statement checks whether the value in variable K = y or not.  Variable y holds the total record count of the Query.  If it is found 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 value in variable y.

This is necessary to initialize the Static Variables during the rerun of the same Query. In case of any change made on the Source Data before rerun, it will not reflect on the balance amount calculated earlier, because it will keep taking the ELSE route of the If . . . Then statement and retrieves the old value from 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

Introduction.

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

The RunningSum() Function is written somewhat on similar logic, with a few parameters matching the QrySeq() Function.

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 with few records.

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

The SQL Code of 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 above sample data, to draw your attention to an important point while preparing the data for the RunningSum() Function.

1. A unique ID Field, like Primary Key, is required in the Query, with either Numeric or String Data, and strictly no duplicates in them.
2. If this is not readily available in the Source-Data, you may join (concatenate) two or more field values 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 given 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 should, like the sample Image given below, with all Count values showing as one.



4. When all the ID Field values are unique then the CountOfID2 Column will have the value 1 in all records.  Greater than one in any record means that those records have duplicate key values and need to join some other field to eliminate duplicates.
5. Once you are sure that all records have unique ID values then you may add other required fields in the first Query for the purpose you plan to use, like Form or Report Source Query.
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.

On 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 a 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 then you can declare and instantiate a Dictionary Object, like the Class Object of Access or Collection Object.

Note: If you are not familiar with Dictionary, Class Object, or Collection Object, then we have all the information you need to learn the fundamentals about them, on this Web site.  The links are given at the end of this page. You may visit them to learn with 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 does 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 an added advantage of displaying its Properties and Methods, when you type a dot (D.) 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 Key-Value of Dictionary Object and the current Value in X is written as Dictionary Object Item, as 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 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,  like 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 the 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 are taking place when the control-variable K=1.  

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

Some Images of a sample Run done on the Products Table of NorthWind 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 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 Event
   
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

Introduction.

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, it saves report design time.  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 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 above Code into the Standard VBA Module, save the Code and compile it.

Let us try out the Code directly from 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 TextBox on Report Header, use on 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 into Text form can be changed back into a valid date format with the Text2Date() Function. There is no validation check performed on the input value for errors and expected to pass the parameter value in the correct input format, the same as the Date2Text() Function output.

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

We have introduced the ROUNDUP() Function of Excel in Microsoft Access earlier.  It works like the ROUND() Function except that the number is always rounded up.  When a number of digits are greater than 0 (zero), then the number is rounded up to the specified number of decimal places. If a number of digits specified are 0 (zero), then the number is rounded up to the nearest integer.

Visit the following link for more details on 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.

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 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

Introduction.

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 panelist 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 others.  Subscribe to Blogs/News Feeds, or topics of any other area of interest, and get regular updates from www.blog.feedspot.com to your Inbox, as and when they happen, 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 an array containing 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: An important point to note is that any re-dimensioning of the array 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 one 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 individual cell in the array 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 look at the Dim statement used to define the two-dimensional array, and how the sales values are assigned to 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 (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 numbers of the rows and columns. 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), and for each row, the inner loop runs four times (for k = 0 to 3), collecting values from the user for each cell in the current row.

The Output Function ProdPrint().

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 memory reference (address) of the Products array through the ByRef method.  If the ByVal or ByRef keyword is not specified explicitly before the parameter variable, it assumes ByRef as 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 Boundary) function on the array dimension. In the previous example, we directly used the 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 automatically.

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 element of a collection instead of relying on array positions as we did in the examples above. This will make it much easier—and more intuitive—than trying to memorize each array element’s logical meaning 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

Introduction

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 happens to hold 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 used by default. 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 contents of the array 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 as a parameter 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 element number, as we did in the earlier example. The opening and closing brackets are required along with the array variable name to indicate that the parameter passed 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 the right-side value is the array 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 with any number of elements 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