Solved

VBA Error - checking if date is weekend

Posted on 2010-09-20
20
999 Views
Last Modified: 2012-06-21
Hi experts

What am I doing wrong with my code below I am getting an error on the line
IsWeekend(StartDate"). It is a ByRef Type Mismatch error.

I can't see what the problem would be - the Start Date is a date




Dim tbl As Table

    Dim rw As Row

    Dim cl As Cell

    Dim c As Integer

    Dim iStartCol As Integer

    

    Dim StartDate, EndDate, MyDate As Date

    StartDate = CDate(ActiveDocument.CustomDocumentProperties.Item("xBeginDate"))

    EndDate = CDate(ActiveDocument.CustomDocumentProperties.Item("xEndDate"))

   

    iStartCol = 1

    

    Set tbl = ActiveDocument.Tables(1)

    

    'Add rows for each day from StartDate to EndDate

        

    Do While StartDate <= EndDate

    

        'if date is a weekend then do nothing



        If IsWeekend(StartDate) = True Then



        Else

        

            Set rw = tbl.Rows.Add

            c = iStartCol

            rw.Cells(c).Range.Text = Format(StartDate, "dddd, d mmmm")

            

        End If

        

        StartDate = StartDate + 1

        

    Loop

        

      

        



End Sub





Public Function IsWeekend(InputDate As Date) As Boolean

    Select Case Weekday(InputDate)

        Case vbSaturday, vbSunday

            IsWeekend = True

        Case Else

            IsWeekend = False

    End Select

End Function

Open in new window

0
Comment
Question by:Fi69
  • 11
  • 5
  • 3
  • +1
20 Comments
 
LVL 80

Accepted Solution

by:
byundt earned 84 total points
ID: 33721988
In VBA, each variable must be declared as something. Variables not declared as something will be variants.
Dim StartDate, EndDate, MyDate As Date     'MyDate is a Date, but StartDate and EndDate are Variant

Other languages let you declare an entire list as something, but not VBA. I suspect this is the source of your type mismatch.
0
 
LVL 58

Assisted Solution

by:cyberkiwi
cyberkiwi earned 83 total points
ID: 33721996
Change

    Dim StartDate, EndDate, MyDate As Date

to

    Dim StartDate As Date, EndDate As Date, MyDate As Date
0
 
LVL 65

Assisted Solution

by:rockiroads
rockiroads earned 83 total points
ID: 33722005
If you do not specify the pass by type it takes it as by reference.
Change your method to pass by value instead

Public Function IsWeekend(byval InputDate As Date) As Boolean
0
 

Author Comment

by:Fi69
ID: 33722022
Hi Rockiroads

I actually fixed the MyDate as per first two suggestion and it works. Just wondering is it necessary to change the function as you've described (ie is it better code writing practice) or just leave it?
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 33722079
good practice to define the datatypes. I didn't mention it because byundt already mentioned it. However if u stick with variants then change the method. Hence suggesting the alternative. Give it a try, change your code back to how it was and tell me if it makes a difference or not.

I personally find it good practice to specify the pass by type. Byref or nothing set will mean that value can change so will be passed back to the caller. byval means it wont. Thats the major difference between passing by value and passing by reference. I only pass by reference if I need the change reported back to the caller otherwise I always state byval
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 33722103
eg
Public Sub Testit1(ByRef x As Integer)
    x = 20
End Sub

Public Sub Testit2(ByVal x As Integer)
    x = 21
End Sub

if u pass a variable to test1, that value will now have a value of 20
if u pass a variable to test2, that value will retain the original value regardless of x changing to 21

therefore I think its always safer to use byval

others may disagree but these are the practices I work with
0
 
LVL 80

Expert Comment

by:byundt
ID: 33722146
rockiroads,
I had never understood the distinction between ByVal and ByRef, so thanks for explaining it.

I tested your assertion using a Sub calling a Function. Just as you state, the value in the sub was not changed when it was passed to the function as a ByVal parameter. But it could be changed when the function parameter was passed ByRef.

Being on a roll, I then looked at Excel VBA event subs, which use ByVal for their parameters. The code in the snippet below allowed me to change the cell value--contrary to my understanding of your explanation.

Could you clarify?

Brad
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Target = 3

End Sub

Open in new window

0
 
LVL 65

Expert Comment

by:rockiroads
ID: 33722191
wow, didnt know that byundt. That would be strange why excel would do that but access does not.Access behaves like how the pass by types work in other languages

I wonder if it has anything to do with the types. changing a range has changed the excel sheet so it has to keep that value.
So excel datatypes like Range are changed no matter what.

I checked passing in non excel datatypes and it works how it should.
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 33722198
I will need to try find this out for sure but that would be my educated guess.
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 33722222
byundt, I do not know if you would understand the following, I expect you to do but its like this

byref is passing a pointer to that variable, so your passing in the memory address. So whatever you change you are changing the source so anybody accessing after will get the updated changes
byval is passing a value of that variable. Its like a copy of the memory address. So changing it only changes the copy. By the time you get back to the caller, that copy has gone.

I now think that dealing with excel objects is always going to be by reference on the default event handlers because you are dealing with the values on the excel sheet. This is different to that of a variable.

Will have to check on msdn or something to find out for sure.
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 80

Expert Comment

by:byundt
ID: 33722255
Pointers I understand. They were used heavily in PL/1 many a long year ago, in my tech school by the sea.

If Microsoft developers were going to override the intent of ByVal for Excel event subs, why would they go out of their way to insist on it? Unless they were just meaning that you can't use the event sub to turn cell A1 into B5.

Brad
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 33722302
Further testing seems to indicate its not the methods but the data type. So why would an excel data type be treated differently. Obviously for some reason, maybe like I said it changes the physical appearance.

Private Sub ChangeRange(ByVal Target As Range)
    Target = 3
    Debug.Print "Changing to", Target
End Sub

Public Sub TestChangeRange()
    Dim r As Range
   
    Set r = Range(Cells(1, 1), Cells(1, 1))
    r = 15
    Debug.Print "Before", r
    ChangeRange r
    Debug.Print "After", r
End Sub

Changes the target.
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33723168
Consider these examples to see if you understand.

Set R = Range("A1")
Set R2 = R
R2.Value = 5
MsgBox R.Value ' this shows 5  ---(1)
MsgBox R2.Value ' this shows 5, same, really  ---(2)
Set R2 = Range("A2")
R.Value = 4 ' A1 content becomes 4
R2.Value = 6 ' A2 content becomes 6, i.e. does not affect R  ---- (3)

Explanation.  When R2 is set to R, we are really "pointing" R2 to the same object as R.  When you change the range R2, it also affects the range R.  Yet when R2 is set to something else, they are again different.  At no point did R and R2 get merged, they are just 2 object/range "pointer"s.

A = 1
B = A
A = 2
[A1] = A  ' A1 becomes 2
[A2] = B  ' A2 becomes 1

When we say B = A, all it does is copy the value of A to B.  It does not make A equal to (and forever follow) B. When A is changed to 2, B is still 1.


When you pass ByVal, the above is what happens, for value types it copies the value and for object types, the pointer to the object is copied.  Example

       Private Sub SetRangeValue(ByVal Target As Range)
       Target = 3
       Set Target =Target.Offset(1)
       Target = 4
       End Sub

If you call it like this

Sub Test
       Set R = Range("B3")
       SetRangeValue(R)
       R.Value = 5 ' R is unaffected
End Sub

This is pseudo-code for what it expands out to

       Set R = Range("B3")
       Set Target = R
       Target = 3   ' sets B3
       Set Target = Target.Offset(1)
       Target = 4   ' set B4
       R.Value = 5   ' set B3

When you pass ByRef, what you are doing is something less easily explained.  It means you are working on the object itself, not a copy.  If the previous example had been ByRef, then it expands like this instead.

       Private Sub SetRangeValue(ByRef Target As Range)
       Target = 3
       Set Target =Target.Offset(1)
       Target = 4
       End Sub

Sub Test
Set R = Range("B3")
       SetRangeValue(R)
       R.Value = 5  ' R itself is modified by the call
End Sub

This is pseudo-code for what it expands out to

       Set R = Range("B3")
       R = 3   ' sets B3  -- using R itself
       Set R = R.Offset(1)
       R = 4   ' set B4
       R.Value = 5    ' sets B4 !

Hope that helps.
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 33726531
Brad, there is an exception to the rule when passing args in. Arrays are always passed by reference. The range object is really an array hence it works like byref. But why MS left it as byval when it should clearly be byref I do not know.
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33728999
@rockiroads,

Maybe I did a bad job at explaining.  Have a look at the attached sheet for how ByRef and ByVal are different, even for arrays.  There is nothing wrong with how it is implemented by MS.

In a sheet, put the number 10 in C10, and the number 15 in C15 and 15 in J2.
Then run the Sub TestAll below on the sheet.
Sub SetToCellContaining10(ByRef r As Range)

Set r = Cells.Find(What:=10, After:=[A1], LookIn:=xlValues)

End Sub



Sub PrintAddressOfCellContaining15(ByVal r As Range)

Set r = Cells.Find(What:=15, After:=[A1], LookIn:=xlValues)

[A10] = r.Address

End Sub



Sub TestAll()

Dim aRange As Range

Set aRange = Cells(2, 2)     ' aRange is $B$2

[A1] = aRange.Address

SetToCellContaining10 aRange

[A2] = aRange.Address ' aRange is now $J$2



PrintAddressOfCellContaining15 aRange

[A3] = aRange.Address ' UNCHANGED, aRange is now $J$2

End Sub

Open in new window

ByRef-vs-ByVal.xls
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 33729210
howdo cyberkiwi, I get what you mean and ran your test and it seems to work as expected

Can u run this test and tell me what you get before and after?
output I get is

Before         15
Changing to    3
After          3

Note the use of byval



Private Sub ChangeRange(ByVal Target As Range)
    Target = 3
    Debug.Print "Changing to", Target
End Sub

Public Sub TestChangeRange()
    Dim r As Range
   
    Set r = Range(Cells(1, 1), Cells(1, 1))
    r = 15
    Debug.Print "Before", r
    ChangeRange r
    Debug.Print "After", r
End Sub
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33729307
@rockiroads

You may still be missing the point.  ByVal and ByRef refer to whether the VARIABLE "r" that is passed in changes.  Not the content of the range referred to by the VARIABLE "r".

Check these two branches of pseudo-code


      Set R = Range("B3")
------------- the portion below this line runs in the SUB ByVal ------
      Set Target = R         ''' BYVAL = we are working on a "copy" of the variable "R" as a separate variable
      Target = 3   ' sets B3
      Set Target = Target.Offset(1)    ''' THE DISTINCTION IS HERE.  This line in ByVal sub doesn't change R
      Target = 4   ' set B4
------------- the portion above this line runs in the SUB ByVal ------
      R.Value = 5    ' set B3


      Set R = Range("B3")
------------- the portion below this line runs in the SUB ByRef ------
      R = 3   ' sets B3     ''' BYREF = we are working on the variable "R" itself
      Set R = R.Offset(1)    ''' THE DISTINCTION IS HERE.  This line in ByRef sub changes R itself from the caller
      R = 4   ' set B4
------------- the portion above this line runs in the SUB ByRef ------
      R.Value = 5    ' sets B4 !

In both cases, whether Target = 3 or R = 3, the value of the same range is updated, as expected.
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 33729537
I know what you mean but maybe I am missing your point. Maybe we are talking the same thing but seeing and talking differently. I am not talking about making a copy of the passed in variable (like set Target = R). But the variable itself. I know the differences as well between byval and byref.

But it was Brad's question that got me. If the value inside the procedure did change and change was returned back then why would the function prototype be left as byval.

So the method has byval defined but if that method changes it, that updated value gets returned. You would expect this of byref not byval. But because Range is essentially an array of sorts this is why I believe that modified value gets returned.

eg

Private Sub ChangeRange(ByVal Target As Range)
    Target = 3
    Debug.Print "Changing to", Target
End Sub

byval has it defined as Target
I updated the value
The caller sees the updated value instead of the original value

You would expect to see the original value if it was like an int
Private Sub ChangeRange(ByVal Target As integer)
    Target = 3
    Debug.Print "Changing to", Target
End Sub


Now if u created an array, Excel forces you to use byref. This is because arrays work only by reference.

So why is there a difference then?

Maybe Im missing your point. Try it with an example that isnt range.
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33729689
@rockiroads

byval has it defined as Target.  I updated the valueThe caller sees the updated value instead of the original valueYou would expect to see the original value if it was like an int

I think these three lines are where the misconception lies.

[byval has it defined as Target.  I updated the value]
You updated a member of the range, in effect Target.Value = 3.  The variable Target itself is not modified.
If you used "Set Target = Target.Offset(1)", then you are updating the variable Target itself (to point to one cell below).  The fact that Excel lets you write Target = 3 may be what confused you, the "Value" property/member is the default.

[The caller sees the updated value instead of the original value]
The caller sees the effect of updating a member of the variable "Target", set to some Range.  This statement would make more sense if you said:
Because it is ByVal, the caller does not see changes to the variable "R" passed in and recognized inside the Sub as "Target", when you set Target using "Set Target = Target.Offset(1)".  If you passed it ByRef, then the changes to Target (the variable itself) is reflected back to the caller.

[You would expect to see the original value if it was like an int]
It is not an int.  An int is a basic datatype and has no members.  So when you update an int, you are updating the value of the variable itself, not a member of.

For the record, you can pass arrays as Val or Ref as well.  In the ByRef version below, A is being resized from within the Sub.  In the ByVal version, the resize inside the Sub has no effect outside of the Sub

Sub ByValArray(ByVal A As Variant)

MsgBox A(1)

ReDim A(10)

End Sub



Sub ByRefArray(ByRef A As Variant)

MsgBox A(1)

ReDim A(10)

End Sub



Sub TestArrayByValByRef()

Dim A As Variant

ReDim A(5)

A(1) = 4

ByValArray A

MsgBox UBound(A)

ByRefArray A

MsgBox UBound(A)

End Sub

Open in new window

0
 
LVL 65

Expert Comment

by:rockiroads
ID: 33730450
Hi Cyberkiwi, yea I understood. I guess what threw me and what I kept thinking about was byval. Excel forces you to use byref for arrays, udts etc. But for its own methods it allows byval. I was looking at it at that angle trying to understand that.
Think range along the same lines and you would expect it to follow the same rules.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

I'm writing to share my clumsy experience in using this elegant tool so you can avoid every stupid mistake I made. (I leave it to the authorities to decide if this deserves a place in the Knowledge archives.)  Now that I am on the other side of my l…
This is written from a 'VBA for MS Word' perspective, but I am sure it applies to most other MS Office components where VBA is used.  One thing that really bugs me is slow code, ESPECIALLY when it's mine!  In programming there are so many ways to…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In this video, we show how to convert an image-only PDF file into a PDF Searchable Image file, that is, a file with both the image (typically from scanning) and text, which is created in an automated fashion with Optical Character Recognition (OCR) …

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now