Results 1 to 6 of 6

Thread: [RESOLVED] ByRef argument error when calling function

  1. #1

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2018
    Posts
    514

    Resolved [RESOLVED] ByRef argument error when calling function

    Hi
    I am not sure how I did to mange tot get this error but here I am. When calling "GetExpiration", the compiler throws and error saying ByRef type mismatch.

    What is it I am not doing right here?

    Code:
    Sub CalculateExpirationDate(ThisWorkBook As Workbook)
    
    Dim RefVariance() As String
    Dim Trows As Integer
    Dim Expiration As Integer
    
    RefVariance = Split("A,M,U,MI", ",")
    
      For m = LBound(RefVariance) To UBound(RefVariance)
      Trows = ThisWorkBook.Worksheets(RefVariance(m)).Range("A" & Rows.Count).End(xlUp).Row
       If Trows > 1 Then
        Expiration = GetExpiration(ThisWorkBook, RefVariance(m))
        ThisWorkBook.Worksheets(RefVariance(m)).Range("E2").Formula = "=D2+ Expiration*365"
        If Trows > 2 Then
         ThisWorkBook.Worksheets(RefVariance(m)).Range("E2", "E" & Trows).FillDown
        End If
       End If
      Next m
    
    End Sub
    
    Function GetExpiration(ThisWorkBook As Workbook, VariantName As String) As Integer
    
    Dim ReturnValue As Integer
    Dim Numberofdays As Integer
    
    For m = 0 To ThisWorkBook.Worksheets("InfoSheet").Range("C" & Rows.Count).End(xlUp).Row
     If ThisWorkBook.Worksheets("InfoSheet").Range("C" & m).Value = VariantName Then
        ReturnValue = CInt(ThisWorkBook.Worksheets("InfoSheet").Range("D" & m).Value)
      Exit For
     End If
    Next m
    
    GetExpiration ReturnValue
    
    End Function
    And these are the items in "InfoSheet" column C and D:

    C:
    Items
    FileName:
    FilePath:
    Number of days:
    A
    U
    M
    MI

    D:
    Value
    List.xlsx
    \\FSDKHQ001\Users405$\KRLM\Excel projects\RP- tracker\Test files\
    180
    3
    5
    3
    3


    Thanks for any help.

  2. #2
    VB-aholic & Lovin' It LaVolpe's Avatar
    Join Date
    Oct 2007
    Location
    Beside Waldo
    Posts
    19,541

    Re: ByRef argument error when calling function

    Others with far more experience using Office automation than I will chime in. Until they do, do you have multiple project references to different versions of MS Office? If so, that could be the problem
    Insomnia is just a byproduct of, "It can't be done"

    Classics Enthusiast? Here's my 1969 Mustang Mach I Fastback. Her sister '67 Coupe has been adopted

    Newbie? Novice? Bored? Spend a few minutes browsing the FAQ section of the forum.
    Read the HitchHiker's Guide to Getting Help on the Forums.
    Here is the list of TAGs you can use to format your posts
    Here are VB6 Help Files online


    {Alpha Image Control} {Memory Leak FAQ} {Unicode Open/Save Dialog} {Resource Image Viewer/Extractor}
    {VB and DPI Tutorial} {Manifest Creator} {UserControl Button Template} {stdPicture Render Usage}

  3. #3

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2018
    Posts
    514

    Re: ByRef argument error when calling function

    I had different versions of Excel open at the time of getting that error, but the code above was strictly referring to specific workbook with one single version of Excel.
    But when I check, in fact I have different projects with different versions of Excel; but I am not using them.
    Did I understand you correctly?
    Last edited by Grand; Oct 14th, 2019 at 09:32 AM.

  4. #4
    VB-aholic & Lovin' It LaVolpe's Avatar
    Join Date
    Oct 2007
    Location
    Beside Waldo
    Posts
    19,541

    Re: ByRef argument error when calling function

    I was referring to Project References, the checked items that are included with your project.

    Oh, is this code in Excel's VBA or is this a VB6 project? I may have been misinterpreting this from the git-go.

    Also, if you scroll all the way to the bottom of your posted code, is this line a typo:
    Code:
    GetExpiration ReturnValue
    Shouldn't it be: GetExpiration = ReturnValue
    If so, that is likely the reason for the error?
    Insomnia is just a byproduct of, "It can't be done"

    Classics Enthusiast? Here's my 1969 Mustang Mach I Fastback. Her sister '67 Coupe has been adopted

    Newbie? Novice? Bored? Spend a few minutes browsing the FAQ section of the forum.
    Read the HitchHiker's Guide to Getting Help on the Forums.
    Here is the list of TAGs you can use to format your posts
    Here are VB6 Help Files online


    {Alpha Image Control} {Memory Leak FAQ} {Unicode Open/Save Dialog} {Resource Image Viewer/Extractor}
    {VB and DPI Tutorial} {Manifest Creator} {UserControl Button Template} {stdPicture Render Usage}

  5. #5

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2018
    Posts
    514

    Re: ByRef argument error when calling function

    Well spotted, and thanks very much. The "typo" was the cause. Works now.
    Thanks again.

  6. #6
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    9,853

    Re: [RESOLVED] ByRef argument error when calling function

    I just looked at it, and didn't see a problem with the Office automation objects. What I saw was the unusual use of the m variable. For one, I couldn't see where it was declared (which suggest you may not have Option Explicit on, tsk tsk). And secondly, depending where we are in the code, you're using it as an integer sometimes, and a string at others. I'm not sure that was your problem, but it sure seems that that needs to be cleaned up.
    Any software I post in these forums written by me is provided "AS IS" without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. To all, peace and happiness.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width