Solved: Excel VBA Error Handling: Sub or Funcion not defined

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

MRdNk

Thread Starter
Joined
Apr 7, 2007
Messages
439
Currently I have an add-in that includes an "Export Worksheet" (menu item option) - this works perfectly.

However, one of my users has a workbook that includes vba code behind the worksheet, which then refers to vba 'module' procedure - however when the export worksheete is exported, it exports the worksheet, but not the macro.

The obvious way around would be just to include the macro code in the Add-in, however, what I need is more of an error handling function, I need the worksheet code to simple IGNORE or error message if the procedure doesn't exist.

Not sure how to do it!?

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo HandleError0


Application.ScreenUpdating = False
    
    If Not Intersect(Target, Range("FORM_CHGTYPE")) Is Nothing Then
        [COLOR="Red"]projectprogramme[/COLOR]
    End If

Application.ScreenUpdating = True

Done:
    Exit Sub
    
HandleError0:
    GoTo Done

End Sub
Breaks on the red higlighted section, with:
Compile Error:
Sub or Function noy defined.
And the current error handling doesn't stop the code from executing.
 
Joined
Oct 20, 2004
Messages
7,837
You don't name the add-in. Is it ASAP? I just ask because the code for it is protected, so we don't really know specifically how it is interacting with your other code.
If it is some add-in that isn't protected, perhaps you could post that export code?
And I assume that projectprogramme is a defined procedure that is accessible to this code .
 

MRdNk

Thread Starter
Joined
Apr 7, 2007
Messages
439
Apologies the add-in was written by myself, which basically copies the worksheet to a new workbook and breaks any links to other workbooks, this has been made so that sheets from various workbooks (managing workloads etc) need to be exportable with any links broken to make sure its only refering to itself.

"projectprogramme" was also written by me, and is accessible in its original workbook, but not available when the worksheet is exported to a new workbook, as its a module / procedural code, however the worksheet uses the On change event to trigger the module code - which doesn't get copied, and therefore errors when trying to call the module code that no longer exists.

I want it to say if not module in this workbook then ignore; or alternatively remove any validation from worksheet when exporting.

Class Code (vba In Add-in):
Code:
Function ExportWorksheet() As Boolean
    On Error GoTo HandleError0
    
    Application.ScreenUpdating = False
    
    Dim wkbSource As Workbook
    Dim wksSource As Worksheet
    
    Dim wkbDestination As Workbook
    
    Set wkbSource = ActiveWorkbook
    Set wksSource = wkbSource.ActiveSheet
    
    wksSource.Copy
    
    Set wkbDestination = ActiveWorkbook
    
    If UseBreakLink(wkbDestination) = True Then
    End If
    
    'MsgBox "Export Complete"

Done:
    Application.ScreenUpdating = True
    Exit Function

HandleError0:
    MsgBox "Error (dawExpWks100): " & Err.Description & " (" & Err.Number & Err.Source & ")", vbCritical
    GoTo Done
    
End Function

Run via:
Code:
Sub ExportWorksheet()
    Dim cDAW As clsDAWFunctions
    Set cDAW = New clsDAWFunctions
    
    cDAW.ExportWorksheet
End Sub
NB. The add-in automatically adds a menu item before "Help" that include the Export Worksheet button (macro).
 
Joined
Jul 25, 2004
Messages
5,458
This is part of the problem when exporting worksheets like this. Just go through and delete the code programmatically when you export the worksheet. You don't need the code anymore right? So just take out all code in that worksheet module. Here is an example...

Code:
Option Explicit

Sub DeleteAllCodeInModule(ByVal strModName As String)
    'Originally from: http://www.cpearson.com/Excel/vbe.htm#DeleteAllCode
    'Reference set to: Microsoft Visual Basic for Applications Extensibility 5.x
    Dim modVBCode        As CodeModule
    Dim iCountLines      As Long
    Const iStart As Long = 1
    On Error Resume Next
    Set modVBCode = ThisWorkbook.VBProject.VBComponents(strModName).CodeModule
    If Err.Number <> 0 Then GoTo ExitEarly
    iCountLines = modVBCode.CountOfLines
    modVBCode.DeleteLines iStart, iCountLines
ExitEarly:
End Sub

Sub TestDelete()
    Call DeleteAllCodeInModule("Sheet1")
End Sub
This originally came from Chip Pearson, and I edited it for my own usings. There is a link to his website to see the original procedure. I like passing the module name to it, makes the routine more portable for me. There isn't much error handling setup either.

HTH
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Members online

Top