7

I hope this is an appropriate place to ask this question.

I have recently built a data analysis tool in Excel that works by submitting inputs to a SAS Stored Process (as an 'input stream'), running the processes and displaying the results in Excel.

I also use some code to check for and remove all active stored processes from the workbook before running the process again.

This runs successfuly the first 2 times, but fails on the third attempt. It always fails on the third attempt and I can't figure out why.

Is there some kind of memory allocation for Excel VBA that's exhausted by this stage? Or some other buffer that's maxed out? I've stepped-in to every line of the VBA code and it appears to hang (on the third run) at the following line:

SAS.InsertStoredProcess processLoc, _
    outputSheet.Range("A1"), , , inputStream

Code used to initiate SAS Add-in for Microsoft Office:

Dim SAS As SASExcelAddIn
Set SAS = Application.COMAddIns.Item("SAS.ExcelAddIn").Object

Code used to delete stored processes from target output sheet:

Dim Processes As SASStoredProcesses
Set Processes = SAS.GetStoredProcesses(outputSheet)
Dim i As Integer
For i = 1 To Processes.Count
'    MsgBox Processes.Item(i).DisplayName
    Processes.Item(i).Delete
Next i

Code used to insert and run stored process:

Dim inputStream As SASRanges
Set inputStream = New SASRanges
inputStream.Add "Prompts", inputSheet.Range("DrillDown_Input")
SAS.InsertStoredProcess processLoc, _
    outputSheet.Range("A1"), , , inputStream

Cheers

Aleks Danger
  • 93
  • 7
  • 3
    It's a good question and I don't have an answer. I use VBA to call stored processes as well, but have never seen this. It might be best asked to SAS Tech Support. http://support.sas.com – DomPazz Jun 25 '14 at 05:26
  • 2
    You might benefit from putting `Set SAS = Nothing`, `Set Processes = Nothing` and `Set inputStream = Nothing` at the end of your VBA procedure. ( I understand this is a good practice when executing SQL stored procs from VBA, due to memory concerns.) – Mike Jun 27 '14 at 02:03

3 Answers3

1

On reflection, my theory here would be that you are hitting the limit of multibridge connections. Each multibridge connection represents a port, and the more ports you have the more parallel connections are enabled. By default there are three, perhaps you have two, or you are kicking off another STP at the same time?

This would explain the behaviour. I had a spreadsheet that called STPs and it would always fail on the fourth call because the first three were running. You can get around this by either a) increasing the number of multibridge connections or b) chaining your processes so they run sequentially.

Allan Bowe
  • 12,306
  • 19
  • 75
  • 124
0

I don't know if this'll be useful, but I had a similar problem running a DLL written in C++ through VBA. The problem occurred because the function in the DLL returned a double value, which I didn't need and so the code in VBA did 

Call SomeProcessFromDLL()

But the process was returning a double floating point value which was 'filling up' some buffer memory in VBA and VBA has a limited buffer (I think it gave up at 8 tries). So the solution for me was 

Dim TempToDiscard as Double
TempToDiscard = SomeProcessFromDLL()

Maybe looking at the documentation of the process being called would help here, especially if it's returning some value to be discarded anyway, like a 

Return 0;
hnk
  • 2,216
  • 1
  • 13
  • 18
0

I never liked using the IOM in VBA, mainly due to issues with references and having to do client installs when rolling out applications. Last year I found a MUCH better way to connect Excel and SAS - using the Stored Process web application. Simply set up your server side SAS process with streaming output, and pass your inputs via an Excel Web query. No client installs, no worries about SAS version upgrades, hardly any code - am surprised it's not used more often!

See: http://rawsas.blogspot.co.uk/2016/11/sas-as-service-easy-way-to-get-sas-into.html

Allan Bowe
  • 12,306
  • 19
  • 75
  • 124