VBA: Run time error ’91’?

In VB object variables require the Set keyword to be assigned. Object properties that are objects also need to be Set. Runtime error 91 “object variable not set” is raised when the assignment doesn’t use that keyword.

This is inherited from legacy Let keyword to assign values, and Set keyword to assign references; the Let eventually was deprecated (although still needed for defining properties) and the Set remained, leaving the VB6/VBA value assignment syntax like [Let] variable = value, where “Let” is optional.

In the declaration and assignment:

Dim SourceWindow As Window, QACheckWindow As Window
'this is like saying "Let SourceWindow = ActiveWindow":
SourceWindow = ActiveWindow

SourceWindow is an object, assigned as if it were a value – this causes VBA to attempt let-coercion through a default member call. If the object wasn’t initialized, the member call fails with error 91. If the object was initialized but doesn’t have a default member, error 438 is raised.

So in this case error 91 is being raised because of an implicit member call; the .net equivalent would be a NullReferenceException:

Dim SourceWindow As Window, Dim WindowTitle As String
'"SourceWindow" reference isn't set, the object can't be accessed yet:
WindowTitle = SourceWindow.Caption

I’m going to go a bit overboard here, but the legacy Let statement should not be confused with the Let clause (in VB.net) which, in the LINQ query syntax (in VB.net), computes a value and assigns it to a new, query-scoped variable (example taken from MSDN):

From p In products 
Let Discount = p.UnitPrice*0.1 '"Discount" is only available within the query!
Where Discount >= 50
Select p.ProductName, p.UnitPrice, Discount

VB.net assigns both values and references, without the need to specify a Let or a Set, because in .net this distinction is a much thinner line, given how everything ultimately derives from System.Object… including System.ValueType. That’s why the Set keyword was also deprecated in VB.net, and also why the VB.net syntax for defining properties has dropped the Let in favor of Set – because parameterless default members are illegal in VB.NET, so this ambiguous let-coercion doesn’t happen.

Related Posts:

  1. What does <> mean?
  2. How to find a value in an excel column by vba code Cells.Find
  3. Check if a string contains another string
  4. VBScript to open a dialog to select a filepath
  5. Does VBA have Dictionary Structure?
  6. “Sub or Function not defined” when trying to run a VBA script in Outlook
  7. What is the function of FormulaR1C1?
  8. What exactly is the function of Application.CutCopyMode property in Excel
  9. VBA Excel – Insert row below with same format including borders and frames
  10. Deleting a file in VBA
  11. Excel VBA: Can’t get a match, error “Unable to get the Match property of the WorksheetFunction class”
  12. How to comment and uncomment blocks of code in the Office VBA Editor
  13. Run-time error ‘3061’. Too few parameters. Expected 1. (Access 2007)
  14. Pass arguments to Constructor in VBA
  15. How to install Date Picker form control in Excel 2016
  16. How to install Date Picker form control in Excel 2016
  17. How to fill color in a cell in VBA?
  18. Refreshing all the pivot tables in my excel workbook with a macro
  19. Compare Dates in VBA
  20. What is the difference between “Form Controls” and “ActiveX Control” in Excel 2010?
  21. ‘Cannot find DAO350.dll’ file error VB6
  22. Average in VBA in Excel
  23. VBA Excel “Compile error: Object Required”
  24. Choosing paper size (NOT DEFAULT sizes) in excel vba
  25. “Unable to get the VLookup property of the WorksheetFunction Class” error [duplicate]
  26. Paste Special error 1004 PasteSpecial method of Range class failed
  27. VBA: Getting run-time 1004: Method ‘Range’ of object ‘_Worksheet’ failed when using cells
  28. Cannot run the macro… the macro may not be available in this workbook
  29. How to use Regular Expressions (Regex) in Microsoft Excel both in-cell and loops
  30. How to use Regular Expressions (Regex) in Microsoft Excel both in-cell and loops
  31. Object Required Error in excel VBA
  32. What are .NumberFormat Options In Excel VBA?
  33. Better way to find last used row
  34. VBA, if a string contains a certain letter
  35. Excel VBA Macro: User Defined Type Not Defined
  36. lists and arrays in VBA
  37. VBA Runtime Error 1004 “Application-defined or Object-defined error” when Selecting Range
  38. Select method of Range class failed via VBA
  39. Does VBA contain a comment block syntax?
  40. Why does “Paste Method of Worksheet class failed” occasionally occur?
  41. How to return a result from a VBA function
  42. How to clear memory to prevent “out of memory error” in excel vba?
  43. Use VBA to Clear Immediate Window?
  44. VBA: Else without If Error
  45. Recommended IDE for VBA
  46. ByRef argument type mismatch in Excel VBA
  47. VBA: Convert Text to Number
  48. VBA Error: “Compile error: Expected End Sub”
  49. Scraping data from website using vba
  50. Error: “Could Not Find Installable ISAM”
  51. Loop without Do error
  52. Row count where data exists
  53. VBA array sort function?
  54. Test or check if sheet exists
  55. How to detect if user select cancel InputBox VBA Excel
  56. macro run-time error ‘9’: subscript out of range
  57. How to Clear Office Clipboard with VBA
  58. Why does my VBA code throw an “Invalid outside procedure” error?
  59. Excel Solver Password: what is it and where can it be acquired/bought?
  60. Using Excel VBA to run SQL query
  61. Compile error: Next without For || VBA
  62. Is there a way to crack the password on an Excel VBA Project?

Leave a Comment