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...

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

    and get a "Compile Error : Constant expression required"

    Any help really appreciated,


  • 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.

    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:

    '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.


  • 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)


Participate now!

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