-
Oct 14th, 2019, 09:08 AM
#1
Thread Starter
Fanatic Member
[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.
-
Oct 14th, 2019, 09:21 AM
#2
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
-
Oct 14th, 2019, 09:25 AM
#3
Thread Starter
Fanatic Member
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.
-
Oct 14th, 2019, 09:41 AM
#4
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?
-
Oct 14th, 2019, 09:51 AM
#5
Thread Starter
Fanatic Member
Re: ByRef argument error when calling function
Well spotted, and thanks very much. The "typo" was the cause. Works now.
Thanks again.
-
Oct 14th, 2019, 01:55 PM
#6
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|