Compile error byref argument type mismatch vba



Несоответствие типа аргумента ByRef

Аргумент, передаваемый ByRef (по ссылке), значение по умолчанию, должен иметь точный тип данных, ожидаемый в процедуре. Эта ошибка имеет следующие причину и решение:

Был передан аргумент с типом, не приведенный к ожидавшемуся типу.

Например, эта ошибка происходит при попытке передать переменную типа Integer, когда ожидался тип Long. Если требуется, чтобы произошло приведение, даже когда это связано с потерей информации, можно передать непосредственно сам аргумент, заключив его в скобки.

Например, чтобы передать аргумент типа Variant MyVar процедуре, ожидающей аргумент типа Integer, соответствующий вызов можно записать следующим образом:

Размещение непосредственно самого аргумента в скобках принудительным образом оценивает его как выражение. Во время этого оценивания дробная часть числа округляется (не посредством усечения), чтобы обеспечить соответствие ожидаемому типу аргумента. Результат оценивания помещается во временное расположение, а ссылка на это временное размещение получается процедурой. Таким образом исходный аргумент MyVar сохраняет свое значение.

Если не требуется задавать тип для переменной, данная переменная получает тип по умолчанию, Variant. Это очевидно не во всех случаях. Например, в приведенном ниже коде объявляются две переменные: первая, MyVar , имеет тип Variant; вторая, AnotherVar , имеет тип Integer.

Для получения дополнительной информации выберите необходимый элемент и нажмите клавишу F1 (для Windows) или HELP (для Macintosh).

Поддержка и обратная связь

Есть вопросы или отзывы, касающиеся Office VBA или этой статьи? Руководство по другим способам получения поддержки и отправки отзывов см. в статье Поддержка Office VBA и обратная связь.

Источник

ByRef argument type mismatch

An argument passed ByRef (by reference), the default, must have the precise data type expected in the procedure. 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:

Placing the argument in its own set of parentheses forces evaluation of it as an expression. 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.

If you don’t specify a type for a variable, 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.

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

Support and feedback

Have questions or feedback about Office VBA or this documentation? Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.

Источник

Compile error byref argument type mismatch vba

This forum has migrated to Microsoft Q&A. Visit Microsoft Q&A to post new questions.

Answered by:

Question

I have written a user defined function as follows:

Читайте также:  Kmode exception handled что такое

Function SpiralArc(dblThickness As Double, dblTheta As Double) As Double

SpiralArc = dblThickness * (dblTheta * Sqr(1 + dblTheta ^ 2) + HArcsin(dblTheta)) / 2
End Function

I have two variables declared as doubles,

dblTapethickness and dblThetaInner

I am passing them to SpiralArc as follows.

dblLoopLengthInner = SpiralArc(dblTapethickness, dblThetaInner)

I get a compile error of argument type mismatch, and the first variable is highlighted.

Answers

I have written a user defined function as follows:

Function SpiralArc(dblThickness As Double, dblTheta As Double) As Double

SpiralArc = dblThickness * (dblTheta * Sqr(1 + dblTheta ^ 2) + HArcsin(dblTheta)) / 2
End Function

I have two variables declared as doubles,

dblTapethickness and dblThetaInner

I am passing them to SpiralArc as follows.

dblLoopLengthInner = SpiralArc(dblTapethickness, dblThetaInner)

I get a compile error of argument type mismatch, and the first variable is highlighted.

Check that the value for dblTapethickness is actually a double.

I would also use ByVal in the function’s arguments like this:

Function SpiralArc(ByVal dblThickness As Double, ByVal dblTheta As Double) As Double

Jeanette is mostly correct: it would be much better to declare the function arguments using ByVal. Why? Because it explicitly tells the user of the function you have no intention of changing the value on the caller. I wish this was the VBA default, but alas it is not.

However, the actual reason you’re getting the error is because of a common mistake with declaring variables. You used this:

Dim dblTapethickness, dblThetaInner As Double

The error is in your assumption that dblTapeThickness is now declared as a Double. It is not. It’s a Variant.

That’s why I always declare one variable per line:
Dim dblTapethickness As Double
Dim dblThetaInner As Double

Or, if you must:
Dim dblTapethickness As Double, dblThetaInner As Double

In the ByRef case, VBA’s type coercion is not strong enough, resulting in a compile error. In the ByVal case, type coercion does what most users would expect.

Not to weird you out, but this would have worked with your original code:
dblLoopLengthInner = SpiralArc((dblTapethickness), dblThetaInner)
Per the help file the extra parentheses cause the variable to be evaluated and ‘promoted’ to a double in a temporary variable of the precise type.

Источник

VBA ByRef Argument Type Mismatch

ByRef Argument Type Mismatch in Excel VBA

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 “ ByVal ByVal VBA 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 .”

Table of contents

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:

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.

Читайте также:  Whatsminer m21s error 2010

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.

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).

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 error VBA Error VBA 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.

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:

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:

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:

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.

Читайте также:  X owa error microsoft exchange data storage objectnotfoundexception

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.

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: –

Источник

Compile error byref argument type mismatch vba

This forum has migrated to Microsoft Q&A. Visit Microsoft Q&A to post new questions.

Answered by:

Question

I have written a user defined function as follows:

Function SpiralArc(dblThickness As Double, dblTheta As Double) As Double

SpiralArc = dblThickness * (dblTheta * Sqr(1 + dblTheta ^ 2) + HArcsin(dblTheta)) / 2
End Function

I have two variables declared as doubles,

dblTapethickness and dblThetaInner

I am passing them to SpiralArc as follows.

dblLoopLengthInner = SpiralArc(dblTapethickness, dblThetaInner)

I get a compile error of argument type mismatch, and the first variable is highlighted.

Answers

I have written a user defined function as follows:

Function SpiralArc(dblThickness As Double, dblTheta As Double) As Double

SpiralArc = dblThickness * (dblTheta * Sqr(1 + dblTheta ^ 2) + HArcsin(dblTheta)) / 2
End Function

I have two variables declared as doubles,

dblTapethickness and dblThetaInner

I am passing them to SpiralArc as follows.

dblLoopLengthInner = SpiralArc(dblTapethickness, dblThetaInner)

I get a compile error of argument type mismatch, and the first variable is highlighted.

Check that the value for dblTapethickness is actually a double.

I would also use ByVal in the function’s arguments like this:

Function SpiralArc(ByVal dblThickness As Double, ByVal dblTheta As Double) As Double

Jeanette is mostly correct: it would be much better to declare the function arguments using ByVal. Why? Because it explicitly tells the user of the function you have no intention of changing the value on the caller. I wish this was the VBA default, but alas it is not.

However, the actual reason you’re getting the error is because of a common mistake with declaring variables. You used this:

Dim dblTapethickness, dblThetaInner As Double

The error is in your assumption that dblTapeThickness is now declared as a Double. It is not. It’s a Variant.

That’s why I always declare one variable per line:
Dim dblTapethickness As Double
Dim dblThetaInner As Double

Or, if you must:
Dim dblTapethickness As Double, dblThetaInner As Double

In the ByRef case, VBA’s type coercion is not strong enough, resulting in a compile error. In the ByVal case, type coercion does what most users would expect.

Not to weird you out, but this would have worked with your original code:
dblLoopLengthInner = SpiralArc((dblTapethickness), dblThetaInner)
Per the help file the extra parentheses cause the variable to be evaluated and ‘promoted’ to a double in a temporary variable of the precise type.

Источник

Оцените статью
toolgir.ru
Adblock
detector