I understand how to declare a Public Constant and set its value eg . . .

Public Const <name> as String = <value>



However I want to define the value as a concatenated string eg . . .

Public Const <name> as String = "C:\Users" & Environ$("username") & "\OneDrive"

. . . but this fails with "Compile error".

I can get round this by just declaring the constant's name, then setting its value in the Auto-Open macro.

I was wondering if there is a better or neater way to do this ?

A constant cannot be the result of a function since that result could change. Similarly you can't declare a constant and then set its value in another routine.

You can either use a variable - which I assume is what you meant with your second option - or use a function that returns the value you want. You can then simply use that function name as you would the constant.