Calling a function/sub

[KarimK9]

I've a code that does some calculations and changes to cells when specific row changes are detected. Here is the Code:

                Target.Formula = "=IF(" & CLng(Target.Value) & " < TODAY()," & _
                "DATEDIF(" & CLng(Target.Value) & ",TODAY(),""d"") & "" Day(s) Ago""," & _
                "DATEDIF(TODAY()," & CLng(Target.Value) & ",""d"") & "" Day(s) Ahead"")"

The problem is that I want to use that code in multiple areas when certain conditions are met. Currently I've that code pasted 4 times and it works, but creates redundancy. I want to create a (Sub/Function) to have this code and call it whenever the conditions are met. For example:

Private Sub Calculate_Date(ByVal Target As Range)
    On Error Resume Next
    If Not Intersect(Target, Range("L:L")) Is Nothing Then
        With ActiveWorkbook.Worksheets("Interested")
            Application.EnableEvents = False
            >>> Call The Code Here <<<
            Application.EnableEvents = True
        End With
    End If

I tried to create a sub and call it but nothing changes, it doesn't call the sub at all.

Many Thanks!.

 

[JLGWhiz]

Procedures that are called by other procedures must be installed in one of the public code modules (the ones you insert like Module1). You cannot call a Private Sub, however, a Priviate Sub can call any sub procedure from a public code module. The called procedure must have a title line and End Sub line, Assume you have this code in the 'ThisWorkbook' code module.

Private Sub Workbook_Open()
myCalledProcedure
End Sub

Then in a public code mocule you have

Sub myCalledPocedure()
MsgBox "Hello There!"
End Sub

Then everytime you open that workbook it would display "Hello There" in a message box generated by the called procedure.

The use of Private in front of a procedure name should be limited to code in worksheet code modules, UserForm code modules and the ThisWorkbook code module. Using it anywhere else can get undesired results, since it implies certain restrictions within the VBA underlying application.

 

[KarimK9]

Procedures that are called by other procedures must be installed in one of the public code modules (the ones you insert like Module1). You cannot call a Private Sub, however, a Priviate Sub can call any sub procedure from a public code module. The called procedure must have a title line and End Sub line, Assume you have this code in the 'ThisWorkbook' code module.

Private Sub Workbook_Open()
myCalledProcedure
End Sub

Then in a public code mocule you have

Sub myCalledPocedure()
MsgBox "Hello There!"
End Sub

Then everytime you open that workbook it would display "Hello There" in a message box generated by the called procedure.

Okay, I did the exact same thing and it didnt work.

Now I found out why and I feel really embarrassed, for some reason suddenly excel decided to disable VBA Macros, I enabled it and it works perfectly now. Thanks!

 

[JLGWhiz]

You're welcome,
Regards, JLG