A Handy Function

Next year my seniors club is changing their meetings from fortnightly to twice a month, in the 2nd and 4th Thursday of each month. I had to change some of our documents and spreadsheets to cope with this change.

This is a very handy VBA function that works for such a situation:

Function ThisDay(ByVal yy As Long, ByVal mm As Long, ByVal ThisWeek As Integer) As Date

' calculates date of nth day (nDay) of nth week (ThisWeek) of month
' ie 2nd Thursday in month

Dim nD As Date
Dim nDay As Integer
Dim nDate As Integer

nDate = (7 * (ThisWeek - 1)) + 1

'nDay Value - SUN=1 MON=2 TUES= 3 WED=4 THUR=5 FRI=6 SAT=7

nDay = 5

nD = DateSerial(yy, mm, nDate)

While Weekday(nD) <> nDay
    nD = nD + 1
Wend

ThisDay = nD

End Function

You don’t need the ByVal calls as they are the default but I put them in for clarity.

To call it for example to find the 2nd Thursday of the current month

ThisMeeting = ThisDay(Year(Date), Month(Date), 2)

or it could be called using variables:

ThisMeeting = ThisDay(Year(ThisDate), ThisMonth, 4)

Someone might find it useful.

[quote=“Bruce, post:1, topic:98954”]
Next year my seniors club is changing their meetings from fortnightly to twice a month
[/quote]:wink:

Errrrmm … aint that the same spacing? Fortnightly = every 14 days and twice monthly also = every 14 days, or am I missing something :man_shrugging:

Occasionally some months will have a fifth Thursday?

At our great age nothing can ever be guaranteed. :grinning:

2 Likes

You’re missing something - the meetings will be on the second and fourth Thursdays of the month.

Currently for example in August we have a meeting on the 3rd, 17th and 31st so three meetings in a month, this happens a couple of times a year. Whereas the meetings for this month would happen on the 10th and 24th if the new regime was in place. There are a lot of advantages and two meetings less a year.

1 Like

Might I suggest a yearly diary rather than an electronic version Bruce. The frequency may change again in the short term…
:nerd_face:

Well, if they change it at their current frequency it will 2085 before it is changed again so I am not going to be troubled by it and will give the diary a miss.

I am back to being branch secretary again, this script fills in the date of the current meeting, the previous meeting and the next meeting into the minutes, it also knows that we start meeting at the end of January and finish at the beginning of December and fills in the correct dates for that as well. There is little I actually have to type.

Previously it only had to know a previous meeting date (many years ago even) and work out fortnights from that. Having meetings on 2nd and 4th Thursdays makes the calculation a little more complex but nothing VBA can’t do in a millisecond.

2 Likes

Cheers Bruce, I didn’t realise that you were branch secretary and you may be called upon to provide history etc…

I can most certainly guarantee that The Grim Reaper will call for each of us at some time in the future. Sooner or later, it will be a call none of us can avoid :wink::grin:

I’m letting nobody with a bloody great Scythe anywhere near me.

1 Like

cheerful bugger aren`t you LD :shushing_face:

1 Like

I am cheerful but as for a bugger, not guilty M’Lord.:grin:

Off with his head!

Father time

I gave it up after many years just before the pandemic and fortunately someone else took it over but they made a bit of a dogs breakfast of it and no one else wanted the job so I was asked to take over the position again.

Years ago tried to make minute writing an automated process in Word so this was just a tweak to that. It took me a a couple of hours to write it and get it working properly then insert it in the old script but now it is ready for next year.

Minutes have a format so have made it a form filling exercise, writing the minutes now only take about 15 minutes and fulfils all the constitution requirements even for a quite complex meeting. It even checks the figures for the treasurer’s report for example.

Same with the membership record all handled by VBA scripts, makes life so much easier.

1 Like

That looks like a very tidy programme, I would be proud of that. I used to use excel when I was a courier to work out prices for various distances. Based on the MPG of the van and taking into account the VAT I charged 40 pence per loaded mile back then. You just entered the destination and excel did the rest.

You might be interested to see the interface for the Minutes script using M$ Word.

Seniors Minutes Entry Screen

The Tabs along the top are the various sections of the minutes, and the script adds text to the bare bones info.

For example if you put name(s) into the New Members line then this will appear in the minutes:

The branch welcomed Bill Blogs and Ernie Dingo as new members

If it is left blank then nothing appears in the minutes. I have found that is better than having a template with everything included and filling in blanks or deleting lines.

The minutes are less than two pages even if every section has something entered. The script is now quite long and complex but it grew over time and works faultlessly (touch wood), the new addition for the new meeting dates is only a tiny addition. This was the test data I added:

'calculates meeting for 2nd and 4th ThisWeeksday of month

Private Sub FindDate()

Dim LastMeeting As Date
Dim ThisMeeting As Date
Dim NextMeeting As Date
Dim ThisDate As Date
Dim ThisMonth As Integer

'ThisDate = Date  '<<<<<<<<uncomment<<<<<<<

'test dates
ThisDate = #12/2/2024#   '<<<<<<<<<<Comment out<<<<<<<<<

ThisMonth = Month(ThisDate)

Select Case ThisMonth

    Case Is = 1
    
        '4th ThisWeeks in January
        
        ThisMeeting = ThisDay(Year(ThisDate), ThisMonth, 4)
        
        LastMeeting = ThisDay(Year(ThisDate) - 1, 12, 2)
        
        NextMeeting = ThisDay(Year(ThisDate), ThisMonth + 1, 2)
        
        If ThisDate < ThisMeeting Then
                 
            ThisMeeting = ThisDay(Year(ThisDate) - 1, 12, 2)
            
            LastMeeting = ThisDay(Year(ThisDate) - 1, 11, 4)
            
            NextMeeting = ThisDay(Year(ThisDate), ThisMonth, 4)
              
        End If
        
        
       
    Case 2 To 11
    
         '2nd ThisWeeks in month
         
        ThisMeeting = ThisDay(Year(ThisDate), ThisMonth, 2)
        
        LastMeeting = ThisDay(Year(ThisDate), ThisMonth - 1, 4)
         
        NextMeeting = ThisDay(Year(ThisDate), ThisMonth, 4)
        
        If ThisDate < ThisMeeting And ThisMonth > 2 Then
                 
            ThisMeeting = ThisDay(Year(ThisDate), ThisMonth - 1, 4)
            
            LastMeeting = ThisDay(Year(ThisDate), ThisMonth - 1, 2)
            
            NextMeeting = ThisDay(Year(ThisDate), ThisMonth, 2)
              
        End If
        
         If ThisDate < ThisMeeting And ThisMonth = 2 Then
                 
            ThisMeeting = ThisDay(Year(ThisDate), ThisMonth - 1, 4)
            
            LastMeeting = ThisDay(Year(ThisDate) - 1, 12, 2)
            
            NextMeeting = ThisDay(Year(ThisDate), ThisMonth, 2)
              
        End If
        
        If ThisDate > ThisMeeting + 13 Then
                  
            ThisMeeting = ThisDay(Year(ThisDate), ThisMonth, 4)
            
            LastMeeting = ThisDay(Year(ThisDate), ThisMonth, 2)
            
            NextMeeting = ThisDay(Year(ThisDate), ThisMonth + 1, 2)
              
        End If
    
    Case Is = 12
    
         '2nd ThisWeeks in Dec
         
        ThisMeeting = ThisDay(Year(ThisDate), ThisMonth, 2)
         
        LastMeeting = ThisDay(Year(ThisDate), ThisMonth - 1, 4)
         
        NextMeeting = ThisDay(Year(ThisDate) + 1, 1, 4)
    
        If ThisDate < ThisMeeting Then
        
            '4th ThisWeeks Nov
            
            ThisMeeting = ThisDay(Year(ThisDate), ThisMonth - 1, 4)
         
            LastMeeting = ThisDay(Year(ThisDate), ThisMonth - 1, 2)
            
            NextMeeting = ThisDay(Year(ThisDate), ThisMonth, 2)
            
    
        End If

End Select


Debug.Print "This Meeting = " & ThisMeeting
Debug.Print "Last Meeting = " & LastMeeting
Debug.Print "Next Meeting = " & NextMeeting

End Sub


Function ThisDay(ByVal yy As Long, ByVal mm As Long, ByVal ThisWeek As Integer) As Date

' calculates date of nth day (nDay) of nth week (ThisWeek) of month
' ie 2nd Thursday in month

Dim nD As Date
Dim nDay As Integer
Dim nDate As Integer

nDate = (7 * (ThisWeek - 1)) + 1

'nDay Value - SUN=1 MON=2 TUES= 3 WED=4 THUR=5 FRI=6 SAT=7

nDay = 5

nD = DateSerial(yy, mm, nDate)

While Weekday(nD) <> nDay
    nD = nD + 1
Wend

ThisDay = nD

End Function

The Function section is as previously mentioned. The rest replaced a few lines of code because once you had the current date it was just a matter of adding or subtracting 14 whereas the new system is a bit more complex. Rewriting it also gave me the opportunity to automate bits that I would have previous altered manually.
.

That’s far superior to anything I’ve done in the past Bruce. How do you learn all this stuff?

I learned BASIC on my Commodore 64 and have just kept it up or rather kept up with the improvements

VBA is being replaced but I am too old to learn anything new but it will see me out.

I use Batch files as much as I use VBA, they are my favourite computer language and M$ still support it and have improved it over the years. They are great for backups etc and much quicker than GUI

1 Like

I have been considering using Arduino for some of my electronics projects. Looking at examples on youtube it should come in very handy, I think they are programmable, do you know anything about them?

https://www.makeuseof.com/tag/10-great-arduino-projects-for-beginners/