Excel Mystery

[stanl]

I have a very simple WB script that accepts a dropdown of project reports from various sources, performs multiple SQL statements from multiple data sources, updates links, pivots and slicers in Excel Report files. Not wanting the curious to look into the various Data Tabs that populate the Pivots I wrote a routine that can make/unmake those tabs very hidden or visible.  There were 2 ways to do this as a subroutine or a function. Both would require sending a 'mode' parameter, while the function would require an additional 'sheet' parameter:

[NOTE: this is not optimal code...]

The subroutine works perfectly

Code (WINBATCH) Select Expand


#DefineSubRoutine hSheet(mode)
   If mode==1
      If oXL.Activeworkbook.Sheets("%cSheet%").Visible==2 Then oXL.Activeworkbook.Sheets("%cSheet%").Visible=-1
   Else
      If oXL.Activeworkbook.Sheets("%cSheet%").Visible==-1 Then oXL.Activeworkbook.Sheets("%cSheet%").Visible=2
   Endif
   Return(1)
#EndSubRoutine


While as a function, I get an OLE Object Not recognized error

Code (WINBATCH) Select Expand


#DefineFunction hSheet(mode,cSheet)
   If mode==1
      If oXL.Activeworkbook.Sheets("%cSheet%").Visible==2 Then oXL.Activeworkbook.Sheets("%cSheet%").Visible=-1
   Else
      If oXL.Activeworkbook.Sheets("%cSheet%").Visible==-1 Then oXL.Activeworkbook.Sheets("%cSheet%").Visible=2
   Endif
   Return(1)
#EndFunction




Of course, I realize a function should only return a value, so this a not a WB problem, per se. I originally wrote just the subroutine and the function was just a test. I live in a world where slicers are the 'coup de gras' of analytics, but truth be told my 200 lines of WB script code run just as fast and accurate as several thousand lines of WBNET code.

[JTaylor]

Don't you need to pass oXL to the function as well?

Jim

[stanl]

Don't you need to pass oXL to the function as well?

Jim

Makes sense. Getting rusty.  :o