Posts by crazyjoe_uk

    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)


    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,