Get macro name in variable

User avatar
Rudi
gamma jay
Posts: 25455
Joined: 17 Mar 2010, 17:33
Location: Cape Town

Get macro name in variable

Post by Rudi »

Hi,

I have a few dozen macros in a workbook and need to identify which macro filters which rows in a large list.
In each macro I need to collect the last two name chars of the macro into a variable

For example:
One macro is called: Filter2Z, another called: Filter2NZ, another called Filter11Z. (They are all prefixed with "Filter")
I need to get the part after "Filter" into a variable so I can assign it to a cell in the sheet.
EG: Cell A1 = 2Z, cell B1 = 2NZ, cell C1 = 11Z, .......

How do i get a macro name into a variable?
Note: I don't need a loop. I just need the name of the current running macro in a variable. I will run each of the few dozen macros individually.

TX
Regards,
Rudi

If your absence does not affect them, your presence didn't matter.
Top
User avatar
HansV
Administrator
Posts: 78530
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Get macro name in variable

Post by HansV »

I fear that you'll have to code it manually. (And your description contradicts the requirement of "the last two name chars" - sometimes it's three)

Code: Select all

Public strMacroName As String

Sub Filter2Z()
    strMacroName = "2Z"
    ...
End Sub

Sub Filter2NZ()
    strMacroName = "2NZ"
    ...
End Sub

...
Best wishes,
Hans
Top
User avatar
Rudi
gamma jay
Posts: 25455
Joined: 17 Mar 2010, 17:33
Location: Cape Town

Re: Get macro name in variable

Post by Rudi »

Sorry...overlooked that contradiction...

That's a pity...of all the things macros can do, it cannot identify itself?? (Bummer)
Its OK. Not a train smash, but would have been easier.

TX
Regards,
Rudi

If your absence does not affect them, your presence didn't matter.
Top
User avatar
HansV
Administrator
Posts: 78530
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Get macro name in variable

Post by HansV »

If the macros have a lot in common, you could try something like this:

Code: Select all

Public strMacroName As String

Sub Filter2Z()
    Call FilterSub("2Z")
End Sub

Sub Filter2NZ()
    Call FilterSub("2NZ")
End Sub

...

Sub FilterSub(Suffix As String)
    strMacroName = Suffix
    Select Case Suffix
        Case "2Z"
            ...
        Case "2NZ"
            ...
        Case ...
            ...
    End Select
    ' Common code here
    ...
End Sub
Best wishes,
Hans
Top
User avatar
Rudi
gamma jay
Posts: 25455
Joined: 17 Mar 2010, 17:33
Location: Cape Town

Re: Get macro name in variable

Post by Rudi »

TX. That suggestion is useful, but the solution was simple to write it in (in the current scenario)...

Below is the code (modified) from one module in the WB.
There are a few more modules, but I've put my head and heart into it and am almost finished with the conversion...
Cheers

Code: Select all

Option Explicit

Private Sub Filter3Z()
Dim myCalc As Range
Dim F1 As Single, F2 As Single, F3 As Single, F4 As Single, F5 As Single

    Set myCalc = Sheets("SrcData").Range("J1000000").End(xlUp)
    Application.ScreenUpdating = False
    Sheets("SrcData").AutoFilterMode = False
    Range("A5").CurrentRegion.AutoFilter Field:=8, Criteria1:="ORDINARY SHARES"
    Range("A5").CurrentRegion.AutoFilter Field:=13, Criteria1:="ZAR"
    Range("A5").CurrentRegion.AutoFilter Field:=18, Criteria1:="L"
    Columns("S").Insert
    Columns("S").ColumnWidth = 4
    Range("A5").CurrentRegion.Offset(0, Range("A5").CurrentRegion.Columns.Count).Columns(1).Interior.ColorIndex = Int((50 * Rnd) + 1)
    Range("S5").Value = "3Z1"
    F1 = myCalc.Value
    Sheets("SrcData").AutoFilterMode = False
    Range("A5").CurrentRegion.AutoFilter Field:=8, Criteria1:="Preference Shares"
    Range("A5").CurrentRegion.AutoFilter Field:=13, Criteria1:="ZAR"
    Range("A5").CurrentRegion.AutoFilter Field:=18, Criteria1:="L"
    Columns("S").Insert
    Columns("S").ColumnWidth = 4
    Range("A5").CurrentRegion.Offset(0, Range("A5").CurrentRegion.Columns.Count).Columns(1).Interior.ColorIndex = Int((50 * Rnd) + 1)
    Range("S5").Value = "3Z2"
    F2 = myCalc.Value

    Sheets("SrcData").AutoFilterMode = False
    Range("A5").CurrentRegion.AutoFilter Field:=8, Criteria1:="ORDINARY SHARES"
    Range("A5").CurrentRegion.AutoFilter Field:=14, Criteria1:="Unit Trust"
    Range("A5").CurrentRegion.AutoFilter Field:=13, Criteria1:="ZAR"
    Range("A5").CurrentRegion.AutoFilter Field:=18, Criteria1:="L"
    Columns("S").Insert
    Columns("S").ColumnWidth = 4
    Range("A5").CurrentRegion.Offset(0, Range("A5").CurrentRegion.Columns.Count).Columns(1).Interior.ColorIndex = Int((50 * Rnd) + 1)
    Range("S5").Value = "3Z3"
    F3 = myCalc.Value
    Sheets("SrcData").AutoFilterMode = False
    Range("A5").CurrentRegion.AutoFilter Field:=8, Criteria1:="ORDINARY SHARES"
    Range("A5").CurrentRegion.AutoFilter Field:=14, Criteria1:="Variable Rate"
    Range("A5").CurrentRegion.AutoFilter Field:=13, Criteria1:="ZAR"
    Range("A5").CurrentRegion.AutoFilter Field:=18, Criteria1:="L"
    Columns("S").Insert
    Columns("S").ColumnWidth = 4
    Range("A5").CurrentRegion.Offset(0, Range("A5").CurrentRegion.Columns.Count).Columns(1).Interior.ColorIndex = Int((50 * Rnd) + 1)
    Range("S5").Value = "3Z4"
    F4 = myCalc.Value
    Sheets("SrcData").AutoFilterMode = False
    Range("A5").CurrentRegion.AutoFilter Field:=8, Criteria1:="ORDINARY SHARES"
    Range("A5").CurrentRegion.AutoFilter Field:=15, Criteria1:="Exchange Trades Funds"
    Range("A5").CurrentRegion.AutoFilter Field:=13, Criteria1:="ZAR"
    Range("A5").CurrentRegion.AutoFilter Field:=18, Criteria1:="L"
    Columns("S").Insert
    Columns("S").ColumnWidth = 4
    Range("A5").CurrentRegion.Offset(0, Range("A5").CurrentRegion.Columns.Count).Columns(1).Interior.ColorIndex = Int((50 * Rnd) + 1)
    Range("S5").Value = "3Z5"
    F5 = myCalc.Value

    Sheets("SrcData").AutoFilterMode = False
    Range("A1").Select
    Sheets("Report").Range("B3").Value = F1 + F2 - F3 - F4 - F5
    Application.ScreenUpdating = True
End Sub
Private Sub Filter3NZ()
Dim myCalc As Range
Dim F1 As Single, F2 As Single, F3 As Single, F4 As Single, F5 As Single

    Set myCalc = Sheets("SrcData").Range("J1000000").End(xlUp)
    Application.ScreenUpdating = False
    Sheets("SrcData").AutoFilterMode = False
    Range("A5").CurrentRegion.AutoFilter Field:=8, Criteria1:="ORDINARY SHARES"
    Range("A5").CurrentRegion.AutoFilter Field:=13, Criteria1:="<>ZAR"
    Range("A5").CurrentRegion.AutoFilter Field:=18, Criteria1:="L"
    Columns("S").Insert
    Columns("S").ColumnWidth = 4
    Range("A5").CurrentRegion.Offset(0, Range("A5").CurrentRegion.Columns.Count).Columns(1).Interior.ColorIndex = Int((50 * Rnd) + 1)
    Range("S5").Value = "3NZ1"
    F1 = myCalc.Value
    Sheets("SrcData").AutoFilterMode = False
    Range("A5").CurrentRegion.AutoFilter Field:=8, Criteria1:="Preference Shares"
    Range("A5").CurrentRegion.AutoFilter Field:=13, Criteria1:="<>ZAR"
    Range("A5").CurrentRegion.AutoFilter Field:=18, Criteria1:="L"
    Columns("S").Insert
    Columns("S").ColumnWidth = 4
    Range("A5").CurrentRegion.Offset(0, Range("A5").CurrentRegion.Columns.Count).Columns(1).Interior.ColorIndex = Int((50 * Rnd) + 1)
    Range("S5").Value = "3NZ2"
    F2 = myCalc.Value

    Sheets("SrcData").AutoFilterMode = False
    Range("A5").CurrentRegion.AutoFilter Field:=8, Criteria1:="ORDINARY SHARES"
    Range("A5").CurrentRegion.AutoFilter Field:=14, Criteria1:="Unit Trust"
    Range("A5").CurrentRegion.AutoFilter Field:=13, Criteria1:="<>ZAR"
    Range("A5").CurrentRegion.AutoFilter Field:=18, Criteria1:="L"
    Columns("S").Insert
    Columns("S").ColumnWidth = 4
    Range("A5").CurrentRegion.Offset(0, Range("A5").CurrentRegion.Columns.Count).Columns(1).Interior.ColorIndex = Int((50 * Rnd) + 1)
    Range("S5").Value = "3NZ3"
    F3 = myCalc.Value
    Sheets("SrcData").AutoFilterMode = False
    Range("A5").CurrentRegion.AutoFilter Field:=8, Criteria1:="ORDINARY SHARES"
    Range("A5").CurrentRegion.AutoFilter Field:=14, Criteria1:="Variable Rate"
    Range("A5").CurrentRegion.AutoFilter Field:=13, Criteria1:="<>ZAR"
    Range("A5").CurrentRegion.AutoFilter Field:=18, Criteria1:="L"
    Columns("S").Insert
    Columns("S").ColumnWidth = 4
    Range("A5").CurrentRegion.Offset(0, Range("A5").CurrentRegion.Columns.Count).Columns(1).Interior.ColorIndex = Int((50 * Rnd) + 1)
    Range("S5").Value = "3NZ4"
    F4 = myCalc.Value
    Sheets("SrcData").AutoFilterMode = False
    Range("A5").CurrentRegion.AutoFilter Field:=8, Criteria1:="ORDINARY SHARES"
    Range("A5").CurrentRegion.AutoFilter Field:=15, Criteria1:="Exchange Trades Funds"
    Range("A5").CurrentRegion.AutoFilter Field:=13, Criteria1:="<>ZAR"
    Range("A5").CurrentRegion.AutoFilter Field:=18, Criteria1:="L"
    Columns("S").Insert
    Columns("S").ColumnWidth = 4
    Range("A5").CurrentRegion.Offset(0, Range("A5").CurrentRegion.Columns.Count).Columns(1).Interior.ColorIndex = Int((50 * Rnd) + 1)
    Range("S5").Value = "3NZ5"
    F5 = myCalc.Value

    Sheets("SrcData").AutoFilterMode = False
    Range("A1").Select
    Sheets("Report").Range("C3").Value = F1 + F2 - F3 - F4 - F5
    Application.ScreenUpdating = True
End Sub
Private Sub Filter4Z()
Dim myCalc As Range
Dim F1 As Single, F2 As Single, F3 As Single, F4 As Single, F5 As Single

    Set myCalc = Sheets("SrcData").Range("J1000000").End(xlUp)
    Application.ScreenUpdating = False
    Sheets("SrcData").AutoFilterMode = False
    Range("A5").CurrentRegion.AutoFilter Field:=8, Criteria1:="SWAPS"
    Range("A5").CurrentRegion.AutoFilter Field:=13, Criteria1:="ZAR"
    Range("A5").CurrentRegion.AutoFilter Field:=18, Criteria1:="L"
    Columns("S").Insert
    Columns("S").ColumnWidth = 4
    Range("A5").CurrentRegion.Offset(0, Range("A5").CurrentRegion.Columns.Count).Columns(1).Interior.ColorIndex = Int((50 * Rnd) + 1)
    Range("S5").Value = "4Z1"
    F1 = myCalc.Value
    Sheets("SrcData").AutoFilterMode = False
    Range("A5").CurrentRegion.AutoFilter Field:=8, Criteria1:="SWAPSFUTURES"
    Range("A5").CurrentRegion.AutoFilter Field:=13, Criteria1:="ZAR"
    Range("A5").CurrentRegion.AutoFilter Field:=18, Criteria1:="L"
    Columns("S").Insert
    Columns("S").ColumnWidth = 4
    Range("A5").CurrentRegion.Offset(0, Range("A5").CurrentRegion.Columns.Count).Columns(1).Interior.ColorIndex = Int((50 * Rnd) + 1)
    Range("S5").Value = "4Z2"
    F2 = myCalc.Value
    Sheets("SrcData").AutoFilterMode = False
    Range("A5").CurrentRegion.AutoFilter Field:=8, Criteria1:="SWAPSOPTIONS"
    Range("A5").CurrentRegion.AutoFilter Field:=13, Criteria1:="ZAR"
    Range("A5").CurrentRegion.AutoFilter Field:=18, Criteria1:="L"
    Columns("S").Insert
    Columns("S").ColumnWidth = 4
    Range("A5").CurrentRegion.Offset(0, Range("A5").CurrentRegion.Columns.Count).Columns(1).Interior.ColorIndex = Int((50 * Rnd) + 1)
    Range("S5").Value = "4Z3"
    F3 = myCalc.Value

    Sheets("SrcData").AutoFilterMode = False
    Range("A1").Select
    Sheets("Report").Range("B4").Value = F1 + F2 + F3
    Application.ScreenUpdating = True
End Sub
Private Sub Filter4NZ()
Dim myCalc As Range
Dim F1 As Single, F2 As Single, F3 As Single, F4 As Single, F5 As Single

    Set myCalc = Sheets("SrcData").Range("J1000000").End(xlUp)
    Application.ScreenUpdating = False
    Sheets("SrcData").AutoFilterMode = False
    Range("A5").CurrentRegion.AutoFilter Field:=8, Criteria1:="SWAPS"
    Range("A5").CurrentRegion.AutoFilter Field:=13, Criteria1:="<>ZAR"
    Range("A5").CurrentRegion.AutoFilter Field:=18, Criteria1:="L"
    Columns("S").Insert
    Columns("S").ColumnWidth = 4
    Range("A5").CurrentRegion.Offset(0, Range("A5").CurrentRegion.Columns.Count).Columns(1).Interior.ColorIndex = Int((50 * Rnd) + 1)
    Range("S5").Value = "4NZ1"
    F1 = myCalc.Value
    Sheets("SrcData").AutoFilterMode = False
    Range("A5").CurrentRegion.AutoFilter Field:=8, Criteria1:="SWAPSFUTURES"
    Range("A5").CurrentRegion.AutoFilter Field:=13, Criteria1:="<>ZAR"
    Range("A5").CurrentRegion.AutoFilter Field:=18, Criteria1:="L"
    Columns("S").Insert
    Columns("S").ColumnWidth = 4
    Range("A5").CurrentRegion.Offset(0, Range("A5").CurrentRegion.Columns.Count).Columns(1).Interior.ColorIndex = Int((50 * Rnd) + 1)
    Range("S5").Value = "4NZ2"
    F2 = myCalc.Value
    Sheets("SrcData").AutoFilterMode = False
    Range("A5").CurrentRegion.AutoFilter Field:=8, Criteria1:="SWAPSOPTIONS"
    Range("A5").CurrentRegion.AutoFilter Field:=13, Criteria1:="<>ZAR"
    Range("A5").CurrentRegion.AutoFilter Field:=18, Criteria1:="L"
    Columns("S").Insert
    Columns("S").ColumnWidth = 4
    Range("A5").CurrentRegion.Offset(0, Range("A5").CurrentRegion.Columns.Count).Columns(1).Interior.ColorIndex = Int((50 * Rnd) + 1)
    Range("S5").Value = "4NZ3"
    F3 = myCalc.Value

    Sheets("SrcData").AutoFilterMode = False
    Range("A1").Select
    Sheets("Report").Range("C4").Value = F1 + F2 + F3
    Application.ScreenUpdating = True
End Sub
Private Sub Filter5Z()
Dim myCalc As Range
Dim F1 As Single, F2 As Single, F3 As Single, F4 As Single, F5 As Single

    Set myCalc = Sheets("SrcData").Range("J1000000").End(xlUp)
    Application.ScreenUpdating = False
    Sheets("SrcData").AutoFilterMode = False
    Range("A5").CurrentRegion.AutoFilter Field:=8, Criteria1:="Annuities"
    Range("A5").CurrentRegion.AutoFilter Field:=13, Criteria1:="ZAR"
    Range("A5").CurrentRegion.AutoFilter Field:=18, Criteria1:="L"
    Columns("S").Insert
    Columns("S").ColumnWidth = 4
    Range("A5").CurrentRegion.Offset(0, Range("A5").CurrentRegion.Columns.Count).Columns(1).Interior.ColorIndex = Int((50 * Rnd) + 1)
    Range("S5").Value = "5Z1"
    F1 = myCalc.Value
    Sheets("SrcData").AutoFilterMode = False
    Range("A5").CurrentRegion.AutoFilter Field:=8, Criteria1:="Bonds"
    Range("A5").CurrentRegion.AutoFilter Field:=13, Criteria1:="ZAR"
    Range("A5").CurrentRegion.AutoFilter Field:=18, Criteria1:="L"
    Columns("S").Insert
    Columns("S").ColumnWidth = 4
    Range("A5").CurrentRegion.Offset(0, Range("A5").CurrentRegion.Columns.Count).Columns(1).Interior.ColorIndex = Int((50 * Rnd) + 1)
    Range("S5").Value = "5Z2"
    F2 = myCalc.Value
    Sheets("SrcData").AutoFilterMode = False
    Range("A5").CurrentRegion.AutoFilter Field:=8, Criteria1:="Cash"
    Range("A5").CurrentRegion.AutoFilter Field:=15, Criteria1:="Interest Bearing 0 - 3 years"
    Range("A5").CurrentRegion.AutoFilter Field:=13, Criteria1:="ZAR"
    Range("A5").CurrentRegion.AutoFilter Field:=18, Criteria1:="L"
    Columns("S").Insert
    Columns("S").ColumnWidth = 4
    Range("A5").CurrentRegion.Offset(0, Range("A5").CurrentRegion.Columns.Count).Columns(1).Interior.ColorIndex = Int((50 * Rnd) + 1)
    Range("S5").Value = "5Z3"
    F3 = myCalc.Value
    Sheets("SrcData").AutoFilterMode = False
    Range("A5").CurrentRegion.AutoFilter Field:=8, Criteria1:="Debentures"
    Range("A5").CurrentRegion.AutoFilter Field:=13, Criteria1:="ZAR"
    Range("A5").CurrentRegion.AutoFilter Field:=18, Criteria1:="L"
    Columns("S").Insert
    Columns("S").ColumnWidth = 4
    Range("A5").CurrentRegion.Offset(0, Range("A5").CurrentRegion.Columns.Count).Columns(1).Interior.ColorIndex = Int((50 * Rnd) + 1)
    Range("S5").Value = "5Z4"
    F4 = myCalc.Value
    Sheets("SrcData").AutoFilterMode = False
    Range("A5").CurrentRegion.AutoFilter Field:=8, Criteria1:="Other Loans"
    Range("A5").CurrentRegion.AutoFilter Field:=13, Criteria1:="ZAR"
    Range("A5").CurrentRegion.AutoFilter Field:=18, Criteria1:="L"
    Columns("S").Insert
    Columns("S").ColumnWidth = 4
    Range("A5").CurrentRegion.Offset(0, Range("A5").CurrentRegion.Columns.Count).Columns(1).Interior.ColorIndex = Int((50 * Rnd) + 1)
    Range("S5").Value = "5Z5"
    F5 = myCalc.Value

    Sheets("SrcData").AutoFilterMode = False
    Range("A1").Select
    Sheets("Report").Range("B5").Value = F1 + F2 + F3 + F4 + F5
    Application.ScreenUpdating = True
End Sub
Private Sub Filter5NZ()
Dim myCalc As Range
Dim F1 As Single, F2 As Single, F3 As Single, F4 As Single, F5 As Single

    Set myCalc = Sheets("SrcData").Range("J1000000").End(xlUp)
    Application.ScreenUpdating = False
    Sheets("SrcData").AutoFilterMode = False
    Range("A5").CurrentRegion.AutoFilter Field:=8, Criteria1:="Annuities"
    Range("A5").CurrentRegion.AutoFilter Field:=13, Criteria1:="<>ZAR"
    Range("A5").CurrentRegion.AutoFilter Field:=18, Criteria1:="L"
    Columns("S").Insert
    Columns("S").ColumnWidth = 4
    Range("A5").CurrentRegion.Offset(0, Range("A5").CurrentRegion.Columns.Count).Columns(1).Interior.ColorIndex = Int((50 * Rnd) + 1)
    Range("S5").Value = "5NZ1"
    F1 = myCalc.Value
    Sheets("SrcData").AutoFilterMode = False
    Range("A5").CurrentRegion.AutoFilter Field:=8, Criteria1:="Bonds"
    Range("A5").CurrentRegion.AutoFilter Field:=13, Criteria1:="<>ZAR"
    Range("A5").CurrentRegion.AutoFilter Field:=18, Criteria1:="L"
    Columns("S").Insert
    Columns("S").ColumnWidth = 4
    Range("A5").CurrentRegion.Offset(0, Range("A5").CurrentRegion.Columns.Count).Columns(1).Interior.ColorIndex = Int((50 * Rnd) + 1)
    Range("S5").Value = "5NZ2"
    F2 = myCalc.Value
    Sheets("SrcData").AutoFilterMode = False
    Range("A5").CurrentRegion.AutoFilter Field:=8, Criteria1:="Cash"
    Range("A5").CurrentRegion.AutoFilter Field:=15, Criteria1:="Interest Bearing 0 - 3 years"
    Range("A5").CurrentRegion.AutoFilter Field:=13, Criteria1:="<>ZAR"
    Range("A5").CurrentRegion.AutoFilter Field:=18, Criteria1:="L"
    Columns("S").Insert
    Columns("S").ColumnWidth = 4
    Range("A5").CurrentRegion.Offset(0, Range("A5").CurrentRegion.Columns.Count).Columns(1).Interior.ColorIndex = Int((50 * Rnd) + 1)
    Range("S5").Value = "5NZ3"
    F3 = myCalc.Value
    Sheets("SrcData").AutoFilterMode = False
    Range("A5").CurrentRegion.AutoFilter Field:=8, Criteria1:="Debentures"
    Range("A5").CurrentRegion.AutoFilter Field:=13, Criteria1:="<>ZAR"
    Range("A5").CurrentRegion.AutoFilter Field:=18, Criteria1:="L"
    Columns("S").Insert
    Columns("S").ColumnWidth = 4
    Range("A5").CurrentRegion.Offset(0, Range("A5").CurrentRegion.Columns.Count).Columns(1).Interior.ColorIndex = Int((50 * Rnd) + 1)
    Range("S5").Value = "5NZ4"
    F4 = myCalc.Value
    Sheets("SrcData").AutoFilterMode = False
    Range("A5").CurrentRegion.AutoFilter Field:=8, Criteria1:="Other Loans"
    Range("A5").CurrentRegion.AutoFilter Field:=13, Criteria1:="<>ZAR"
    Range("A5").CurrentRegion.AutoFilter Field:=18, Criteria1:="L"
    Columns("S").Insert
    Columns("S").ColumnWidth = 4
    Range("A5").CurrentRegion.Offset(0, Range("A5").CurrentRegion.Columns.Count).Columns(1).Interior.ColorIndex = Int((50 * Rnd) + 1)
    Range("S5").Value = "5NZ5"
    F5 = myCalc.Value

    Sheets("SrcData").AutoFilterMode = False
    Range("A1").Select
    Sheets("Report").Range("C5").Value = F1 + F2 + F3 + F4 + F5
    Application.ScreenUpdating = True
End Sub
Regards,
Rudi

If your absence does not affect them, your presence didn't matter.
Top

Return to “VB/VBA/.Net/Office Scripting”