Use Cell Value As Constant (Const) In Macro Code

  • Hi all, I'm trying to set named range as a constant in VBA, to allow me to recall the same range as an output target in different subs, without having to 'set' it in each module.


    I have tried to declare it at the top of a module like this...


    Code
    Public Const tpnb_range = Worksheets("SQL").Range("tpnbs")


    and get a "Compile Error : Constant expression required"


    Any help really appreciated,


    Thanks,
    Joe.

  • Re: Set Named Range As Constant


    I don't think that you can define a constant that way. The user changing the name would change the constant...which makes it a poor constant.
    You could use a UDF instead of a constant.

    Code
    Function tpnb_range () as Range
        Set tpnb_range = Worksheets(" SQL").Range("tpnbs") 
    End Function
  • Re: Set Named Range As Constant


    Hi Joe


    I'm afraid Constants can only hold simple data types eg like a string or a numeric value. Perhaps you could just use a Public variable instead eg declare:


    Code
    'declarations section of standard module:
    Public tpnb_range As Range
    
    
    
    
    'then in ThisWorkbook module:
    Private Sub Workbook_Open()
    Set tpnb_range =  Worksheets(" SQL").Range("tpnbs") 
    End Sub


    You can then refer to Worksheets(" SQL").Range("tpnbs") by using tpnb_range.


    Richard

  • Re: Set Named Range As Constant


    Thanks guys.


    mikerickson - I've not used UDF's (hope thats the right term!) before, but I shall definately read up on them, as they are more than likely a powerful tool to master.


    RichardSchollar - Thanks for the helpful info about Constants - sometimes hard to find simple explanations of things. You have also helped answer another question I had in the back of my mind, regarding the best way to declare a Public variable ( the answer being within the Workbook_Open module).


    Thanks again for the excellent speedy responses.[hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]For the benefit of others in same position, I've found this to be an excellent place to start out with UDF's (User Defined Functions)


    http://www.ozgrid.com/VBA/Functions.htm


    Thanks,
    Joe.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!