PDA

View Full Version : sub that creates two arrays month and dayslnMonth



Lesley
10-02-2017, 03:58 PM
I am trying to understand where to start with this coding ... anyone know?

Write a sub that creates two arrays month and dayslnMonth, each of size 12. It should then populate the first array with the months January, February, and so on, and it should populate the second with the number of days in these months: 31, 28 (assume it is not a leap year), and so on. Then it should loop through these arrays and write their elements to the range A1:L2. For example, cells A1 and A2 will have January and 31.

Bob Phillips
10-03-2017, 01:26 AM
The first array is straightforward


Dim months(1 To 12) As String
Dim i As Long

For i = 1 To 12

months(i) = Format(DateSerial(2017,i,1), "mmmm")
Next I


this builds a date of the first of each month for this year in a loop, DateSerial(2017,i,1), and then extracts the month name using the format method.

Days in the month is trickier, so here I use an Excel function EOMONTH to get it. In Excel, if you do say =EOMONTH(TODAY(),0) it returns the last day of the month (the ,0 is the key here, check out EOMONTH). So we can build a date in a loop of the first of each month for this year (not a leap year), dte = DateSerial(2017, i, 1),. The days array is populated by passing that date to the EOMONTH function using APplication.Evaluate, days(i) = Day(Application.Evaluate("EOMONTH(" & CLng(dte) & ",0)"))


Dim months(1 To 12) As String
Dim days(1 To 12) As Long
Dim dte As Date
Dim i As Long

For i = 1 To 12

months(i) = Format(DateSerial(2017,i,1), "mmmm")
Next i

For i = 1 To 12

dte = DateSerial(2017, i, 1)
days(i) = Day(Application.Evaluate("EOMONTH(" & CLng(dte) & ",0)"))
Next I

Now we have the two arrays populated, it is a simple loop to populate the worksheet.


Dim months(1 To 12) As String
Dim days(1 To 12) As Long
Dim dte As Date
Dim i As Long

For i = 1 To 12

months(i) = Format(DateSerial(2017, i, 1), "mmmm")
Next i

For i = 1 To 12

dte = DateSerial(2017, i, 1)
days(i) = Day(Application.Evaluate("EOMONTH(" & CLng(dte) & ",0)"))
Next i

For i = 1 To 12

Cells(1, i).Value = months(i)
Cells(2, i).Value = days(i)
Next i

Lesley
10-03-2017, 06:32 AM
Ok I understand a little better now! How would I use the xlDown feature to get them to list downward

January 31
February 28

etc......

Lesley
10-03-2017, 07:37 AM
I give up! Please help! We are using xlDown and I have no idea how to write to get code that will list months and days in month
January 30
February 28
March 30

Instead of two one- dimensional arrays, create one two-dimensional array monthInfo of size 12 by 2.
The first dimension should contain the month and the second should contain the number of days in the month.
For example, monthInfo(6,1) should be June and monthInfo(6,2) should be 30. Use nested loops to fill the range A1:L2 .

mdmackillop
10-03-2017, 07:58 AM
Sub Test()Dim MonthInfo(1, 11)
For i = 0 To 11
x = DateSerial(Year(Date), i + 1, 1)
MonthInfo(0, i) = Format(x, "mmmm")
MonthInfo(1, i) = Format(Application.EoMonth(x, 0), "d")
Next i
Range("A1:L2") = MonthInfo
End Sub

snb
10-03-2017, 08:08 AM
Is this a school assignment ?

SamT
10-03-2017, 08:54 AM
@ snb,
Yes.

@ Paul,
Mac's is incomplete, but it's homework, so that's a "Good thing."

SamT
10-03-2017, 08:59 AM
Lesley,

We discourage one person making two threads on the same subject, so I combined both of your homework threads into this one.

SamT

Bob Phillips
10-03-2017, 09:00 AM
@ snb,
Yes.

@ Paul,
Mac's is incomplete, but it's homework, so that's a "Good thing."

I thought that at first, but in another post the OP was intimating that they were working through chapters of a book.

I took the long-winded approach as Lesley wants/needs to understand looping, not just be given a solution.

Lesley
10-03-2017, 09:01 AM
It is a pre-assignment so we can do the actual assignment later with different info and I am not getting it at all. I read the entire chapter and again I am not getting it at all

Lesley
10-03-2017, 09:03 AM
I would much rather use excel formulas than VBA .... it is entirely too confusing and I am not sure what I am missing.

mdmackillop
10-03-2017, 09:29 AM
Looping with 2 arrays

Sub Test()
Dim Month(12)
Dim DaysInMonth(12)
Dim x As Date, y As Date
Dim i as Long
For i = 1 To 12
'Set x to first of month
x = DateSerial(2017, i, 1)
'Set y = First of next month -1
y = DateSerial(2017, i + 1, 1) - 1

'Write month to array
Month(i) = Format(x, "mmmm")
'Day number
DaysInMonth(i) = Format(y, "d")
Next i

'Write result
For i = 1 To 12
Cells(1, i) = Month(i)
Cells(2, i) = DaysInMonth(i)
Next i
End Sub

Edit: This is questionable for homework as it uses month 13, but which Excel will interpret correctly.

snb
10-03-2017, 12:06 PM
Or:
- illustrating zero based arrays
- illustrating how to write a zero-base array into a worksheet
- illustrating the basic VBA library : monthname, day and dateserial
- illustrating how to use a counter outside a loop


Sub M_snb()
Dim sn(11, 1)

For j = 0 To UBound(sn)
sn(j, 0) = MonthName(j + 1)
sn(j, 1) = Day(DateSerial(2017, j + 2, 0))
Next

Cells(1).Resize(j, UBound(sn, 2) + 1) = sn
End Sub

A second loop doesn't make sense in this case.

Paul_Hossler
10-04-2017, 05:24 AM
I'm confused ... (normal state of affairs)


Write a sub that creates two arrays month and dayslnMonth, each of size 12. It should then populate the first array with the months January, February, and so on, and it should populate the second with the number of days in these months: 31, 28 (assume it is not a leap year), and so on. Then it should loop through these arrays and write their elements to the range A1:L2. For example, cells A1 and A2 will have January and 31.



Instead of two one- dimensional arrays, create one two-dimensional array monthInfo of size 12 by 2.
The first dimension should contain the month and the second should contain the number of days in the month.
For example, monthInfo(6,1) should be June and monthInfo(6,2) should be 30. Use nested loops to fill the range A1:L2 .



and


I would much rather use excel formulas than VBA .... it is entirely too confusing and I am not sure what I am missing.


Do you want two 1-D arrays or one 2-D or both

Worksheet formulas or VBA?

XLD gave a nice explanation of Looping which was in his previous post

As examples of looping to show concepts. There are more clever ways to do this (see previous posts) but these are introductory examples to show the concept



Option Explicit
Sub TwoArrays()
Dim MonthNames(1 To 12) As String
Dim DaysInMonth(1 To 12) As String
Dim i As Long
Dim FirstOfMonth As Date

'month names
For i = 1 To 12
'date = first day in i-th month
FirstOfMonth = DateSerial(2017, i, 1)

'format out the month name
MonthNames(i) = Format(FirstOfMonth, "mmmm")
Next I

'days in month
For i = 1 To 12
'day BEFORE the start of NEXT (i+1) month
FirstOfMonth = DateSerial(2017, i + 1, 0)
DaysInMonth(i) = Format(FirstOfMonth, "d")
Next I

'put on worksheet
For i = 1 To 12
'row i, column 1 = month name I
ActiveSheet.Cells(i, 1).Value = MonthNames(i)
'row i, column 2
ActiveSheet.Cells(i, 2).Value = DaysInMonth(i)
Next i
End Sub

Sub OneArray()
Dim MonthNamesAndDays(1 To 12, 1 To 2) As String
Dim i As Long
Dim FirstOfMonth As Date

For i = 1 To 12
'date = first day in i-th month
FirstOfMonth = DateSerial(2017, i, 1)

'refering to a 2D array
'format out the month name
MonthNamesAndDays(i, 1) = Format(FirstOfMonth, "mmmm")

'days in month
'day BEFORE the start of NEXT (i+1) month
FirstOfMonth = DateSerial(2017, i + 1, 0)
MonthNamesAndDays(i, 2) = Format(FirstOfMonth, "d")
Next I

'put on worksheet
For i = 1 To 12
'row i, column 1 = month name I
ActiveSheet.Cells(i, 1).Value = MonthNamesAndDays(i, 1)
'row i, column 2
ActiveSheet.Cells(i, 2).Value = MonthNamesAndDays(i, 2)
Next i
End Sub

SamT
10-04-2017, 08:07 AM
A 1D array is like a stack of containers, in your example, a stack 12 containers tall. It doesn't matter what you put in each container, but you can only address it as an entire container.
To borrow from Paul,
Dim MonthNames(1 To 12) As String
'month names
For i = 1 To 12
'date = first day in i-th month
FirstOfMonth = DateSerial(2017, i, 1)

'format out the month name
MonthNames(i) = Format(FirstOfMonth, "mmmm")
Next I
Each container holds one month Name, but his containers can hold any String, it just happens that the Strings you want are the Names of the months.

Each container in the stack can also hold other containers. A 2D Array.

Typically, it is standard and common for all primary containers to hold an identical number of secondary containers. In your example, you want two secondary containers, one to hold the Month Name, and the other to hold number the last day of that month.
Dim MonthNamesAndDays(1 To 12, 1 To 2) As String
For i = 1 To 12
'date = first day in i-th month
FirstOfMonth = DateSerial(2017, i, 1)

'refering to a 2D array
'format out the month name
MonthNamesAndDays(i, 1) = Format(FirstOfMonth, "mmmm")

'days in month
'day BEFORE the start of NEXT (i+1) month
FirstOfMonth = DateSerial(2017, i + 1, 0)
MonthNamesAndDays(i, 2) = Format(FirstOfMonth, "d")
Next I
That is 12 containers tall, each with two secondary contaniers inside.

In VBA all Array indices, at any number of sub container Levels, are numerical. [ArrayName(i, 1) and ArrayName(i, 2).] Every Primary container has two sub-containers numbered 1 and 2 inside.
Practically speaking, there is no small limit to the number of containers you can stack up, nor the number of secondary containers each can hold. Dim MyArray(1 to gazillions, 1 to gazillions)

1D, 2D, 3D, ...300D Arrays. The Number in front of the "D", ie 3D simply refers to the hierarchy of containers, IOW a 3D Array has a "Stack" of Primary containers, each with a set of Secondary Containers, each of which has a set of tertiary containers. This simply means that you need 3 loops to read all the containers. Note: in any Multidimensional Array, only the "smallest," or last dimension, containers contain any actual values, all the larger containers only contain smaller containers. For now, it is safe to think of every primary container in the stack as having identical internal "containership" structure.

In the Case of a 3D Array

Dim myArray(1 to i, 1 to j, 1 to k) 'i, j, and k are just some numbers. k = 3
'Only the "k" level has values, the "i" and "j" levels only contain more containers.
for Index_1 = 1 to n
for Index_j = 1 to nn
MyArray(Index_i, Index_j, 1) = MonthName
MyArray(Index_i, Index_j, 2) = LastDay
MyArray(Index_i, Index_j, 3) = FirstWeekDayName
next
next