Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Excel : How To call an Excel Sub from vb6

Posted on 2003-03-13
4
Medium Priority
?
145 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
  • 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
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…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses
Course of the Month10 days, 22 hours left to enroll

572 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