Global Variable Scope/Value Assignment

[fuzzyaces]

All,

I am trying to write a simple piece of code and across multiple procedures, I'm trying to find the bottom row of a range (e.g.

Cells(65536, 1).End(xlUp).Row

I'm not having any troubles with this. My question is that to simplify the code, and to speed up changes, is there a way I can make a global variable (e.g. mlBottomRow) at the initial declaration AND set the value? For example:

Option Explicit
Dim mlBottomRow as Long
mlBottomRow = 65536

Sub Procedure1()
    Code...
End sub

Sub Procedure2()
    Code...
End sub

I know in variable scope, I can dimension the variable and type. I'm just unclear on actually setting the value outside a procedure.

Thanks for your help!

-Fuzzy

 

[Norie]

Fuzzy

Do you mean you want to declare the no of rows in a worksheet, eg 65536, to use when you want to find the last row of data?

Why not just use Rows.Count?

That will return the no of rows in a worksheet for whichever version of Excel the code is in.

 

[fuzzyaces]

All,

Nevermind. I searched and found that others had previously answered this question. It appears a minor modification is needed:

Public Const mlBottomRow as Long = 65536

 

[fuzzyaces]

Fuzzy

Do you mean you want to declare the no of rows in a worksheet, eg 65536, to use when you want to find the last row of data?

Why not just use Rows.Count?

That will return the no of rows in a worksheet for whichever version of Excel the code is in.

Norie,

That's a much smarter route than I've been considering with the rows example, but there were a few other values I wanted to constrain. In any event, thank you for your help. It's much appreciated.

-Fuzzy

 

[Norie]

Fuzzy

What other values do you want to 'constrain'?

 

[fuzzyaces]

Fuzzy

What other values do you want to 'constrain'?

Some simple color scheme RGB values (e.g. mlR, mlG, mlB) so that any formatting changes are quicker.

 

[Norie]

I know they're kind of restrictive but why not just use the builtin vbRed, vbBlue etc and the ColorIndex palette?

Or are you going a bit beyond that?

 

[fuzzyaces]

I know they're kind of restrictive but why not just use the builtin vbRed, vbBlue etc and the ColorIndex palette?

Or are you going a bit beyond that?

Sigh. I would love that, but unfortunately, I have a specific RGB color scheme to follow.