PDA

View Full Version : Module not seeing functions in ThisWorkbook



ctmurray1
01-04-2008, 12:16 PM
I may have accidently sent my query before I was done - sorry if I did.

The code below is in MyWorkbook, in module 2. It calls several functions (Fileopen, SummarySheetMan, Copy_Paste_to_PowerPoint) which are located in the "ThisWorksheet" code section of the same MyWorkbook. Last week this all ran fine but now it stops at the first function and says it cannot find the function (I am assuming it will not find any of the funtions). If I copy all the functions into module 2 then it works fine (all functions work). Is there some code I need to put in module 2 or in ThisWorksheet to make these functions visible / available? I would prefer to not have to copy and paste all the functions into module 2 if it can be avoided.


Sub RunThisMacro()
'Modular form of the SummarySheet macro.
Dim isheet
Dim start
Dim pvalue
Dim rvalue
Dim svalue
Dim iplace
Dim ispot
Dim irow
Dim i As Integer

On Error GoTo Errhandler

Application.ScreenUpdating = False
'turn off screen updates

'Code here uses FileOpen Subroutine to automatically populate each bond
'spreadsheet with latest electrical data

isheet = Worksheets.Count

'MsgBox isheet
For i = 2 To isheet
Sheets(i).Select
Fileopen
' Open the subfiles and copy data into subworksheets of this workbook
Next i
SummarySheetMan

' Run the manual Summary Sheet macro
'copy the bar chart (chart 2) to Powerpoint

Copy_Paste_to_PowerPoint

Application.ScreenUpdating = True

Exit Sub
Errhandler:

Select Case Err

Case 1004: 'File open or path error
MsgBox "Error" & Err & " : " & Error(Err)

Case Else: ' An error other than 68, 75 or 76 has occurred.
' Display the error number and the error text.
MsgBox "Error # " & Err & " : " & Error(Err)

End Select

End Sub
Below is the code in that calls the RunThisMacro which is in MyWorkbook above. This is in a different workbook (MyStartingWorkbook) and is using the Application.Run command to open MyWorkbook above and have the RunThisMacro start its work.


Sub RunMacro_NoArgs()
'Macro purpose: Use the application.run method to execute
'a macro without arguments from another workbook

Dim PathToFile As String, _
NameOfFile As String, _
wbTarget As Workbook, _
CloseIt As Boolean
Dim namelength As Integer
Dim rng As Range
Dim irow As Integer
Dim iLastRow As Integer
Dim iLastCol As Integer
Dim filetoOpen As String
Dim action As Integer

'Set file name and location. You will need to update this info!
'NameOfFile = "Book-1.xls"
' PathToFile = "C:\Documents and Settings\us265779\My Documents\My Code"

' Determine the last row in the open file
Set rng = Range("A1").SpecialCells(xlCellTypeLastCell)
iLastRow = rng.Row
iLastCol = rng.Column

' MsgBox iLastRow
' First row to read is 10, last is iLastRow
irow = 10
Do While irow < iLastRow + 1
' Do While irow < 15

' Select the row with a filename contained and action to perform

Rows(irow).Select
action = Range("A" & irow)
filetoOpen = Range("B" & irow)

' Read in the action to perform from irow, column A
' Read in the file name from irow, column B
' If filetoOpen = "" Then GoTo Line1


'Attempt to set the target workbook to a variable. If an error is
'generated, then the workbook is not open, so open it
On Error Resume Next
'Set wbTarget = Workbooks(NameOfFile)
Set wbTarget = Workbooks(filetoOpen)

If Err.Number <> 0 Then
'Open the workbook
Err.Clear
'Set wbTarget = Workbooks.Open(PathToFile & "\" & NameOfFile)
Set wbTarget = Workbooks.Open(filetoOpen)
CloseIt = True
End If

'Check and make sure workbook was opened
If Err.Number = 1004 Then
MsgBox "Sorry, but the file you specified does not exist!" _
& vbNewLine & PathToFile & "\" & NameOfFile
Exit Sub
End If
On Error GoTo 0

' Application.Run "'" & wbTarget.Name & "'!ThisWorkbook.Opening"

If action = 1 Then
Application.Run "'" & wbTarget.Name & "'!RunThisMacro"
'MsgBox "RunThisMacro"
Else
Sheets("Summary Sheet").Select
Application.Run "'" & wbTarget.Name & "'!ThisWorkbook.Copy_Paste_to_PowerPoint"
'MsgBox "Copy_Paste_to_Powerpoint"
End If

'special use of single quotes to make the application.run work with files containing dashes in
'their names. Bug in Excel with the standard form of command.

If CloseIt = True Then
'If the target workbook was opened by the macro, close it
wbTarget.Close savechanges:=False
Else
'If the target workbook was already open, reactivate this workbook
ThisWorkbook.Activate
End If

irow = irow + 1
Loop


End Sub
Not sure why this ran fine last week. I might have initiated MyStartingWorkbook from a different location. I might have had some other Excel workbooks open at the time that contained this same function. Today I only have MyStartingWorkbook open.

mikerickson
01-04-2008, 04:07 PM
If they are being called from multiple locations, put your functions in a normal module rather than an object's code module. Take the function out of ThisWorkbook and put it in Module1 (or which ever.)

ctmurray1
01-05-2008, 08:22 PM
Moving macros to module 2 worked. I had 42 workbooks where I imported the code as module 2. Thanks for the suggestion.