vba: lbound error on dates array

Question

I have a "Datevarie" dynamic matrix which contains many dates.

At some point in the routine I have to extract from DateVarie the lowest value.

The problem is that when I use the Lbound function, I always return the value 1 instead of the date (for example "01/01/2018").

I think the data in the array is entered correctly as I debugged it step by step several times.

How can I resolve without having to report data on a sheet and order them? Do I have to use FOR ... NEXT? The dates can be 2-3 but also several tens

Type interventi
...
data as date
...

public Intervento as interventi
public rs1 as adodb.recordset    
    ...
Sub TheSubWithProblem(...)
...
dim Datevarie() as date
..
redim preserve etc..
DateVarie(k)=format(RS1!DataI,"dd/mm/yyyy") '<<<< correct
...

Intervento.data=lbound(datevarie)  '<<<< always return 1
...

Sub test() Dim datevarie(2) As Date datevarie(1) = DateValue("01/01/2000") datevarie(2) = DateValue("02/01/2000") Debug.Print LBound(datevarie) End Sub.... results "0" — Massimo Griffani
– Massimo Griffani, Commented Jun 15, 2018 at 12:48
If you want the date instead of the index, try datevarie(lbound(datevarie)). — Brian M Stafford
– Brian M Stafford, Commented Jun 15, 2018 at 12:50
@MassimoGriffani that results 0 because the LBound of datevarie is datevarie(0). — dwirony
– dwirony, Commented Jun 15, 2018 at 12:51
@MassimoGriffani Are you looking for the date value in the first position of the array, or the earliest date value anywhere in the array? — Mistella
– Mistella, Commented Jun 15, 2018 at 12:52

David Zemens · Accepted Answer · 2018-06-15 13:11:37Z

The problem is that when I use the Lbound function, I always return the value 1 instead of the date (for example "01/01/2018").

LBOUND and UBOUND functions return the lower- and upper bounds of the array, not the lowest and highest values within the array.

Arrays in VBA are base-0 by default, so an initialized array should typically have LBound of 0.

If you want to get the lowest or highest value, then you need to implement your own algorithm to do so (e.g., a for/next loop), or if this is a single-dimensional array we can potentially make use of an ArrayList object which has (among other things) a Sort method:

Set d = CreateObject("System.Collections.ArrayList")
Dim dateVarie As Variant
Dim i As Long, m As Long, day As Long

For i = 1 To 100
    m = Application.WorksheetFunction.RandBetween(1, 12)
    day = Application.WorksheetFunction.RandBetween(1, 30)
    d.Add (DateValue(DateSerial(2018, m, day)))
Next

'Preserve your un-sorted array, if needed:
ReDim dateVarie(d.Count - 1)
dateVarie = d.ToArray()

Msgbox "The first value is: " & dateVarie(0)

d.Sort  ' Sorts the arraylist:

MsgBox "The lowest value is: " & (d(0))

Vityata · Accepted Answer · 2018-06-15 12:58:00Z

0

Arrays start at 0. This is why the below returns 0:

Sub TestMe()

    Dim datevarie(2) As Date
    Debug.Print LBound(datevarie)

End Sub

And it does not matter, whether you put values in the array or not. If you want an array to start at 1, in VBA write Option Base 1 and make sure that noone sees the code, because you may have problems with your colleagues/peers:

Option Explicit
Option Base 1

Sub TestMe()

    Dim datevarie(2) As Date
    Debug.Print LBound(datevarie)

End Sub

edited Jun 15, 2018 at 12:58

answered Jun 15, 2018 at 12:50

Vityata

43.8k10 gold badges64 silver badges110 bronze badges

Collectives™ on Stack Overflow

vba: lbound error on dates array

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related