byVal sheetname as Variant

[df9864]

The following code works OK - this essentially triggers an Essbase Zoom event on cell "A4" on worksheet "2. Retrieve":

Declare Function EssVZoomIn Lib "ESSEXCLN.XLL" (ByVal sheetName As Variant, ByVal range As Variant, ByVal selection As Variant, ByVal level As Variant, ByVal across As Variant) As Long

Sub ZoomData()

X = EssVZoomIn("2. Retrieve", Null, range("A4"), 2, False)
If X = 0 Then
MsgBox ("Zoom successful.")
Else
MsgBox ("Zoom failed.")
End If

End Sub

However, within the VBA module I've named the worksheet "2. Retrieve" as wkRetrieve
How do I replace the worksheet name (in the workbook) with the worksheet name I've defined in the background. Everything I've done seems to give me errors (eg. replacing the declaration with sheetname as object or byval to byref, etc....

I'd be hugely grateful for a solution as my small brain cannot figure this out.

 

[Colin Legg]

I think you want this?

[I]X = EssVZoomIn(wkRetrieve.Name, Null, range("A4"), 2, False)[/I]