Posts by crazyjoe_uk

  • Use Cell Value As Constant (Const) In Macro Code

    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.

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