Change value of a VBA constant

Question

I have a function in which I set a VBA constant as follows:

Const KEY_TEXT As String = "mycystomtext"

Is there a way to change the VBA constant to a different text programmatically?

I have tried to define:

Const KEY_TEXT As String = Range("A1").value

but this leads to an error.

Constant values are assigned at *compile time*, but (eg) a cell's value cannot be known until *run time*, so it can't be used as the source for a constant. Only other [already-defined] Constants, or "constant expressions" (eg `2 * SOME_OTHER_CONSTANT`, `2^3`, etc) can be used. — Tim Williams, Aug 04 '14 at 20:31

score 5 · Answer 1 · answered Aug 04 '14 at 20:15

5

constant, by definition, cannot be changed after initialization. it has to be initialized when defining the variable.

answered Aug 04 '14 at 20:15

3

There's not a lot to add to this... except "if you think you are gong to want to change it, don't define it as a `Const` - just define it as a global variable." – Floris Aug 04 '14 at 20:18
1

I have to define it as a constant for my purposes. A clever answer would be to use VBA code to change this VBA line using the replace function in VBA. See http://www.cpearson.com/excel/vbe.aspx – JoaMika Aug 04 '14 at 20:28
4

@JoannaMikalai - can you explain _why_ it needs to be defined as a constant? I cannot think of a situation in VBA where this is so. Maybe if we understood, we could offer an alternative. As for the downvote - it wasn't me. – Floris Aug 04 '14 at 21:00

score 4 · Accepted Answer · edited Aug 27 '19 at 12:25

I am answering my own question which was specifically:

Is there a way to change the VBA constant to a different text programmatically?

Sub replace_run()
    Dim s As String
    Dim i As Long
    Dim VBProj As VBIDE.VBProject
    Dim VBComp As VBIDE.VBComponent
    Dim CodeMod As VBIDE.CodeModule

    With Application
        .EnableEvents = False
        .ScreenUpdating = False
        .DisplayAlerts = False
    End With

    s = ThisWorkbook.Path
    wcodestring = s & "\myfile.xlsm"

    Workbooks.Open FileName:=wcodestring
    Set wb_code = Application.Workbooks("myfile.xlsm")
    Set VBProj_xl = Application.Workbooks("myfile.xlsm").VBProject

    Set VBProj = wb_code.VBProject
    Set VBComp = VBProj.VBComponents("Module1")
    Set CodeMod = VBComp.CodeModule


    With CodeMod
        For i = 1 To .CountOfLines
            If .Lines(i, 1) = "Const KEY_TEXT As String = " & Chr(34) & "mycystomtext" & Chr(34) Then
                .ReplaceLine i, "Const KEY_TEXT As String = " & Chr(34) & "mycystomreplace" & Chr(34)
            End If
        Next i
    End With

    wb_code.Close SaveChanges:=True

    Application.ScreenUpdating = True

End Sub

and thanks for the -2 ... it's easy to press a button down. I spent more time posting my answer than getting help here.. — JoaMika, Aug 04 '14 at 22:02
If you want to reference your constant to a range, I've posted a workaround for that in here. But if it is not referenced in a range, then what you posted is what'll suit your purpose. — L42, Aug 04 '14 at 22:44
thanks your workaround is neat ! I will be using both methods in the future — JoaMika, Aug 04 '14 at 22:50

Change value of a VBA constant

2 Answers2