How do I assign a global constant variable to the current year date?

Or explained another way, a constant is something that doesnt change. A variable (as per its name) is something that does change.

So if you try and assign a constant a value that could change automatically (like a function such as date or now) it fails because it knows that its not a static value.

You may be able to do something like this instead (declaring and assigning value to a variable at the same time):

Please Login or Register  to view this content.

However, what was your reasoning behind wanting to do this (I am not judging, I have tried this very thing before)?

I know for myself, I tend to put constants towards the top of my code as a way to reuse values multiple times in my code in such a way if I need to change it I can do so in 1 place (the constant) instead of each place in my code I use it. I also tend to use constants for values I will use more than once and despite not needing to ever change them I know if will speed up code (ex: 1, 2, 0, "A1", etc). The last reason I use constants, when writing code for others, is to give them an easy way to change a value used in the code.

Its that last one that led me to what you have tried. I often find in this case its better to provide a way for the user to input the desired parameter and store it in a variable for use in the code. It can be something as simple as a cell on a sheet they input into or a message/input box. It can be more advanced like a custom form to gather information and validate the entry before running.

So long story short, you can do it as a variable instead or you may consider gathering that piece of input some other way.

So based on one of your responses it seems like you may not be aware of the difference between a variable and constant and how they are declared and assigned values. Depending on how serious you are about learning VBA you may consider reading up on this fundamental concept. You may also want to look into scope as well.

The short of it is as follows (my apologies if I have underestimated your understanding of VBA):

A variable is a name we give to a value that can be anything, it can even change in the course of running our code. A variable will be of a certain data type which dictates the type of data (or object) it can store and how much memory it uses to store said data.

The typical method of doing this is:

Please Login or Register  to view this content.

In which "variablename" is whatever you want to call your variable and "type" is the data type, like "String" or "Integer" (without quotes).

A constant is like a variable, in that it is a name we give to a value to use later in our code by its name. The difference is a constant is assigned a static value at the time of declaration and it remains the same value. It also is declared with a data type.

Please Login or Register  to view this content.

In which "constantname" is whatever you want to name the constant and "type" is the data type, like "String" or "Integer" (without quotes).

When dealing with scope, the syntax to declare changes slightly but all the rules still apply.

Variable (Public scope)

Please Login or Register  to view this content.

Constant (Public scope)

Please Login or Register  to view this content.

You should only declare variables beyond the default scope when absolutely needed.

Its possible you may not be able to assign the value during declaration with public scope variables.

As I mentioned there are better alternatives to this. The easiest would be a cell on the sheet with data validation. Make the validation a whole number from whatever start year you like to whatever future year you like, ex: 2015 to 2050. In this fashion you can prevent other entries, provide a dialog warning should they try an improper entry and ensure the data type is a 4 digit numeric value. Then in your code use a range variable to store the cell itself, and you can declare another variable to store its value (to later concatenate to your file name). You may have to play with the data type of the second variable (either converting it to string during to storing it or converting it when concatenating to the name). This will make it very easy for them to change, very easy for you to code, and very "strict" in terms of what they can enter which is good (as a programmer you cannot always assume the user will enter information properly).

As a twist on the above, you could put in a cell the following formula if you want it to be current year (like your OP code), then just skip the validation and reference the cell...maybe consider locking the cell to prevent someone replacing the formula with manually entered values.:

Formula:

Please Login or Register  to view this content.

Now that I think about it, you could also just do:

Please Login or Register  to view this content.

You can simply put a comment above it to explain what they need to change (ie: 2018 to any valid 4 digit year)