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