Naming a set of instructions to be called upon in other code.

[MeisterConrad]

I have a kind of Frankenstein monster of a workbook that I've been evolving over the years. My latest idea for improvement involves making little packages of code than can be called upon within other code; so the little packages are "multi-purpose".
So, the workbook keeps track of 20 monetary accounts, with each account having its own LedgerTable on its own Worksheet.
The Account numbers are 1 to 20. The worksheets being used for the accounts are Sheets 9 through 28, but the sheets have all been rename as "Account"#"Sheet"; that is, "Account1Sheet","Account2Sheet",...Account20Sheet". The Tables being used as Ledgers ("LedgerTable"#) are Tables 4 through 23, and each of these Tables is a named range ("LedgerTable1","LedgerTable2",..."LedgerTable20"). And these numbers ("Account"#"Sheet", and "LedgerTable"#) match; LeddgerTable17 is on the worksheet Account17Sheet.
With all that, what I'm trying to make is code for a set of instructions that I can group together and call it "IDLT". Then later, in other pieces of code, I could just have something like "IDLT.Run" to perform this set of instructions.
Here's what I want those instructions to do (Identify the LedgerTable on the Active Sheet):
First, look at the name of the Active Sheet (which we assign to "ACCSht" As String), which consists of a number (that we assign to "ACC" As Integer) inbetween the words"Account" and "Sheet";
Second, EXTRACT that number ("ACC" = #);
Third, paste that number at the end of some text that says "LedgerTable" ("LedgerTable"&ACC and assign that to "ACCTbl" As ListObject)
Fourth, use that combined text ("LedgerTable"&ACC; ex, "LedgerTable2") to identify the ListObject that is LedgerTable2, which in this example is Table5.
After these instructions happen, the routine is over. So, my next line of code could be
ACCTble.Select so that it is selected rather than the sheet being activated.


So, if I understand correctly, then I should insert a Module named "Sub IDLT", and this module should be available throughout the entire workbook because this code will be run on diffferent pages, UserForms, etc. Does that sound right so far?

Then the code would be something like:

Sub IDLT()
Dim ACC As Integer
DimACCSht As String
Dim ACCTbl As ListObject

ACCSht = ActiveSheet.Name
' how do I extract the number (ACC) from this Sheet's name?
ACCTble = "LedgerTable"&ACC

msgbox ACCTbl

End Sub

Surely, it can't be that simple. Could it? I gotta be missing something, right?


Also wondering about setting up a collection of data in a standardized order. Is this what is meant as a matrix? For example, suppose I want to line up empty spots for "ACC", "ACCSht","ACCTbl","FIRSTRW", and "LASTRW". So, that's 5 pieces of data in the same order everytime.
With this set up, I could have some kind of code that uses any one of those datums to calculate the other four. With this, then
When ACC = #, ACCSht = "Account"&#&"Sheet", ACCTble = "LedgerTable"&#, FIRSTRW = the cell address of the Table's first column, first row, and LASTRW = the cell address of the Table's first column, last row. Also
When ACCSht="Account"&#&"Sheet", then ACC=#, ACCTble="LedgerTable"&#,...
And so on so that any one of the datums sets all the others.
But that's all for later on,...I digress.

I've ended the sample code with a msgbox for testing, but I really don't know if the code that I write means what I want it to mean.
Can somebody tell me if I think I know what I'm talking about, because I get confused enough with the details, and then syntax gets involved and I lose sight of the details and purpose. I'm just trying to make these instructions so I don't have to repeat the code in multiple places throughout the workbook. And I'm not really sure how to call up those instructions; what the command should be.

Am I getting too involved? Is my thinking sound? - Or am I way off base?

 

[HaHoBe]

Hi MeisterConrad,

first regarding getting the number for any sheet and listobject:

Sub IDLT()
' https://www.mrexcel.com/board/threads/naming-a-set-of-instructions-to-be-called-upon-in-other-code.1225890/
Dim ACC As Integer
Dim ACCSht As String
Dim ACCTbl As ListObject

ACCSht = ActiveSheet.Name
If LCase(Left(ACCSht, 7)) = "account" Then
  ACCSht = Replace(ACCSht, "Sheet", "")
  ACC = Replace(ACCSht, "Account", "")
  Set ACCTbl = ActiveSheet.ListObjects("LedgerTable" & ACC)
  MsgBox ACCTbl.Name
End If
End Sub

Regarding the order for certain items: either use Enumeration, an Array or a Collection for that. Sample for Enumeration may look like this

Private Enum Steuersatz
  maxi = 19
  midi = 7
  mini = 0
End Enum

Private Enum Vorwahl
  Belgien = 32
  Deutschland = 49
  Italien = 39
End Enum

Public Sub TesteEnumerationen()
  
  MsgBox Steuersatz.maxi, , "Steuersatz Max"
  MsgBox "0" & Vorwahl.Belgien, , "Belgien"

End Sub

Ciao,
Holger