XL97: Macro Error Using ActiveCell in a Binder or Embedded File

• Microsoft Excel 97 for Windows
• Microsoft Office 97 for Windows

SYMPTOMS

When you run a macro in a Microsoft Excel workbook that is part of a Microsoft Binder file or that is embedded in a document in another program, you may receive the following error message:

   Run-time error '91':
   Object variable or With block variable not set

CAUSE

This problem occurs when both of the following conditions are true:

• The macro line that causes the error uses ActiveCell or ActiveSheet.

  -and-

• You run the macro from the Visual Basic Editor.

RESOLUTION

Microsoft provides examples of Visual Basic for Applications procedures for illustration only, without warranty either expressed or implied, including, but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Visual Basic procedures in this article are provided 'as is' and Microsoft does not guarantee that they can be used in all situations. While Microsoft support engineers can help explain the functionality of a particular macro, they will not modify these examples to provide added functionality, nor will they help you construct macros to meet your specific needs. If you have limited programming experience, you may want to consult one of the Microsoft Solution Providers. Solution Providers offer a wide range of fee-based services, including creating custom macros. For more information about Microsoft Solution Providers, call Microsoft Customer Information Service at (800) 426-9400.

To run the macro in the Visual Basic Editor, modify the macro by specifying the worksheet in which you want to use the ActiveCell or ActiveSheet property. For example, if the macro contains the following code:

   Sub Test()
       ActiveCell.Value = "my value"
   End Sub

   Sub Test()
       Thisworkbook.Sheets("Sheet1").Activate
       ActiveCell.Value = "it works"
   End Sub

STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

MORE INFORMATION

This problem is likely to occur when you record a macro that contains the ActiveCell property, and you then run the recorded macro from the Visual Basic Editor.

When you type text or a number in a cell, the macro recorder uses the ActiveCell property. The following macro is recorded when you select cell B1 and type "hello" in the cell:

   Sub Macro1()
       Range("B1").Select
       ActiveCell.FormulaR1C1 = "hello"
       Range("B2").Select
   End Sub