Module not seeing functions in ThisWorkbook
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.
Code:
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.
Code:
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.
