Compile error byref argument type mismatch - Исправление ошибок и поиск оптимальных решений проблем

I’m working with VBA. I wrote a user define function that takes a string, process it and return a cleaned string. I am not sure what is wrong with it. I am not able to call it and ask it to process my string and return it. I am thinking there are a mistake in the way I am defining or returning it.

Public Function ProcessString(input_string As String) As String
    ' The temp string used throughout the function
    Dim temp_string As String

    For i = 1 To Len(input_string)
        temp_string = Mid(input_string, i, 1)
        If temp_string Like "[A-Z, a-z, 0-9, :, -]" Then
            return_string = return_string & temp_string
        End If
    Next i
    return_string = Mid(return_string, 1, (Len(return_string) - 1))
    ProcessString = return_string & ", "
End Function

And I use this function like this

Worksheets(data_sheet).Range("C2").Value = ProcessString(last_name)

Last name is a string variable, usually looks like this Lastname*****, and I am trying to remove all the stars behind it. Have it return Lastname without the stars.

I received Compile error: ByRef arugment type mismatch when I tried to run this. I am using Windows XP with Office 2003.

EDIT: I added the basic struction of the code I have, I have about 20 lines of the similar code. Doing the same thing for each field I need.

Private Sub CommandButton2_Click()
' In my original production code I have a chain of these
' Like this Dim last_name, first_name, street, apt, city, state, zip As String
Dim last_name As String

' I get the last name from a fixed position of my file. Because I am 
' processing it from another source which I copied and pasted into excel
last_name = Mid(Range("A4").Value, 20, 13)

' Insert the data into the corresponding fields in the database worksheet
Worksheets(data_sheet).Range("C2").Value = ProcessString(last_name)

Источник

I am completely confused why I am getting a ByRef Argument Type Mismatch error during compile in VBA module ThisOutlookSession

I have a Sub (which used to be a function I could not get to work) which has two parameters. The Sub does a recursive search of all folders for all top level folders (the folders representing the email accounts/addresses) for a folder whose name is searched
and known. The Sub returns the folder as an object in one of the parameters. (Note this was tried as a function, but efforts to get it to work by returning the object were met with errors. Another commenter on the web suggested creating it as a Sub with the
return value put in one on the arguments; that worked)

Arg 1 takes a string which represents the name of any folder located within an «account» (top level folder representing email address).

Arg 2 is not set but is the parameter that returns the Folder object

The code extract below shows 4 Subs. The first Sub (which will be Application_Startup when ready) is the calling sub that produces the «type mismatch» error in the 2nd argument of the call to GetFolderFromName. The 2nd Sub is another procedure
that makes the call and actually works and was actually used to get the GetFolderFromName working. The 3rd Sub is the GetFolderFromName procedure itself: it immediately calls the procedure that does all the work to find the folder in a deep recursive
search and return it as an object.

The question is why the 1st sub produces the compiler error when the 2nd sub did not, despite all declarations seeming to be in order! The local variables are declared the same, and correctly. What is happening?

' The calling sub with the compile error

Sub TestStartCode()
    Dim mainAccount, reportFolder As folder
    Dim response As Integer

    mainAccount = Nothing
    ' check for top level folder, exit with message if not present
    Call GetFolderFromName(INCOMING_CORRESPONDENCE_ACCOUNT, mainAccount)
    If mainAccount Is Nothing Then
        MsgBox ("Account '" & INCOMING_CORRESPONDENCE_ACCOUNT & "' designated " & vbCrLf & _
        "for incoming correspondence not found on this Outlook installation" & vbCrLf & vbCrLf & _
        "Make sure you have this account installed to make use of the system")
        Exit Sub
    End If
 End Sub

' Working test code that makes same call without a compile type mismatch error

Sub TestFindFolderCode()
    Dim myFolderName As String
    Dim resultFolder As folder
    
    Let myFolderName = "Drafts"
    Call GetFolderFromName(myFolderName, resultFolder)
    If resultFolder Is Nothing Then
        Debug.Print "not found"
    Else
        Debug.Print "found!"
        Debug.Print "             Entry ID: " & resultFolder.EntryID
        Debug.Print "          Description: " & resultFolder.Description
        Debug.Print "          Folder Path: " & resultFolder.FolderPath
        Debug.Print "         Web View URL: " & resultFolder.WebViewURL
        Debug.Print "Default Message Class: " & resultFolder.DefaultMessageClass
    End If
End Sub

' The called Sub, showing its declaration and working code

Private Sub GetFolderFromName(targetFolderName As String, ByRef foundFolder As folder)
    Call ReadFoldersForTarget(targetFolderName, Application.GetNamespace("MAPI").Folders, foundFolder, -1)
End Sub

' The workhorse procedure that does the deep recursive search called by the wrapper Sub

' sub doubles as folder hiearchy lister if level is set to 0 on the call

Private Sub ReadFoldersForTarget(targetFolderName As String, _
            foldersCollection As Folders, ByRef foundTargetFolder As folder, ByRef level As Integer)
    Dim folder As folder
    Dim i As Integer
    Dim margin As String
    Dim searching As Boolean

    If level < 0 Then
        searching = True
    Else
        searching = False
    End If
    If searching = True Then
        For i = 0 To level
            margin = margin & "    "
        Next i
        Debug.Print margin & "|"
    End If
    For Each folder In foldersCollection
        If searching = True Then
            Debug.Print margin & "+--- " & folder.Name
            '& (EntryID: " & folder.EntryID & "), (StoreID: " & folder.StoreID & ")"
        Else
            Debug.Print "folder.name: " & folder.Name
            If InStr(folder.Name, targetFolderName) > 0 Then
                Set foundTargetFolder = folder
                Exit Sub
            End If
        End If
        If folder.Folders.Count > 0 Then
            Call ReadFoldersForTarget(targetFolderName, folder.Folders, foundTargetFolder, level + 1)
            If Not foundTargetFolder Is Nothing Then
                Exit Sub
            End If
        End If
    Next folder
    Set foundTargetFolder = Nothing
End Sub

Edited by

Saturday, February 10, 2018 4:48 AM
errant code block displaying

Источник

This article explains the error encountered using Excel VBA ByRef as “Argument Type Mismatch Error.” Before that, let me introduce you to “By Ref” first. Variables are key to any programming language, and VBA is not different either. We have seen many ways of declaring variables. One such way of declaring variables is by using the words “ByRef” and “ByValVBA ByVal or «By Value» is a statement that facilitates the user to reset the main value, i.e., by replacing it with another sub procedure in excel. It is a common practice when the user gets a down to 0 main procedure value.read more.”

ByRef Argument Type Mismatch in Excel VBA
- What Does ByRef Mean?
- Top 3 Reasons for VBA Byref Argument Type Mismatch
  - Error Reason #1 – Different Variable Names
  - Error Reason 2: Different Variable Data Types
  - Error Reason 3: Variable Data Types Missing in One Macro
- Things to Remember
- Recommended Articles

What Does ByRef Mean?

“ByRef” means “By Reference” using this word, we can pass arguments to procedures (for both sub & function) by reference. It is unlike its brother, “By Val,” which is not flexible but fixed in nature.

To understand this, let us look at the two macros below.

Code:

Sub Macro1()

    Dim A As Long

    A = 50

   Macro2 A

   MsgBox A
End Sub

Sub Macro2(ByRef A As Long)

    A = A * 10

End Sub

We have two subprocedures here named Macro1 and Macro2, respectively. To understand this better, run the macro line by line by pressing the F8 key.

Press the F8 key to capture the variable “A” value is 50.

The next line of code says “Macro2 A,” i.e., the name of the second macro, and “A” is the variable defined through the “By Ref” word.

As you can see above, the moment we execute the line of code “Macro2 A,” it has jumped to the next VBA sub procedureSUB in VBA is a procedure which contains all the code which automatically gives the statement of end sub and the middle portion is used for coding. Sub statement can be both public and private and the name of the subprocedure is mandatory in VBA.read more from the above procedure.

We can see the value of the variable “A” is 50 because we have used the word “ByRef” to declare the variable “A,” which is the same as in Macro1. Therefore, it has captured the value assigned to this variable “A” from the Macro1.

Now, in this macro (Macro2) equation says A = A * 10 i.e. A = 50 * 100. Press the F8 key three times to return to the macro above (Macro1).

Now, press the F8 key one more time the F8 key to see the value of variable “A” in the message box in VBAVBA MsgBox function is an output function which displays the generalized message provided by the developer. This statement has no arguments and the personalized messages in this function are written under the double quotes while for the values the variable reference is provided.read more.

The value says 500.

Even though the value we have assigned in this macro (Macro1) is 50 using the ByRef word, we triggered the Macro2 sub procedure retaining the value of variable “A” from Macro1. Then, we execute the value of A by multiplying 10.

Top 3 Reasons for VBA Byref Argument Type Mismatch

Above, we have seen how “ByRef” works, but we are bound to make mistakes that invariably result in throwing a VBA errorVBA error handling refers to troubleshooting various kinds of errors encountered while working with VBA. read more message as “ByRef Argument Type Mismatch.”

It can be due to many reasons. This section will show you how to rectify this error and debug the code.

You can download this VBA ByRef Argument Type Mismatch Excel Template here – VBA ByRef Argument Type Mismatch Excel Template

Error Reason #1 – Different Variable Names

One of the main reasons for getting this error in Excel VBA is that different variables have passed in two procedures. For example, look at the below codes.

Code:

Sub Macro1()

   Dim A As Long
   A = 50

   Macro2 B

   MsgBox A

End Sub

Sub Macro2(ByRef A As Long)

    B = B * 10

End Sub

In Macro1, we have used the “A” variable, and in Macro2, we have used the “B” variable. So, if you try to run the code, we will get a VBA Error as “ByRef Argument Type Mismatch.”

As you can see above, variable “B” gets highlighted because of the variable name type mismatch.

Solution: To overcome this issue, we must ensure that variable names in both procedures are exact.

Error Reason 2: Different Variable Data Types

Even though variable names are the same, it still causes an error. That is because of the data type we assign to them. For example, look at the below code.

Code:

Sub Macro1()

    Dim A As Integer
    A = 50

    Macro2 A
    MsgBox A

End Sub

Sub Macro2(ByRef A As Long)

    A = A * 10

End Sub

In the above codes, we have declared variable “A” as an Integer data type in Macro1. However, in Macro2, the same variable was assigned the data type “Long.”

When we run this code, it will cause a VBA error “ByRef Argument Type Mismatch.”

That is because we have assigned two different data types for the same variable name.

Solution: Data type should be the same in both the procedures.

Error Reason 3: Variable Data Types Missing in One Macro

The Excel VBA error, “ByRef Argument Type Mismatch,” could happen due to the data type assigned in one macro, not another macro.

Code:

Sub Macro1()

    A = 50

    Macro2 A
    MsgBox A

End Sub

Sub Macro2(ByRef A As Long)

    A = A * 10

End Sub

In the above code of Macro1, we have not declared any variable but assigned the variable value.

On the other hand, for Macro2, we have declared the variable “A” as long. So if you try running this code, it will cause the “ByRef Argument Type Mismatch” VBA error.

Solution1: The first solution is to declare the variable in both the procedures and assign the same data type to avoid these situations.

Solution2: An alternative solution is to make the variable declaration mandatory by adding the “Option Explicit” word at the top of the module.

What this will do is that before it shows VBA “ByRef Argument Type Mismatch,” Error, it asks us to declare the variable first.

So, Option ExplicitVBA option explicitly makes a user mandatory to declare all the variables before using them; any undefined variable will throw an error while coding execution. We can enable it for all codes from options to require variable declaration.read more always comes in handy in VBA.

Things to Remember

ByRef is the opposite of By Val.
ByRef carries the reference from one procedure to another.
The variable name and data type should be the same in both procedures.
Each variable needs to be declared separately in the case of multiple variables.

Recommended Articles

This article has been a guide to VBA ByRef Argument Type Mismatch. Here, we discuss the top 3 reasons for the ByRef argument type mismatch error with their solution in Excel VBA. You can learn more about VBA from the following articles: –

Goal Seek in VBA
VBA Index Match Function
Overflow Error in Excel VBA
1004 Error in Excel VBA

Источник

azma

Пользователь

Сообщений: 314
Регистрация: 03.12.2017

29.06.2020 12:56:07

Добрый день, уважаемый форумчани!
ниже пользовательская функция, которая ругается на Ошибку: Compile error: ByRef argument type mismatch

Код

Function GETLASTWORD(ByVal Text As String, Optional Separator As Variant)

Dim lastword As String

If IsMissing(Separator) Then
 Separator = " "
End If
lastword = StrReverse(Text)
lastword = Left(lastword, InStr(1, lastword, Separator, vbTextCompare))

GETLASTWORD = StrReverse(replace(lastword, Separator, ""))

End Function

как можно от этого избавиться?
функция в отделенной книге работает без проблем, а я добавил её в мою надстройку и начала ругаться.

Прикрепленные файлы

Screen Capture #001.png (41.66 КБ)

sokol92

Пользователь

Сообщений: 4429
Регистрация: 10.09.2017

Один из возможных вариантов — в Вашей надстройке переопределена функция Replace. Если это так, то подобного рода вещи (называть свои функции так же, как стандартные) не желательны. Замена в приведенном выше примере Replace на VBA.Replace может помочь.

azma Пользователь Сообщений: 314 Регистрация: 03.12.2017	sokol92, Спасибо большое!

Jerry.Sweer

Пользователь

Сообщений: 60
Регистрация: 11.01.2017

21.10.2020 18:20:07

Друзья помогите с той же проблемой.
Ниже вписан код. При использовании в коде функции выдает ошибку «ByRef argument type mismatch»
Как лечить?

Благодарен заранее.

Код

Public Function Equalist(t1 As String, t2 As String) As Variant
    Dim h As Long, l As Long
        For h = Len(t1) To 1 Step -1
            For l = 1 To Len(t1) - h + 1
                If t2 Like "*" & Mid(t1, l, h) & "*" Then
                    If h > Equalist Then
                        Equalist = h
                        Exit Function
                    End If
                End If
            Next
        Next
End Function

__________________________________________________________________________________________________________________________

Код

Public Sub Test4()


Dim i As Long, w As Long, ob As Long, LR As Long, c As Long
Dim Line1 As Object
Dim t As Single
Dim j As Worksheet
Dim NomerDokumenta As Integer, KodTovara As Integer, Naimenovanie As Integer, RaznicaKolvo _
As Integer, RaznicaGrn As Integer, Hozoperaciya As Integer

't = Timer

LR = Cells(Rows.Count, 1).End(xlUp).Row

ReDim B(1 To LR), D(1 To LR), E(1 To LR), R(1 To LR), S(1 To LR), U(1 To LR) As Variant

Set j = Sheets(3)

NomerDokumenta = j.Cells.Find("№ документа").Column
KodTovara = j.Cells.Find("Код товара").Column
Naimenovanie = j.Cells.Find("Наименование товара").Column
RaznicaKolvo = j.Cells.Find("Разница, кол-во").Column
RaznicaGrn = j.Cells.Find("Разница, грн").Column
Hozoperaciya = j.Cells.Find("Хоз.операция").Column

For c = 1 To 3
    ' Первый заход
    For i = 2 To LR
        B(i) = j.Cells(i, NomerDokumenta)
        D(i) = j.Cells(i, KodTovara)
        E(i) = j.Cells(i, Naimenovanie)
        R(i) = j.Cells(i, RaznicaKolvo)
        S(i) = j.Cells(i, RaznicaGrn)
        U(i) = j.Cells(i, Hozoperaciya)
    Next i
    
    For w = 2 To UBound(B())
        For i = 2 To UBound(B())
            If IsEmpty(j.Cells(w, 30)) And IsEmpty(j.Cells(i, 30)) Then
                If Equalist(E(w), E(i)) > 20 Then
                    If R(w) < 0 And R(i) > 0 Then
                        If Abs(j.Cells(w, 18)) > Abs(j.Cells(i, 18)) Then
                            If Abs(Abs(S(w) / R(w)) - Abs(S(i) / R(i))) < 20 Then
                                If w < i Then
                                    j.Rows(w + 1).Insert Shift:=xlDown
                                    j.Rows(w + 1).FillDown
                                    j.Cells(w + 1, 18) = -j.Cells(i + 1, 18)
                                    j.Cells(w, 18) = j.Cells(w, 18) + j.Cells(i + 1, 18)
                                    j.Cells(w + 1, 30) = "Четвертый круг 1_" & w
                                    j.Cells(i + 1, 30) = "Четвертый круг 1_" & w
                                        LR = Cells(Rows.Count, 1).End(xlUp).Row
                                            ReDim B(1 To LR), D(1 To LR), E(1 To LR), R(1 To LR), S(1 To LR), U(1 To LR) As Variant
                                                For ob = 2 To LR
                                                    B(ob) = j.Cells(ob, NomerDokumenta)
                                                    D(ob) = j.Cells(ob, KodTovara)
                                                    E(ob) = j.Cells(ob, Naimenovanie)
                                                    R(ob) = j.Cells(ob, RaznicaKolvo)
                                                    S(ob) = j.Cells(ob, RaznicaGrn)
                                                    U(ob) = j.Cells(ob, Hozoperaciya)
                                                Next ob
                                End If
                            End If
                        End If
                    End If
                End If
            End If
        Next i
    Next w

End Sub

Изменено: Jerry.Sweer — 21.10.2020 22:40:40

Дмитрий(The_Prist) Щербаков

Пользователь

Сообщений: 13995
Регистрация: 15.09.2012

Профессиональная разработка приложений для MS Office

21.10.2020 18:37:08

Коды ОЧЕНЬ желательно оформлять соответствующим тегом(кнопка <…> в панели редактора сообщений). Без оформления тегами очень неудобно коды читать.
Ошибка банальная — у Вас массивы имеют тип VAriant, а функция Equalist требует тип String. Посему исправляется очень просто:

Код
If Equalist(Cstr(E(w)), Cstr(E(i))) > 20 Then

Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы…

Jerry.Sweer Пользователь Сообщений: 60 Регистрация: 11.01.2017	#6 21.10.2020 22:48:52 Спасибо большое.

Источник

In this article, we will look at the ByRef argument type mismatch error. First, let us have a look at what is ByRef and the difference between ByRef and ByVal.

In Visual Basic, you can pass an argument to a procedure or function by value or by reference. This is known as the passing mechanism, and it determines whether the procedure or function can modify the variable that has been passed. The declaration of the function determines the passing mechanism for each parameter by specifying the ByVal or ByRef keyword.

The advantage of passing an argument ByRef is that the procedure can return a value to the calling code through that argument. The advantage of passing an argument ByVal is that it protects a variable from being changed by the procedure. The default in VBA is to pass arguments by reference. For more details, you can refer to the article here.

Whenever an argument is passed ByRef (by reference), the default, you must match the precise data type in the argument passed and the argument in the function definition. So, let us have a look at some of the most likely causes of the error and how to fix them:

Contents

Case 1: You passed an argument of one type that could not be forced to the type expected.
- Solution 1:
- Solution 2:
- Solution 3:
- Solution 4:
Case 2: You have not declared the variable in the calling sub.
Case 3: Variable is declared but the data type is not specified

Case 1: You passed an argument of one type that could not be forced to the type expected.

For example, this error occurs if you try to pass an Integer variable when a Long is expected.

Sub callByRef()

Dim amount As Integer

amount = 80
Call processAmt(amount)

'Rest of the code

End Sub

Public Function processAmt(amount As Long)
'Do your processing here
End Function

The error that you will get is:

Let us look at possible solutions to this:

Solution 1:

If possible, always try to match the data type. This is easiest and quickest solution. So, in our case, the amount variable should be integer (or long, based on the requirement) in both the places.

Dim amount As Long


Solution 2:
Force coercion to occur by passing the argument in its own set of parentheses (even if it causes information to be lost).
Sub callByRef()

Dim amount As Integer

amount = 80

Call processAmt((amount))

End Sub


Placing the argument in its own set of parentheses the fractional portion of the number is rounded to make it conform to the expected argument type. The result of the evaluation is placed in a temporary location and the original amount retains its value.
Solution 3:
Similar to the above solution, you can explicitly convert the data type of the variable while passing
Sub callByRef()

Dim amount As Integer

 amount = 80

 Call processAmt(CLng(amount))

 End Sub


Solution 4:
Pass the variable by value. For that, specify the keyword “ByVal” before the variable name in the function definition.
Public Function processAmt(ByVal amount As Long)
'Do your processing here
End Function


Case 2: You have not declared the variable in the calling sub.
Sub callByRef()

amount = 80

Call processAmt(amount)

End Sub

Public Function processAmt(amount As Long)
'Do your processing here
End Function


Here you need to make sure that the variable is defined along with the data type



  


                       
  




Dim amount As Long


As a standard practice, you should always have “Option Explicit” added to your code as the first line. This makes declaring variables mandatory and helps preventing many errors. Here is an example of where to place it
Option Explicit
Sub callByRef()

'Code here
End Sub


Case 3: Variable is declared but the data type is not specified
Dim firstName


In VB, if you declare a variable without specifying the data type, by default the variable is assigned the type “variant”. So, you need to explicitly specify the variable data type before passing it to a function.
Another common source of error here is when you declare multiple variables on a single line and specify the data type only for the last one. For example,
Dim firstName, lastName As String


Here, VBA sets lastName as a String. However, for firstName it is a variant data type and not String. This is because in VBA, you have to specify data type for each and every variable separately (even if the variables are declared on the same line).
So, the above statement is equivalent to:
Dim firstName As Variant, lastName As String


And the right way to declare the variable is
 Dim firstName As String, lastName As String 
So, to summarize, the argument data type should always be same while calling a function and in the function definition. If you get this error, you can fix it by using one of following methods

Make sure the variable is defined with correct data type
Use explicit conversion while passing the argument
Use an own set of parentheses while passing the argument
Pass the variable by value if you do not intend to change the value in the called function

Источник

This repository has been archived by the owner on Aug 31, 2018. It is now read-only.

Permalink

Cannot retrieve contributors at this time

title	keywords	f1_keywords	ms.prod	ms.assetid	ms.date
ByRef argument type mismatch	vblr6.chm1011308	vblr6.chm1011308	office	6adca657-8620-e3f1-3587-e317f988979c	06/08/2017

An argument passed ByRef (by reference), the default, must have the precise data type expected in theprocedure. This error has the following cause and solution:

You passed an argument of one type that could not be coerced to the type expected.

For example, this error occurs if you try to pass an Integer variable when a Long is expected. If you want coercion to occur, even if it causes information to be lost, you can pass the argument in its own set of parentheses. For example, to pass the Variant argument MyVar to a procedure that expects an Integer argument, you can write the call as follows:

Dim MyVar 
MyVar = 3.1415 
Call SomeSub((MyVar)) 
 
Sub SomeSub (MyNum As Integer) 
MyNum = MyNum + MyNum 
End Sub

Placing the argument in its own set of parentheses forces evaluation of it as an [expression](vbe-glossary.md). During this evaluation, the fractional portion of the number is rounded (not truncated) to make it conform to the expected argument type. The result of the evaluation is placed in a temporary location, and a reference to the temporary location is received by the procedure. Thus, the original  `MyVar` retains its value.

 **Note**  If you don't specify a type for a [variable](vbe-glossary.md), the variable receives the default type,  **Variant**. This isn't always obvious. For example, the following code declares two variables, the first, `MyVar`, is a  **Variant**; the second, `AnotherVar`, is an  **Integer**.

Dim MyVar, AnotherVar As Integer

For additional information, select the item in question and press F1 (in Windows) or HELP (on the Macintosh).

Источник

Автор postrelll, 25 марта 2016, 18:00

Уважаемые форумчане, буду благодарен за подсказку в решении проблемки, причину которой я не понимаю.

Есть основная процедура в которой определен тип одной переменной, которая передаем имя открытой книги
————
Sub analyse()
‘так определена переменная
Dim book_with_source As String
…
‘так эта переменная в основной программе получает своё значение
book_with_source = ActiveWorkbook.name
End Sub
————

Далее есть процедура в которую я передаю эту переменную
————
Private Sub IndividualReportFormer(book_with_source As String, _
Optional book_with_report As String, _
Optional sheet_of_source As Integer, _
Optionalsource_letter As String, _
Optional prefix_type As String)
————

Далее вот так из основной программы я обращаюсь к вышеприведенной процедуре
————
IndividualReportFormer book_with_sourse
————

Однако при выполнении я получаю:
Compile error: ByRef argument type mismatch
и выделяется вот эта переменная — book_with_sourse.

И я не понимаю, почему несовпадение типов? Ведь в обоих случаях четко определено, что переменная строковая.

Администратор

Administrator
Сообщения: 2,206
Записан

Сделайте вот так в VBA:
Tools — Options… — поставьте галочку «Require Variable Declaration».
Теперь у вас в новых модулях сверху будет появляться «Option Explicit». Этот параметр будет проверять, создали (в справках вместо термина «создали» используется термин «объявили») ли вы переменную.

Сейчас в уже имеющемся модуле в самом верху вставьте этот текст:
Option Explicit
и запустите макрос. VBA выдаст сообщение, что у вас не создана переменная.

То есть вы передаёте в процедуру переменную, которую не создали. Здесь:
IndividualReportFormer book_with_sourse

происходит два действия:
1) создаётся переменная «book_with_sourse» и 2) эта переменная отправляется в процедуру. При этом у переменной «book_with_sourse» будет тип данных «Variant» (этот тип данных присваивается автоматически, если явно не задавать тип данных). Из-за этого вы и видите ошибку «несоответствие типов», т.к. у переменной тип данных «Variant», а у аргумента процедуры — «String».

Спасибо! Помогло, не обратил внимания, что опечатка была в sourSe вместо sourCe.

Форум по VBA, Excel и Word
►
VBA, Excel
►
VBA, макросы в Excel
►
Excel: Странная проблема при обработке процедуры — ByRef argument type mismatch

Источник

Table of contents

What Does ByRef Mean?

Top 3 Reasons for VBA Byref Argument Type Mismatch

Error Reason #1 – Different Variable Names

Error Reason 2: Different Variable Data Types

Error Reason 3: Variable Data Types Missing in One Macro

Things to Remember

Recommended Articles

Case 1: You passed an argument of one type that could not be forced to the type expected.

Solution 1:

Solution 2:

Solution 3:

Solution 4:

Case 2: You have not declared the variable in the calling sub.

Case 3: Variable is declared but the data type is not specified

Администратор