CODEWITHSUNDEEP
excelvba
SometingNew
SometingNew

Reputation: 59

How to fill an array with dates of the month?

I want to fill an array with values and then fill a combobox with the values of the array.

Here is my code:

Dim i As Integer
Dim arr As Variant
Dim firstDay As Date
Dim MyMounth As Long
Dim initial_array As Variant
Dim lastDay As Date

MyMounth = 1
firstDay = DateSerial(Year(Date), MyMounth, 1)

lastDay = DateAdd("m", 1, firstDay) - 1

For i = 1 To lastDay - firstDay

    initial_array(i) = Format(firstDay + 1, "d mmmm yyyy")

Next i

ComboBox3.List = initial_array

I got the first day and the last day of the Month January because of MyMounth = 1.

How do I fill the array?

Upvotes: 1

Views: 853

Answers (1)

riskypenguin
riskypenguin

Reputation: 2199

You are not changing the values you're putting into the array, you're putting Format(firstDay + 1, "d mmmm yyyy") into the array multiple times. This might be what you're looking for:

For i = 0 To lastDay - firstDay

    initial_array(i) = Format(firstDay + i + 1, "d mmmm yyyy")

Next i

Also, arrays start at 0, so you should start populating your array at index 0.

EDIT: Sorry, I completely missed your array declaration, try this corrected code:

Dim i As Integer
Dim arr As Variant
Dim firstDay As Date
Dim MyMounth As Long
Dim initial_array As Variant
Dim lastDay As Date

MyMounth = 1
firstDay = DateSerial(Year(Date), MyMounth, 1)

lastDay = DateAdd("m", 1, firstDay) - 1

ReDim initial_array(lastDay - firstDay)
For i = 0 To lastDay - firstDay

  initial_array(i) = Format(firstDay + i, "d mmmm yyyy")

Next i

ComboBox3.List = initial_array

This will put every value including firstDay and lastDay into the array.

EDIT: Explanation: Before populating the array with your values you need to set the length of the array to the number of your values (since arrays start at 0, if you want to put the numbers from 11 through 15 in it, your array will look like this (index: value): 0: 11 - 1: 12 - 2: 13 - 3: 14 - 4: 15. So your array has a length of 5, but the last index is 4). The line ReDim initial_array(lastDay - firstDay) re-initializes the array with a length of (lastDay - firstDay).

Next, while looping through the array with a For loop and populating it with values you don't only need to increment the array index, but your values as well. The expression Format(firstDay + 1, "d mmmm yyyy") does not change, since firstDay never changes. So each time we increase i by 1 we also increase the value you're putting into the array by 1 by adding i.

Upvotes: 2

Related Questions