Passing values from different Subs

[bconforto]

Hello guys,
I have below example, where I have 3 buttons.
One to clear values. (Sub Clear)
One to perform an RFC to a SAP table that provides some values. (Sub RFC_Read_Table)
One that should use one of the values(Servnot) of the Sub before to display the document.

The goal is to execute Sub RFC_Read_Table to obtain Servnot value and stop.
Then if the third button is click execute third Sub to show it in SAP.

Original problem was that when executing third Sub, Servnot was empty. Values was not passed from a Sub to the other.

I google'd and tried different things.
Now, when I execute second Sub, it does not stop and goes directly to show document in SAP.
If i execute third Sub it says Argument not optional.

What should I do to get a value in a Sub that is finished, but then it should be available to be used in a different Sub, that could be executed only when button is clicked?

Regards,
Brian

Sub Clear()
        Range(Cells(8, 3), Cells(8, 3)).Cells.ClearContents
        Range(Cells(10, 3), Cells(14, 3)).Cells.ClearContents
        Range(Cells(8, 5), Cells(8, 5)).Cells.ClearContents
End Sub

Public Sub RFC_Read_Table()
Dim Functions As Object
Dim RfcCallTransaction As Object
Dim Messages As Object
Dim BdcTable As Object
Dim tblOptions
Dim tblData
Dim tblFields
Dim strExport1
Dim strExport2
Dim intRow As Integer
Dim conn As Object
Dim sap As Object
Dim Servnot As Long
Dim Servnot1 As Long
Application.ScreenUpdating = False

user = Sheets("Sheet2").Range("E2").Value
pass = Sheets("Sheet2").Range("E3").Value
Application.ScreenUpdating = True
Set Functions = CreateObject("SAP.Functions")
Functions.Connection.System = "PR1"
Functions.Connection.client = "010"
Functions.Connection.user = user
Functions.Connection.Password = pass
Functions.Connection.Language = "EN"

If Functions.Connection.logon(0, True) <> True Then
    Exit Sub
End If
Set RfcCallTransaction = Functions.Add("RFC_READ_TABLE")
Set strExport1 = RfcCallTransaction.exports("QUERY_TABLE")
Set strExport2 = RfcCallTransaction.exports("DELIMITER")
Set tblOptions = RfcCallTransaction.Tables("OPTIONS")
Set tblData = RfcCallTransaction.Tables("DATA")
Set tblFields = RfcCallTransaction.Tables("FIELDS")
'QMEL Read
Dim GCH2 As String
strExport1.Value = "QMEL"
strExport2.Value = ";"
GCH1 = Cells(8, 3).Value
GCH2 = Cells(8, 5).Value
GCH1L = UCase(Left(GCH1, 2))
GCH1R = Right(GCH1, 4)
GCH = GCH1L + GCH1R + GCH2
 
tblOptions.AppendRow
tblOptions(1, "TEXT") = "ZZCRM_ORDER EQ '" & GCH & "' "
tblOptions.AppendRow
tblFields.AppendRow
tblFields(1, "FIELDNAME") = "QMNUM"
tblFields.AppendRow
tblFields(2, "FIELDNAME") = "VKORG"
tblFields.AppendRow
tblFields(3, "FIELDNAME") = "VTWEG"
tblFields.AppendRow
tblFields(4, "FIELDNAME") = "SPART"
tblFields.AppendRow
tblFields(5, "FIELDNAME") = "SERIALNR"
tblFields.AppendRow
tblFields(6, "FIELDNAME") = "ERNAM"
tblFields.AppendRow
tblFields(7, "FIELDNAME") = "ERDAT"
 
If RfcCallTransaction.Call = True Then
    If tblData.RowCount > 0 Then
    For intRow = 1 To tblData.RowCount
    Record = tblData(intRow, "WA")
    Servnot = Left(Record, 12)
    Sorg = Mid(Record, 14, 4)
    DC = Mid(Record, 19, 2)
    Div = Mid(Record, 22, 2)
    Serial = Trim(Mid(Record, 25, 18))
    CreatedBy = Trim(Mid(Record, 44, 12))
    Createdon = Right(Record, 8)
    
    Cells(10, 3).Value = Servnot
    Cells(11, 3).Value = Serial
    Cells(12, 3).Value = CreatedBy
    Cells(13, 3).Value = Createdon
    Cells(14, 3).Value = Sorg + " / " + DC + " / " + Div
    
    
    Next
    Else
        MsgBox "No records returned"
        Exit Sub
    End If
Else
    MsgBox "Error"
    Exit Sub
End If
Functions.Connection.Logoff
Button3_click Servnot1:=Servnot
End Sub
Sub Button3_click(ByVal Servnot1 As Long)
'Dim Servnot1 As Long
'Servnot1 = Servnot
If Not IsObject(SAP_Application) Then
   Set SapGuiAuto = GetObject("SAPGUI")
   Set SAP_Application = SapGuiAuto.GetScriptingEngine
End If
If Not IsObject(Connection) Then
   Set Connection = SAP_Application.Children(0)
End If
If Not IsObject(session) Then
   Set session = Connection.Children(0)
End If
If IsObject(WScript) Then
   WScript.ConnectObject session, "on"
   WScript.ConnectObject SAP_Application, "on"
End If
Set xclsht = ActiveWorkbook.ActiveSheet
session.findById("wnd[0]").maximize
session.findById("wnd[0]/tbar[0]/okcd").Text = "/niw53"
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]/usr/ctxtRIWO00-QMNUM").Text = Servnot1
session.findById("wnd[0]/usr/ctxtRIWO00-QMNUM").caretPosition = 9
session.findById("wnd[0]").sendVKey 0
End Sub

 

[Jerry Sullivan]

Hi Brian,

If you are going to call the third Sub from a Button, you won't be able to use a parameter for that Sub (delete that parameter from the Sub's declaration).

Sub Button3_click()

You should eliminate this line in the second Sub, since you only want to run the third Sub when the user clicks the button.

Button3_click Servnot1:=Servnot

There's a few places to store the value of Servnot that results from the second Sub so that it can be retrieved by the third Sub:

1. In a Public variable
2. In a worksheet cell
3. In a defined name

There's some pros and cons to each. I'd recommend using a worksheet cell is the simplest and most transparent for your checking.

So have Sub2 write the value of Servnot to a cell, then have Sub3 read that value and use it.
Once that's working you should add a step in Sub3 to validate that Sub2 has saved a valid value to the cell, just in case someone tries to run Sub3 prior to running Sub2.

 

[bconforto]

Thanks Jerry,
I am already saving the value in a Cell, but wanted to know if there was any way to do it through coding.

Regards,
Brian

 

[Joe4]

As Jerry said, you can use a Public Variable in VBA to store it, so that it will be available to the other procedures (Subs).
See: Variable Scope in Excel VBA - EASY Excel Macros

 

[GTO]

@Jerry Sullivan:

If you are going to call the third Sub from a Button, you won't be able to use a parameter for that Sub (delete that parameter from the Sub's declaration).

Sub Button3_click()

Hi Jerry ,

I believe I may be presuming differently, in that I am guessing that the OP's code is in a Standard Module, and that the command button is a Forms button. I am not saying that there would be any advantage to it, but just thought to respectfully mention that if the button is a forms style button, we could change the OnAction to include a parameter. Something like:

Option Explicit
  
Private lPrivateToModuleValueAsLong As Long
  
Public Property Get GetValue() As Long
  GetValue = lPrivateToModuleValueAsLong
End Property
  
Sub CallingSub()
  Button1_Click lPrivateToModuleValueAsLong
End Sub
  
Private Sub Button1_Click(ByVal ModScopedLong As Long)
  
  lPrivateToModuleValueAsLong = 1 + ModScopedLong
  MsgBox "lPrivateToModuleValueAsLong = " & lPrivateToModuleValueAsLong & vbLf & _
         "ModScopedLong = " & ModScopedLong, _
            vbOKOnly Or vbInformation, vbNullString
         
End Sub
  
' REM or delete before putting WB in use.
Sub SetupStuff()
  
  lPrivateToModuleValueAsLong = 1
  'old-style forms button
  Sheet1.Buttons("Button 1").OnAction = "'" & ThisWorkbook.Name & "'!'Module2.Button1_Click GetValue'"
  
  Debug.Print Sheet1.Buttons("Button 1").OnAction
  
End Sub

...where the Property Get (or if discluding the Property Get, the variable referred to) needs to be Public for the button's OnAction to be able to "see".

Mark

 

[bconforto]

Thank you very much to all of you, my question was answered.

Regards,
Brian

 

[Jerry Sullivan]

Hi Jerry ,

I believe I may be presuming differently, in that I am guessing that the OP's code is in a Standard Module, and that the command button is a Forms button. I am not saying that there would be any advantage to it, but just thought to respectfully mention that if the button is a forms style button, we could change the OnAction to include a parameter.

Hi Mark, I'm always happy to receive feedback - especially when I learn something new!

I wasn't aware that forms style controls could accept parameters. As you noted, in this case I don't think there's an advantage to it- but I'll definitely add that to my tool bag. Thanks!

 

[GTO]

@Jerry Sullivan:

Hi Mark, I'm always happy to receive feedback - especially when I learn something new!

Me too! I just wish stuff that I learn would quit falling out of my head so easily/quickly.

Anyways, you are very welcome of course, and here's a link you'll like: Objects and arguments

Mark