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
Get macro name in variable
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Get macro name in variable
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- Administrator
- Posts: 78530
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Get macro name in variable
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
Hans
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Get macro name in variable
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
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.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- Administrator
- Posts: 78530
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Get macro name in variable
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
Hans
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Get macro name in variable
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
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.
Rudi
If your absence does not affect them, your presence didn't matter.