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
#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
#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.
Don't you need to pass oXL to the function as well?
Jim
Quote from: JTaylor on March 25, 2017, 07:36:03 AM
Don't you need to pass oXL to the function as well?
Jim
Makes sense. Getting rusty. :o