Accessing Sub Variable in Another Sub

[animas]

I was creating the following procedures. I want to give it class like structure.

Sub SelectionRegionAddress(ByVal SelectionName As Range)
    Dim Region_Address As String
    
    Dim TopLeft_Address As String
    Dim TopLeft_RowNum As Long
    Dim TopLeft_ColumnNum As Long
    'Dim TopLeft_RowLetter As String
    Dim TopLeft_ColumnLetter As String
    
    Dim TopRight_Address As String
    Dim TopRight_RowNum As Long
    Dim TopRight_ColumnNum As Long
    'Dim TopRight_RowLetter As String
    Dim TopRight_ColumnLetter As String
    
    Dim BottomLeft_Address As String
    Dim BottomLeft_RowNum As Long
    Dim BottomLeft_ColumnNum As Long
    'Dim BottomLeft_RowLetter As String
    Dim BottomLeft_ColumnLetter As String
    
    Dim BottomRight_Address As String
    Dim BottomRight_RowNum As Long
    Dim BottomRight_ColumnNum As Long
    'Dim BottomRight_RowLetter As String
    Dim BottomRight_ColumnLetter As String
    
    Dim Region_Height As Long
    Dim Region_Width As Long
 
    Application.ScreenUpdating = False
    
    Range("SelectionName").CurrentRegion.Select
    
    Region_Address = Selection.Address
    TopLeft_Address = Split(Region_Address, ":")(0)  '//returns 1st part
    'Left(Region_Address, InStr(Region_Address, ":") - 1)
    TopLeft_ColumnLetter = Split(TopLeft_Address, "$")(1)
    TopLeft_RowNum = Split(TopLeft_Address, "$")(2) 'Range(TopLeft_Address).Row
    TopLeft_ColumnNum = Range(TopLeft_Address).Column
    
    BottomRight_Address = Split(Region_Address, ":")(1) 'returns 2nd part
    'Right(Region_Address, Len(Region_Address)-InStr(Region_Address, ":"))
    BottomRight_ColumnLetter = Split(BottomRight_Address, "$")(1)
    BottomRight_RowNum = Split(BottomRight_Address, "$")(2)
    BottomRight_ColumnNum = Range(BottomRight_Address).Column
    
    TopRight_Address = BottomRight_ColumnLetter & TopLeft_RowNum
    TopRight_ColumnLetter = BottomRight_ColumnLetter
    TopRight_RowNum = TopLeft_RowNum
    TopRight_ColumnNum = Range(TopRight_Address).Column
    
    BottomLeft_Address = TopLeft_ColumnLetter & BottomRight_RowNum
    BottomLeft_ColumnLetter = TopLeft_ColumnLetter
    BottomLeft_RowNum = BottomRight_RowNum
    BottomLeft_ColumnNum = Range(BottomLeft_Address).Column
    
    Region_Height = BottomLeft_RowNum - TopLeft_RowNum + 1 'including header row
    Region_Width = TopRight_ColumnNum - TopLeft_ColumnNum + 1
    
     Application.ScreenUpdating = True
End Sub

I will provide name of a range or table as argument. Then I need to use the procedure in other vba like below:

SelectionRegionAddress (MyRange).BottomLeft_RowNum

This should return the expected address for MyRange.

How should I do this?

 

[Zack Barresse]

Hi there,

With so many desired properties, you could think about making your own Type (variable) and just setting properties to it, which you can then access when/how you want. Here is an example...


<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br><br><SPAN style="color:#00007F">Type</SPAN> RegionAddress<br>    Region_Address <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    TopLeft_Address <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    TopLeft_RowNum <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    TopLeft_ColumnNum <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    TopLeft_ColumnLetter <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    TopRight_Address <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    TopRight_RowNum <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    TopRight_ColumnNum <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    TopRight_ColumnLetter <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    BottomLeft_Address <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    BottomLeft_RowNum <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    BottomLeft_ColumnNum <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    BottomLeft_ColumnLetter <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    BottomRight_Address <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    BottomRight_RowNum <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    BottomRight_ColumnNum <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    BottomRight_ColumnLetter <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    Region_Height <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    Region_Width <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Type</SPAN><br><br><SPAN style="color:#00007F">Public</SPAN> rngAddys <SPAN style="color:#00007F">As</SPAN> RegionAddress<br><br><SPAN style="color:#00007F">Sub</SPAN> TestRAstuff()<br>    <SPAN style="color:#00007F">Call</SPAN> SelectionRegionAddress(Selection)<br>    Stop<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br><SPAN style="color:#00007F">Function</SPAN> SelectionRegionAddress(<SPAN style="color:#00007F">ByRef</SPAN> SelectionName <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#00007F">Dim</SPAN> wkb <SPAN style="color:#00007F">As</SPAN> Workbook, wks <SPAN style="color:#00007F">As</SPAN> Worksheet<br>    <SPAN style="color:#00007F">Set</SPAN> wks = Selection.Parent<br>    <SPAN style="color:#00007F">Set</SPAN> wkb = wks.Parent<br>    rngAddys.Region_Address = SelectionName.CurrentRegion.Address<br>    rngAddys.TopLeft_Address = Split(rngAddys.Region_Address, ":")(0)<br>    rngAddys.TopLeft_ColumnLetter = Split(rngAddys.TopLeft_Address, "$")(1)<br>    rngAddys.TopLeft_RowNum = Split(rngAddys.TopLeft_Address, "$")(2)<br>    rngAddys.TopLeft_ColumnNum = wks.Range(rngAddys.TopLeft_Address).Column<br>    rngAddys.BottomRight_Address = Split(rngAddys.Region_Address, ":")(1)<br>    rngAddys.BottomRight_ColumnLetter = Split(rngAddys.BottomRight_Address, "$")(1)<br>    rngAddys.BottomRight_RowNum = Split(rngAddys.BottomRight_Address, "$")(2)<br>    rngAddys.BottomRight_ColumnNum = wks.Range(rngAddys.BottomRight_Address).Column<br>    rngAddys.TopRight_Address = rngAddys.BottomRight_ColumnLetter & rngAddys.TopLeft_RowNum<br>    rngAddys.TopRight_ColumnLetter = rngAddys.BottomRight_ColumnLetter<br>    rngAddys.TopRight_RowNum = rngAddys.TopLeft_RowNum<br>    rngAddys.TopRight_ColumnNum = wks.Range(rngAddys.TopRight_Address).Column<br>    rngAddys.BottomLeft_Address = rngAddys.TopLeft_ColumnLetter & rngAddys.BottomRight_RowNum<br>    rngAddys.BottomLeft_ColumnLetter = rngAddys.TopLeft_ColumnLetter<br>    rngAddys.BottomLeft_RowNum = rngAddys.BottomRight_RowNum<br>    rngAddys.BottomLeft_ColumnNum = wks.Range(rngAddys.BottomLeft_Address).Column<br>    rngAddys.Region_Height = rngAddys.BottomLeft_RowNum - rngAddys.TopLeft_RowNum + 1<br>    rngAddys.Region_Width = rngAddys.TopRight_ColumnNum - rngAddys.TopLeft_ColumnNum + 1<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN></FONT>


HTH

 

[animas]

Thank you.