Go Back  Xtreme Visual Basic Talk > Legacy Visual Basic (VB 4/5/6) > VBA / Office Integration > Word, PowerPoint, Outlook, and Other Office Products > call function/sub from another worksheet

Thread Tools Display Modes
Old 03-07-2003, 05:02 PM
Posts: n/a
Question call function/sub from another worksheet

Can you stand another question from yet another VBA newbie?

While it appears one can call a function or a subroutine from another module, I would like to know if it is possible to call another function or subroutine from another Excel spreadsheet.

I have a "Main" code "attached" to the "Main" worksheet of a workbook. This codes reads in the name of the "Target" worksheet (which the user has input into the appropriate cell in the Main worksheet). It then opens the results files from another program, one at a time, in a DO loop (the user has also input the results files' ID numbers into the appropriate cells in the Main worksheet).

' Open first results file.
Set Results = Network.ResultSet("res_" & Worksheets("Main").Range("b15"))

' Set target worksheet.
DestWorksheet = Worksheets("Main").Range("b18")

' Start inputing data at column 'C' on target worksheet.
c = 3

' Collect Component result data and place in appropriate cells.
Do While Worksheets("Main").Cells(15, c - 1) <> 0


'Call the function that collects the Flowmaster data.
Call function_on_target_worksheet(c)

'Close current results file and open the next one.
Set Results = Nothing
Set Results = Network.ResultSet("res_" & Worksheets("Main").Cells(15, c))

'Add to column counter
c = c + 1


The function is meant to collect the results and place them in the Target worksheet, in a particular order, so that I can
-get a nice little table that can be cut and pasted in to reports;
-do further calculations;

Because each Target worksheet can have a different look or format, before I thought of trying a function, I was forced to update my Main code for every Target worksheet. I was hoping that by "attaching" a function to each Target worksheet and calling it from the "Main" code this would save me some time and work ('cause I'm lazy and in a hurry). But to no avail: I keep getting "Compile error: Sub or Function not defined".

Will someone please show me the way? Or, am I expecting to much from Excel?
Reply With Quote
Old 03-07-2003, 05:32 PM
Wamphyri's Avatar
Wamphyri Wamphyri is offline
Variable not defined

Retired Moderator
* Guru *
Join Date: Apr 2002
Location: Ottawa, Ontario
Posts: 4,793

If you are calling the sub MySheet2Sub located in Sheet2 from Sheet1 you would use

Reply With Quote
Old 03-10-2003, 09:38 AM
Posts: n/a

Thanks for your prompt response, Carl. And thanks for the answer: it worked!
Reply With Quote

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Algorithm Problems Arkasizer Game Programming 5 12-18-2002 08:37 AM
Loading a Sprite Termor Game Programming 14 12-07-2002 08:37 AM
Quickie Code Contest BillSoo General 75 03-20-2002 12:39 PM
Saving as Random Access File VillageBaboon General 5 06-14-2001 10:25 PM
Database design - Step 1 help required chansen Database and Reporting 5 10-27-2000 05:44 PM


Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..