IN Statement in VBA

[shs91]

Hi all

I have a macro, which I use to update a number of different reports. For example monday I want to update report A & B, Tuesday report B & C, Wednesday report A etc. To do this I use the Weekday(Now(), vbMonday) to determine whether a report should be called for example:

If Weekday(Now(), vbMonday) = 1 Or Weekday(Now(), vbMonday) = 3 Then Productions_Reports

Is there any way I can write the weekday equals 1 or 3 in a smarter way ?

For example using something similar to SQL Statement IN eg.
If Weekday(Now(), vbMonday) IN ("1","3) Then Productions_Reports

I look forward to your replies

Br,
Simon

[mdmackillop]

Have you tried Select Case?

Select Case Weekday(Now(), vbMonday)
Case 1, 3
Productions_Reports
Case Else
'Do nothing
End Select

[shs91]

Hi mark007

The problem with that solution is I have reports I run every day, and some I run on specify days, so there is no clear pattern. For your solution I would need like ten cases, which is not better than I currently have.

Br,
SImon

[Paul_Hossler]

I'd use a bit mapping technique to have flexibility

Option Explicit
Enum RunDays
    rdSun = 2
    rdMon = 4
    rdTue = 8
    rdWed = 16
    rdThu = 32
    rdFri = 64
    rdSat = 128
End Enum

Sub RunReports()
    
    Report1 rdMon
    Report2 rdTue
    Report3 rdMon + rdTue
    Report4 rdSun
End Sub


'runs on Monday
Sub Report1(RunOn As RunDays)
    If (2 ^ Weekday(Now) And RunOn) = 0 Then Exit Sub
    
    MsgBox "Report 1"
End Sub


'runs on Tuesday
Sub Report2(RunOn As RunDays)
    If (2 ^ Weekday(Now) And RunOn) = 0 Then Exit Sub
    MsgBox "Report 2"
End Sub

'runs Monday and Tuesday
Sub Report3(RunOn As RunDays)
    If (2 ^ Weekday(Now) And RunOn) = 0 Then Exit Sub
    MsgBox "Report 3"
End Sub


'runs Sunday
Sub Report4(RunOn As RunDays)
    If (2 ^ Weekday(Now) And RunOn) = 0 Then Exit Sub
    MsgBox "Report 4"
End Sub

[shs91]

Hi Paul

Thank you for your answer

However, I think that solution is more complicated and less readable than mine.

Do you guys know if it is possible using arrays ?

Br,
Simon

[snb]

even simpler:

if instr("13",Weekday(date, 2)) Then Productions_Reports

or

if len(replace("13",weekday(date,2),""))=1 then Productions_Reports

or using an array

if ubound(filter(array(1,3),weekday(date,2)))=0 then Production_reports

in Excel you can use

if not iserrror(application.match(weekday(date,2),array(1,3),0)) then Productions_Reports

[Paul_Hossler]

Hi Paul

However, I think that solution is more complicated and less readable than mine.

I'll disagree, esp since

I have reports I run every day, and some I run on specify days, so there is no clear pattern

implies that "Productions_Reports" is only one of several / many that are run on various schedules

It appears to me that a better example would be something like

 If Weekday(Now(), vbMonday) = 1 Or Weekday(Now(), vbMonday) = 3 Then Productions_Reports_1
 If Weekday(Now(), vbMonday) = 2 Or Weekday(Now(), vbMonday) = 3 Then Inventory_Reports_2
 If Weekday(Now(), vbMonday) = 4 Or Weekday(Now(), vbMonday) = 5 Then Sales_Reports_3
 If Weekday(Now(), vbMonday) = 3 Or Weekday(Now(), vbMonday) = 6 Or Weekday(Now(), vbMonday) = 1 Or Weekday(Now(), vbMonday) = 4 Then Warehouse_Reports_4

etc

IN MY OPINION >>> I think that a bunch of IF's that that are more difficult to maintain and to read <<< IN MY OPINION



One way to use arrays

Option Explicit

Sub UsingArray()

    Dim aReports(vbSunday To vbSaturday)
    Dim v As Variant

    aReports(vbMonday) = Array("Report1", "Report2")
    aReports(vbTuesday) = Array("Report1", "Report3")
    aReports(vbWednesday) = Array("Report2", "Report3")
    'etc

    For Each v In aReports(Weekday(Now))
        Application.Run v
    Next
End Sub

Sub Report1()
    MsgBox "Report 1"
End Sub
Sub Report2()
    MsgBox "Report 2"
End Sub
Sub Report3()
    MsgBox "Report 3"
End Sub
Sub Report4()
    MsgBox "Report 4"
End Sub
Sub Report5()
    MsgBox "Report 5"
End Sub