CODEWITHSUNDEEP
excelvba
PeteBradshaw
PeteBradshaw

Reputation: 111

How to call a Excel VBA procedure through late binding

I'm looking for a bit of advice here.

I have a spreadsheet with various modules and procedures that can be called from a Worksheet_Change event. This causes problems when I need to issue a sheet from the workbook for other users to complete.

Whenever the user tries to update the sheet, the on change event gets triggered, causing a compile error as the procedure being called does not exist, and this cannot be trapped (as far as I'm aware). I've tried using Application.EnableEvents = False, but this is in the worksheet event and the code breaks as soon as the event is triggered.

Is there anyway to call a procedure through late binding where I can trap the error?

I'm trying something like this at the moment.

Dim mdl as object

' Test for module in workbook, if error, then exit routine
Set mdl = Application.ActiveWorkbook.VBProject.VBComponents("mdlSharedFunctions")

'If no error, then call procedure here
call mdl.UpdateData(Target)

'Or
Application.Run mdl.UpdateData(Target)

Neither of these call methods will work and I'm hoping someone out there will be able to point me in the right direction.

Cheers

Pete

Upvotes: 0

Views: 712

Answers (1)

MattCrum
MattCrum

Reputation: 1110

You can use a global variable as a flag - bit dirty but it works fine. Then add an If flag = true then statement to the change event sub.

Public globalflag as Boolean

Sub test1()

If globalflag = True Then
    BrokenSub 'This sub has an invalid sub/function referenced, but will be ignored if the flag is set to false
Else
    'Don't run the code
    Exit Sub
End If

End Sub

Sub BrokenSub()
invalidfunction ("asb")
End Sub

EDIT

To put it in a worksheet, just see if the variable exists:

Declare this in a module in your master spreadsheet:

Public globalflag as Boolean

Then in your worksheet code

If not IsEmpty(globalflag) Then
    BrokenSub 'Put your master spreadsheet code here - it'll run if globalflag exists and be ignored if it doesn't
End If

Sub BrokenSub()
invalidfunction ("asb")
End Sub

Upvotes: 0

Related Questions