Objectvariable In Constants Cause Error

  • Hello!

    Tried a couple of diffrent title that I think would be better suited, and wasnt allowed any of them. I hope this title is good enough

    My problem:
    I have written a macro that will open and close a couple of diffrent excel-documents for information-collection. To make it easier and more flexible I wanted to set up a constant with the name of the main document (where the macro is). so, assuming my document is called Document1.xls, instead of using the code:


    I wanted to create a constant with the current name of the workbook:

    Public Const ActWB = ActiveWorkbook.Name

    So I could use that constant everywhere instead of directly naming the document. That would mean that the document would be able to change name and I wouldnt have to go in and change the code for diffrent copies of the same document.
    The problem is that when I try to run it I get the errormessage:
    "Compile Error.
    Constant expression required"
    and the .Name is hightlighted.

    Anyone know why this is, and if possible, how to avoid or work around it?

  • Re: Objectvariable In Constants Cause Error

    The help says


    You can't use variables, user-defined functions, or intrinsic Visual Basic functions (such as Chr) in expressions assigned to constants.

    You will either have to just use a public variable and assign the value at the start of the macro. Or if the property really must be read only create a class.


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!