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.

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

As you can see above, the moment we execute the line of code “Macro2 A,” it has jumped to the next VBA sub procedureVBA 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.

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

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.

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.

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

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

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

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.

So, Option ExplicitOption 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

  • Goal Seek in VBAVBA Index Match FunctionOverflow Error in Excel VBA1004 Error in Excel VBA