+ Reply to Thread
Results 1 to 3 of 3

Trouble declaring a Public Constant that references a cell's value

  1. #1
    Forum Contributor
    Join Date
    04-11-2012
    Location
    Chicago, IL
    MS-Off Ver
    Excel 2010
    Posts
    220

    Trouble declaring a Public Constant that references a cell's value

    Hello -

    I have a workbook containing many modules that rely on a public constant. Currently, my public constant looks like this:

    Public sEventName As String
    Public Const VARDEST = "C:\Users\Tom\Desktop\Event History\"



    What I need to do is this: instead of having VARDEST = an actual path, I want to assign the value of a worksheet cell to my Constant VARDEST. See my declarations below for a better understanding.

    Please Login or Register  to view this content.

  2. #2
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham
    MS-Off Ver
    365
    Posts
    8,483

    Re: Trouble declaring a Public Constant that references a cell's value

    A constant has to be just that Constant. Therefore you cannot assign it a value based on a cell.

  3. #3
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    16,444

    Re: Trouble declaring a Public Constant that references a cell's value

    A Const must have a value that is determined at compile time. You want to use an expression that must be evaluated at runtime, so you cannot use a Const. The only way to do what you want is this:

    Please Login or Register  to view this content.
    If you want VARDEST to be Public, it must be declared outside of a Sub. The two assignment statements here must be inside a Sub.

    Note that the value of VARDEST will be calculated only at the point of this assignment statement. If the value of the cell changes later, the value of VARDEST will not.

    Another way to do this is
    Please Login or Register  to view this content.
    Now VARDEST will always refer to that cell, and if the cell value changes, VARDEST will change.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Public Constant vs. Public Dim Long
    By brucemc777 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-19-2020, 03:55 PM
  2. Declaring Variables with Public
    By nordicdust in forum Excel Programming / VBA / Macros
    Replies: 24
    Last Post: 04-12-2018, 07:14 AM
  3. [SOLVED] Declaring Dim, Public
    By jomili in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-01-2016, 11:14 AM
  4. [SOLVED] declaring public array?
    By desertsp in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-01-2013, 06:19 PM
  5. Using a cell value as a public constant
    By larawill in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-30-2009, 05:59 AM
  6. declaring public variable value
    By Damon in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-24-2005, 10:05 AM
  7. declaring public variables
    By Loane Sharp in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-18-2005, 02:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1