Subscript out of range

[KDBrown]

Good morning!
I have a "Subscript out of range" issue. Background: working in Excel 2007 with an SAP BPC Add-In (BPC was formerly known as OutlookSoft). I know very little about VBA.

We have a file (call it file #1) that contains the following VBA:

Function AFTER_SEND(Argument As String)
Dim oCallingWorksheet As Worksheet
Set oCallingWorksheet = ActiveSheet
On Error Resume Next
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
Application.Run ("MNU_ETOOLS_EXPAND")
<o> </o>
AFTER_SEND = True
<o> </o>
End Function

If we keep file #1 open and then attempt to open a different Excel file (which contains other macros that run upon open), the Subscript message appears. We do not receive the same message when opening those files if file #1 is NOT open. So logic would seem to say the problem is in file #1. Having read some similiar posts, I'm inclined to think the problem is the reference to the "ActiveSheet" in the first file. Am I on the right track here? I'll reiterate that I have very little exposure to VBA.

Any help will be greatly appreciated! Thanks!

 

[Domski]

Is it one of the lines in this code that is highlighted if you click Debug when you get the error?

Dom

 

[KDBrown]

Hi Dom,

When we receive the message, the only option is to click OK. Debug doesn't come up as a choice.

 

[Domski]

Is that the only code in the workbook? I'm just wondering what would call that function for it to cause the error in the first place.

Dom

 

[Andrew Poulsom]

Are you using that function on a worksheet? If so try:

Set oCallingWorksheet = Application.Caller.Parent

 

[KDBrown]

Hi Andrew,

Thanks for your reply. I gave your suggestion a try. Unfortunately it created a runtime error in file #1.


I believe the intent of the code is to kick off the SAP BPC Excel macro "MNU_ETOOLS_EXPAND" after the completion of an SAP BPC Excel Send macro on the worksheet. The only other code in the file pertains to a variety of macros that hide/show different parts of the worksheet. They are all simple recorded macros that only kick off if the user pushes designated buttons.

 

[Andrew Poulsom]

What run-time error did you get and on which line of code? What is the purpose of the variable oCallingWorksheet? How and where is that function being called

 

[KDBrown]

Sorry for dropping off this chain yesterday. System problems prevented me from getting to the files in question.

So, here's where things stand as of now:
I tried removing part and then all of the suspected script. Error continues. I then removed all macros and VBA code from the file. The error still persists.

Other than waving the white flag and calling it a bug in the file, I'm not sure where else to focus my efforts. Any suggestions will be greatly appreciated!

 

[Andrew Poulsom]

It sounds like the error is coming from an SAP workbook. If that's the case i don't think I can help any further.

 

[KDBrown]

We finally found the problem. The issue was the use of a volatile Excel function, namely OFFSET, to determine the member selection for our BPC query (EVDRE). By changing to an IF statement, the problem went away. The issue was only on the BPC member selection. Other OFFSET formulas in the file remain and work fine.