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

• Microsoft Visual Basic, Application Edition, 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 

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.

MORE INFORMATION

Steps to Reproduce Behavior

1. Start Microsoft Excel version 5.0.



2. From the File menu, choose New to create a new Excel book.



3. From the Insert menu, choose Macro and then choose Module to create a new module in the book.



4. Add the following code to the Excel module:

   
      Sub ThingOne
   
      End Sub
   
      Sub ThingTwo
         ThingOne$ = "hi"
      End Sub 




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




6. Replace the above code with the following code.

   
      Sub ThingOne
   
      End Sub
   
      Sub ThingTwo
         ThingOne = 4
      End Sub 




7. Run the macro. Excel will pop-up an error dialog:

   Expected function or variable.

Additional query words:

Keywords :
Version : :1.0; WINDOWS:4.0,5.0
Platform : WINDOWS
Issue type :