0

I need to write some VBA code for a workbook with two worksheets. Each worksheets contains one table with data from a SQL query, but the exact number and order of columns is not yet finalized.

To make dealing with specific worksheet, column and row references easier, I defined public variables in my module, and there is an Init function that sets the corresponding values. That Init function is called in Workbook_Open. This works nicely for column and row variables, but setting a worksheet reference with this:

wsItems = ActiveWorkbook.Worksheets("Items")

causes an error: runtime error 91 - object variable or with block variable not set

I guess it makes sense that during Workbook_Open the worksheets collection isn't initialized yet. The question is, where would be the proper place to do initialize this? I also tried Workbook_Activate and was looking for something like Workbook_AfterOpen or something similar, but nothing really caught my eye.

Tom
  • 1
  • 1
  • 3
  • 3
    you need the `Set` keyword, `set wsItems = ActiveWorkbook.Worksheets("Items")` – Warcupine Mar 30 '23 at 17:28
  • 1
    If it fits your use case, it'd be much easier to just use the sheet codename, which eliminates the need for a global variable. – BigBen Mar 30 '23 at 19:48
  • @Warcupine Thanks, I totally forgot about that! Too many years C# can do that to you :) – Tom Mar 31 '23 at 07:55

0 Answers0