Public Variable Not Getting Passed

[Matt_O]

Hi Excel Gurus,

I can't figure out why a public variable will not get passed to another module in the same workbook. I've searched through lots of threads here and across the web and can't figure out if I'm doing something wrong. It's such a basic thing that I've used multiple times and now I can't get it to work. Is it me or Excel that has a bug?

Once the call to the next module's sub is made the Locals window shows no variables. I wish I wasn't stuck on such an apparently simple issue.

First Module:

Option Explicit
Public x As Long 'count columns
Public y As Long ' count rows


Public Sub getX()


    
    'Gets column count
    Dim x As Long
    x = cn_Sheet1.Cells(1, Columns.Count).End(xlToLeft).Column
    
    'Gets row count
    Dim y As Long
    y = cn_Sheet1.Cells(Rows.Count, 1).End(xlUp).Row
    
    
 'at this point both x and y variables contain the accurate counts


    Call test




    
End Sub

The second module with sub 'test'

Option Explicit


Public Sub test()


    Debug.Print "Row count is " & y

' results in zero as no variable was passed to this module.


End Sub

Thanks,
Matt

 

[Norie]

Matt

Why have you declared x and y twice, once as public and again within the getX sub?

 

[Matt_O]

Hi Norie,

Good question with a no good answer. Pure oversight.

I never even noticed the 2 Dims once I made them public. Just constantly glossed over that small piece of code!

Thanks for super quick reply and fix. Now we know which entity is buggy.

Matt