Check Return value of private sub

Question

In an Excel 2007 workbook I have three Excel modules, each containing one subroutine. The Driver sub (UpdateDataFromOracle) calls the subs UpdateResponse and UpdateSched. The code is working fine, but I'd like to check the "return code" of each of the called subs. I only want the Driver sub visible to the user, so I made the subs in Modules 1 and 2 Private. Module 1 Private Sub UpdateResponse Module 2 Private Sub UpdateSched Module 3 Public Sub UpdateDataFromOracle

Here's code from the Driver sub

Sub UpdateDataFromOracle()
'DECLARE VARIABLES
Dim varSchedReturn as variant
'...
Call UpdateResponse
Call UpdateSched
'I Would like to insert the "return code" check here
End Sub

Here's code from the Called sub

Option Explicit
Private Sub UpdateResponse()
'DECLARE VARIABLES
'...
If Sheets(strTempSheet).UsedRange.Rows.Count > 10 Then
  UpdateResponse = 0
Else UpdateResponse = 90
End If
End Sub

To call the Private subs I had to abandon the "Call" and use"

Application.Run "Module1.UpdateResponse"

But I can't figure out how to get a return code that way. I also made UpdateResponse and UpdateSched Private Functions, but I still couldn't figure out how to get a return code back. When I made UpdateResponse and UpdateSched Public Functions, I can use a statement at the end of the called subs like:

Else UpdateResponse = 90

The problem is that the called subroutines are visible to the user if I leave the functions Public.

My goal is to have only the Driver sub visible to the user, and be able to evaluate some sort of "Return Code" from the called subs in the Driver sub. Thanks for looking at this.

Use `Option Private Module` below `Option Explicit` in the modules that have the functions you don't want the users to see: http://stackoverflow.com/q/296696/293078 — Doug Glancy, Jul 23 '16 at 14:55
As a side note: if you need return variables with the same name as the Sub then use Function instead of Sub — user3598756, Jul 23 '16 at 18:00
Hello Doug. The Option Private Module below Option Explicit did the trick. I turned the called subroutines into functions. Then, all I had to do in the driver program was use the statements: varModule1Return = UpdateResponse and another statement varModule2Return = UpdateSched As I'm sure you're aware, placing the function name on the right side of the equals sign causes the function to be executed. Thanks for all your help. — user3138025, Jul 24 '16 at 12:03
You're welcome. One other tip, this one just about SO. If you put an "@" in front of somebody's name, e.g., @DougGlancy, they'll be notified about your comment. Happy coding! — Doug Glancy, Jul 25 '16 at 02:50

score 1 · Answer 1 · answered Jul 23 '16 at 15:55

I didn't fully read the question, but change them to Function

Private Function UpdateResponse() As Integer
    'DECLARE VARIABLES
    '...
    If Sheets(strTempSheet).UsedRange.Rows.Count > 10 Then
        UpdateResponse = 0
    Else 
        UpdateResponse = 90
    End If
End Function

Then:

Dim response         ' As Variant or Integer 
response = Application.Run("Module1.UpdateResponse")

Also, there are 2 better ways with Option Private Module or a public variable in Module1

3 Ways to Call a Private Sub from Another Module

Hello Slai, That didn't seem to work for me. Using the "Option Private Module" in the called functions seemed to do the trick. That suggestion came from Doug Glancy above. — user3138025, Jul 24 '16 at 12:08

score 0 · Answer 2 · answered Jul 27 '16 at 12:49

0

The answer @DougGlancy gave worked well. It's listed as a comment to my original question, so I'm adding this Answer to indicate that his answer was correct.

answered Jul 27 '16 at 12:49

user3138025

795
4
17
46

Steve · Answer 3 · 2016-07-23T15:18:16.457

-1

One option for Windows Excel that I have used is to set values on the user's machine that can be retrieved in a later process. https://msdn.microsoft.com/en-us/library/z46c489x(v=vs.110).aspx?cs-save-lang=1&cs-lang=vb#code-snippet-1

Alternatives could be to set some properties of your Excel workbook or in Excel you can create names and values associated with them. (part of named ranges).

Lastly, you can add and change values in the windows registry.

edited Jul 23 '16 at 15:18

answered Jul 23 '16 at 15:09

Steve

9
1
4

These are way too complicated. – Doug Glancy Jul 23 '16 at 15:50

Check Return value of private sub

3 Answers3