Excel VBA - Call sub (Object required error message)

Dear Experts,

I have the following VBA code which is structured like that the SAP login details are in MainStream(), which calls the XK02_ReconAccount_AQ1() sub to fill the SAP screens through SAP scripting:

Sub MainStream()

login = InputBox("Please provide SAP user")
pass = InputBox("Please enter password")
system = "AQ1"

Set SapGui = GetObject("SAPGUI")
Set Appl = SapGui.GetScriptingEngine
Set Connection = Appl.OpenConnection(system, True)
Set session = Connection.Children(0)

Dim LastRow As Long
Dim StatusBarText As String
Dim StatusBarMessageType As String
With ActiveSheet
    LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

' Modify here the credentials and SAP system

session.findById("wnd[0]/usr/txtRSYST-BNAME").Text = login
session.findById("wnd[0]/usr/pwdRSYST-BCODE").Text = pass
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]").maximize

Call XK02_ReconAccount_AQ1

End Sub


Sub XK02_ReconAccount_AQ1()

session.findById("wnd[0]/tbar[0]/okcd").Text = "XK02"
session.findById("wnd[0]").sendVKey 0

ActRow = ActiveCell.Row

For i = 1 To LastRow

session.findById("wnd[0]/usr/chkRF02K-D0210").Selected = True
session.findById("wnd[0]/usr/ctxtRF02K-LIFNR").Text = ActiveCell.Offset(i, 0)
ActiveCell.Offset(i, 0).Interior.ColorIndex = 6
session.findById("wnd[0]/usr/ctxtRF02K-BUKRS").Text = ActiveCell.Offset(i, 1)
ActiveCell.Offset(i, 0).Interior.ColorIndex = 6
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]/usr/ctxtLFB1-AKONT").Text = ActiveCell.Offset(i, 2)
ActiveCell.Offset(i, 0).Interior.ColorIndex = 6
session.findById("wnd[0]/tbar[0]/btn[11]").press
tekst = session.findById("wnd[0]/sbar").Text
If InStr(1, tekst, "balance", vbTextCompare) > 0 Then
session.findById("wnd[0]").sendVKey 0
Else
End If

ErrMes = session.findById("wnd[0]/sbar").Text
If InStr(1, ErrMes, "deletion", vbTextCompare) > 0 Then
session.findById("wnd[0]").sendVKey 0
End If

StatusBarText = session.findById("wnd[0]/sbar").Text
StatusBarMessageType = session.findById("wnd[0]/sbar").MessageType
ActiveCell.Offset(i, 3) = StatusBarMessageType
ActiveCell.Offset(i, 3).Interior.ColorIndex = 4
ActiveCell.Offset(i, 4) = StatusBarText
ActiveCell.Offset(i, 4).Interior.ColorIndex = 4

ActiveWindow.ScrollRow = ActRow + i
Application.StatusBar = "Number of records processed: " & i & " of " & LastRow & ": " & Format(i / LastRow, "0%")

Next

End Sub

Open in new window



The actual issue is that when the MainStream() is completed and the code would go to the sub one, the following error message comes:

 'Object required' error messageThe running of the code stops at this line
Presumably some variables are not transferred to the called sub from the main one, could you please advise concretely which ones should be defined as global and how in the syntax?

Thanks in advance,
LVL 1
csehzIT consultantAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bill PrewIT / Software Engineering ConsultantCommented:
You have at least two approaches.  The first would pass the session variable to the subprocedures that need it, and avoid making is a global variable.  The other uses a global variable.

Sub MainStream()

login = InputBox("Please provide SAP user")
pass = InputBox("Please enter password")
system = "AQ1"

Set SapGui = GetObject("SAPGUI")
Set Appl = SapGui.GetScriptingEngine
Set Connection = Appl.OpenConnection(system, True)
Set session = Connection.Children(0)

Dim LastRow As Long
Dim StatusBarText As String
Dim StatusBarMessageType As String
With ActiveSheet
    LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

' Modify here the credentials and SAP system

session.findById("wnd[0]/usr/txtRSYST-BNAME").Text = login
session.findById("wnd[0]/usr/pwdRSYST-BCODE").Text = pass
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]").maximize

Call XK02_ReconAccount_AQ1(session)

End Sub


Sub XK02_ReconAccount_AQ1(session)

session.findById("wnd[0]/tbar[0]/okcd").Text = "XK02"
session.findById("wnd[0]").sendVKey 0

ActRow = ActiveCell.Row

For i = 1 To LastRow

session.findById("wnd[0]/usr/chkRF02K-D0210").Selected = True
session.findById("wnd[0]/usr/ctxtRF02K-LIFNR").Text = ActiveCell.Offset(i, 0)
ActiveCell.Offset(i, 0).Interior.ColorIndex = 6
session.findById("wnd[0]/usr/ctxtRF02K-BUKRS").Text = ActiveCell.Offset(i, 1)
ActiveCell.Offset(i, 0).Interior.ColorIndex = 6
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]/usr/ctxtLFB1-AKONT").Text = ActiveCell.Offset(i, 2)
ActiveCell.Offset(i, 0).Interior.ColorIndex = 6
session.findById("wnd[0]/tbar[0]/btn[11]").press
tekst = session.findById("wnd[0]/sbar").Text
If InStr(1, tekst, "balance", vbTextCompare) > 0 Then
session.findById("wnd[0]").sendVKey 0
Else
End If

ErrMes = session.findById("wnd[0]/sbar").Text
If InStr(1, ErrMes, "deletion", vbTextCompare) > 0 Then
session.findById("wnd[0]").sendVKey 0
End If

StatusBarText = session.findById("wnd[0]/sbar").Text
StatusBarMessageType = session.findById("wnd[0]/sbar").MessageType
ActiveCell.Offset(i, 3) = StatusBarMessageType
ActiveCell.Offset(i, 3).Interior.ColorIndex = 4
ActiveCell.Offset(i, 4) = StatusBarText
ActiveCell.Offset(i, 4).Interior.ColorIndex = 4

ActiveWindow.ScrollRow = ActRow + i
Application.StatusBar = "Number of records processed: " & i & " of " & LastRow & ": " & Format(i / LastRow, "0%")

Next

End Sub

Open in new window

The other is just to make it a global variable by placing it first in the module outside of any procedures, like:

Dim session

Sub MainStream()
. . .

Open in new window


»bp

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
csehzIT consultantAuthor Commented:
Thank you that is brilliant, as I understand more variables should be transferred between of subs as being on the attached picture:

More parameters
csehzIT consultantAuthor Commented:
.
Bill PrewIT / Software Engineering ConsultantCommented:
Welcome, yes, if there are any variables that are needed in the called procedure they can be passed.  It's somewhat cleaner to pass variables rather than define them globally, but in other cases it can make sense to be global.  One of the drawbacks to global vars is that it is hard to know where they are being changed in the body of code that uses them.  Whereas local variables that are passed to called routines can be a little more obvious, especially if you start to use ByRef and ByVal in the parms passing (ByValue does not allow the called procedure to change the variables value in the calling procedure, while ByRef does).


»bp
csehzIT consultantAuthor Commented:
Thank you just again, I learnt so lot by this simple question
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.