Microsoft KB Archive/113947

{|
 * width="100%"|

PRB: Error When Excel VBApp Proc & Implicit Var Have Same Name

 * }

Q113947

-

The information in this article applies to:


 * Microsoft Visual Basic for Applications version 1.0
 * Microsoft Excel for Windows, version 5.0
 * Microsoft Office for Windows, version 4.0

-

SYMPTOMS
In an Excel Visual Basic for Applications module, if you have a procedure and an implicitly declared variable that share the same name, you will get one of two possible errors:

Type-declaration character does not match declared data type.

"-or-"

Expected function or variable.

RESOLUTION
Use the Dim statement to explicitly dimension the local variable (ThingOne$ or ThingOne):

  Sub ThingOne

End Sub

Sub ThingTwo Dim ThingOne$ ' Or: Dim ThingOne As Variant ThingOne$ = "hi" End Sub Or add the Option Explicit statement at the beginning of your code module to force you to explicitly dimension all variables.

STATUS
This behavior is by design. The local variable ThingOne (or ThingOne$) must be explicitly declared or you will get an error. Sub procedures within modules are visible to each other in the Visual Basic, Applications Edition.

Because ThingOne is visible inside ThingTwo (see the code in the More Information section below) and Sub and Function procedures may be called without parameters the reference to ThingOne as a variable is ambiguous.

In the first case, the type char is checked first. The type is determined to be a String. However, the Sub declaration is equivalent to a function which has a void return. The $ contradicts this void return, so you get an error.

In the second case, without the type character, Visual Basic, Applications Edition checks the return type of the procedure. The return for a Basic Sub is void so it results in the second error.

This behavior can be avoided altogether by using the Option Explicit statement.

Steps to Reproduce Behavior
 Start Microsoft Excel version 5.0. From the File menu, choose New to create a new Excel book. From the Insert menu, choose Macro and then choose Module to create a new module in the book.  Add the following code to the Excel module:   Sub ThingOne

End Sub

Sub ThingTwo ThingOne$ = "hi" End Sub   Run the macro by choosing Start from the Run menu or by pressing the F5 key. Excel will pop-up an error dialog:

Type-declaration character does not match declared data type.

  Replace the above code with the following code.   Sub ThingOne

End Sub

Sub ThingTwo ThingOne = 4 End Sub   Run the macro. Excel will pop-up an error dialog:

Expected function or variable.

</li></ol>

Additional query words:

Keywords :

Issue type : kbprb

Technology :