Call all procedures of a worksheet module that relate to the active worksheet name

[martinequilibrium]

Dear Users,

I'm trying to run the code here:

Stack Overflow

When you press a button it checks the active worksheet and tries to run all the code in the worksheet object in Microsof Excel Objects that has the same name.
I tried running it but I get a Subscript Out of Range Error.

Thanks

'~~> Code adapted from http://www.cpearson.com/excel/vbe.aspx
Sub CallModule()
    Dim VBProj As VBIDE.VBProject
    Dim VBComp As VBIDE.VBComponent
    Dim CodeMod As VBIDE.CodeModule
    Dim LineNum As Long, NumLines As Long
    Dim ProcName As String
    Dim ProcKind As VBIDE.vbext_ProcKind
    Dim MyAr() As String
    Dim n As Long
    Dim ModuleName As String


    ' Aca ponemos el module name como
    ModuleName = ActiveSheet.Name
    Set VBProj = ActiveWorkbook.VBProject
    Set VBComp = VBProj.VBComponents(ModuleName)
    Set CodeMod = VBComp.CodeModule


    With CodeMod
        LineNum = .CountOfDeclarationLines + 1
        Do Until LineNum >= .CountOfLines
        ReDim Preserve MyAr(n)
            ProcName = .ProcOfLine(LineNum, ProcKind)


            '~~> Store the routine names in an array
            MyAr(n) = ProcName
            n = n + 1


            LineNum = .ProcStartLine(ProcName, ProcKind) + _
                    .ProcCountLines(ProcName, ProcKind) + 1
        Loop
    End With


    '~~> This is where I am running every routine from Module1
    For n = LBound(MyAr) To UBound(MyAr)
        Run ModuleName & "." & MyAr(n)
    Next n
End Sub


Function ProcKindString(ProcKind As VBIDE.vbext_ProcKind) As String
    Select Case ProcKind
        Case vbext_pk_Get
            ProcKindString = "Property Get"
        Case vbext_pk_Let
            ProcKindString = "Property Let"
        Case vbext_pk_Set
            ProcKindString = "Property Set"
        Case vbext_pk_Proc
            ProcKindString = "Sub Or Function"
        Case Else
            ProcKindString = "Unknown Type: " & CStr(ProcKind)
    End Select
End Function

 

[Norie]

Where do you get the error?

By the way, why don't you just call the macros in the usual way?

 

[martinequilibrium]

I'm getting the error here
Set VBComp = VBProj.VBComponents(ModuleName)

I also tried something simpler like this
Call ActiveWorksheet.RestoreFormulas
which is the name of a procedure in a module and it says "Object Required"

I'm trying to call some code asociated with a sheet and which will only work with that sheet.

I have a button that does something general, but should also do something specific for the sheet.

Another possibility is use two buttons, one for a general proceduer and another for a particular procedure.

 

[Norie]

Does the code need to be in a sheet module?

If it does make sure it's not declared as Private and use it's codename instead of ActiveWorksheet.

PS Where are you trying to call the code from?

 

[martinequilibrium]

Changed From Private to Public the Procedure in the Worksheet but the errors are the same.

I'm trying to call the code from a regular module

Something like this:

Public Sub Remove()
' A esta la llama el boton borrar datos
RemoveTableBodyData
'RemoveExtraColumns
Call ActiveWorksheet.RestoreFormulas ====
'RestaurarFormulas
'CallModule ====
End Sub

 

[Norie]

There is no such thing as ActiveWorksheet.

Change ActiveWorksheet to the codename of the sheet the macro you want to call is in.

PS Why isn't the code in a regular module anyway? The only code that should really go in a sheet module is event code for the sheet.

 

[martinequilibrium]

Manage to do it using the following code to refer to the VBA Name of the page

Sub AChangeOfName()


' Con esto se cambia el valor de el nombre de la hoja en propiedades
'ThisWorkbook.VBProject.VBComponents("Sheet1").Name = "Mudface"
'change the codename for Sheet1 to Mudface


   Dim WS_Count As Integer
   Dim I As Integer


   ' Set WS_Count equal to the number of worksheets in the active
   ' workbook.
   WS_Count = ActiveWorkbook.Worksheets.Count


   ' Begin the loop.
   For I = 1 To WS_Count


      ' Insert your code here.
      ' The following line shows how to reference a sheet within
      ' the loop by displaying the worksheet name in a dialog box.
      'MsgBox ActiveWorkbook.Worksheets(I).Name
      With ActiveWorkbook.Worksheets(I)
        .Parent.VBProject.VBComponents(.CodeName) _
                .Properties("_CodeName") = Trim(ActiveWorkbook.Worksheets(I).Name)
      End With
   Next I


End Sub

Then you just change the code in the CallModule procedure
If you rename the sheet you need to rerun the procedure (possible to run it with events like opening the worksheet)

ModuleName = Trim(ActiveSheet.Name)

Thanks for the help!

 

[Norie]

Did you try using the codename of the sheet as I suggested?

 

[Norie]

Actually, can I ask what the code in post #7 is meant to do?