Cycle through w/s subs within excel from ..._Calculate() sub

[RodP]

Hi everyone.

I'm a little stuck and was wondering if someone could advise...I'm creating a script which writes a new script to a worksheet code-sheet within excel. I'm using the following code to do this where str_S_CFvb contains the created subroutine:

Workbooks("book2.xls").VBProject.VBComponents(Sheets("sheet3").CodeName).CodeModule.AddFromString str_S_CF_vb

The routine needs to be renamed to Sub CheckCells_[rangename](). I presume that you do this within the above string???

The [rangename] will be a custom name and will relate to the range that the sub routine actually checks within the worksheet.

The routine is activated by the ...calculation() subrountine in the sheet code:

Private Sub Worksheet_Calculate()
Me.OnCalculate = ActiveSheet.Name & ".CheckCells"

However I want a dynamic way of getting the ...calculate() sub routine to cycle through all subroutines on the same sheet that begin with 'Checkcells' as there may be a few sets of ranges (therefore subrountines) present. I'm presuming it'll be a variant to the uppermost bit of code posted but I'm not sure.

Hope someone can help and many thanks in advance

RodP

 

[PHV]

Have a look at the Replace function (xl2k or above).

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[RodP]

Ok, that's great. I am using excel 2k. I'll read up on the replace function.

However, I'm still stuck with checking that the range exists (as i presume that the replace function will only work is something exists already????)

I think the main help I need to is to figure out how to dynamically scan/loop through all the sub routines within a worksheet and execute them if they begin with 'checkcells'.

So far I've found some code which provides function/subroutine level data but i'm not sure how to control the worksheet it looks at - it just looks at the whole workbook at the moment. The VBcomp.name provides (I think the worksheet name) although, the procedure seems to miss out lots of subroutines and functions I have within each worksheet. Does anyone know why this is? Please feel free to try the code out and tell me what you get.

Any advice would be greatly appreciated
------------------
Dim VBComp As VBComponent
Dim Msg As String
Dim VBCodeMod As CodeModule
Dim StartLine As Long
Dim strTemp As String

For Each VBComp In ThisWorkbook.VBProject.VBComponents
Set VBCodeMod = ThisWorkbook.VBProject.VBComponents(VBComp.Name).CodeModule
With VBCodeMod
StartLine = .CountOfDeclarationLines + 1
Do Until StartLine >= .CountOfLines
strTemp = .ProcOfLine(StartLine, vbext_pk_Proc) & vbNewLine
StartLine = StartLine + _
.ProcCountLines(.ProcOfLine(StartLine, vbext_pk_Proc), vbext_pk_Proc)
Loop
End With

MsgBox VBComp.Name & " " & strTemp

Next VBComp

------------------
Thanks!

RodP

Note: you'll need to ensure that MS VBA Extensibility library under REFERENCES is checked from within excel VBA editor.

 

[RodP]

Hi everyone,

Just wondered if anyone is able to help with the above yet? Although I've been reading around lots I'm still not sure how to solve this problem. Hope someone out there can help.

Many thanks in advance (indeed!)

RodP

 

[FaneDuru]

I am affraid that it is not possible to trigger and run a subroutine from the sheet codepane...
If you create a module "Tests" you may use the next code in order to run all routines containing "test" in their name:

Sub Subroutines()
Dim VBComp As VBComponent, VBCodeMod As CodeModule, StartLine As Long
Dim strTemp As String, Msg As String
'Reference to "Microsoft Visual Basic Extensibility.5.3"
For Each VBComp In ThisWorkbook.VBProject.VBComponents
  If VBComp.Name = "Tests" Then
    Set VBCodeMod = ThisWorkbook.VBProject.VBComponents(VBComp.Name).CodeModule
       With VBCodeMod
            StartLine = .CountOfDeclarationLines + 1
            Do Until StartLine >= .CountOfLines
              strTemp = .ProcOfLine(StartLine, vbext_pk_Proc) & vbNewLine
              If InStr(strTemp, "test") > 0 Then Application.Run (ThisWorkbook.Name & _
                                                "!" & Left(strTemp, Len(strTemp) - 2))
              strTemp = ""
              StartLine = StartLine + _
                .ProcCountLines(.ProcOfLine(StartLine, vbext_pk_Proc), vbext_pk_Proc)
            Loop
        End With
  End If
Next VBComp
End Sub
Sub test()
 Debug.Print Date, "from test"
End Sub
Sub Example()
 Debug.Print "This is nothing"
End Sub
Sub test2()
 Application.Run (ThisWorkbook.Name & "!test4")
 Debug.Print "from test 2"
End Sub
Sub test4()
 Debug.Print Now, "from test4"
End Sub

Fane Duru