?
Solved

Excel : How To call an Excel Sub from vb6

Posted on 2003-03-13
4
Medium Priority
?
144 Views
Last Modified: 2010-04-07
Hi EE's
   Using vb6.0
I have a reference for an opened excel.worksheet   (say xlSheet)
How to call a stored sub inside it giving it parameters

If possible in excel 97  , 2000 , xp
thanks
0
Comment
Question by:Bahnass
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 5

Accepted Solution

by:
Cimperiali earned 200 total points
ID: 8126760
http://www.codeguru.com/forum/showthread.php?s=&threadid=200305&highlight=excel

'this is vb code inside a form
'where there is a command bbutton and a reference to
'Microsoft Excel (highest number) object library

Option Explicit

Private Sub cmdRun_Click()
Dim myXlApp As Excel.Application
Dim myXlWbook As Excel.Workbook
Dim retVar As Boolean
Set myXlApp = New Excel.Application
'the xls cartel is named "TheMacroSheet" and is located
'in same folder as this vb Pgm
Set myXlWbook = myXlApp.Workbooks.Open(App.Path & "\theMacrosheet.xls")
retVar = Cbool(myXlWbook.Application.Run("MyMacro", "Tom", 12))
myXlWbook.Close xlDoNotSaveChanges
Set myXlWbook = Nothing
myXlApp.Workbooks.Close
myXlApp.Quit
Set myXlApp = Nothing
MsgBox retVar
End Sub
'-----------
'macro code (=a public function in excel Module)
''This is macro in an excel module:
'Public Function MyMacro(strTheName As String, lngTheNumber As Long) As Integer
'If strTheName <> "" Then
'    MsgBox strTheName
'End If
'If lngTheNumber > 0 Then
'    MyMacro = -1
'End If
'End Function

 
 
 
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 8126892
xlSheet.run "YourSub", p1, p2 , p3

(in which px are parameters).
0
 
LVL 3

Author Comment

by:Bahnass
ID: 8127277
Thanks a lot
0
 
LVL 5

Expert Comment

by:Cimperiali
ID: 8127351
;-)
You're welcome!
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses
Course of the Month10 days, 8 hours left to enroll

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question