Programatically change Module Const

[Thebatfink]

Hi,

I'm updating a series of workbooks VBA projects and whilst I have no trouble removing a procedure and injecting a new one into an existing module, I can't seem to figure out how to remove and inject a new const into the top of the module (its outside of any procedures).

Any idea how I would do this if its possible?

Thanks!
Batfink

 

[RoryA]

Seems to me like you need a variable, not a constant.

You would have to read the declaration lines of the relevant code module, looking for your constant name. Then delete that line and replace it with the one you want (using DeleteLines and InsertLines)

 

[Thebatfink]

Hi Rorya,

Thanks for the reply. The code I used was what I found and it was a const so I left it that way! Not sure if it should or it shouldn't be as I'm not very VB experienced.. you know an infinate amount more than me!

I used this code from cpearsons website (where I learnt to do all this to begin with). Based on your reply I assume I will be needing the codeline and from that I can then delete the line and then insert the line, so I'm doing the following (just experimenting to try and get the codeline returned for now!!)

Dim VBProj As VBIDE.VBProject, VBComp As VBIDE.VBComponent, _
CodeMod As VBIDE.CodeModule

        Dim findwhat As String
        Dim SL As Long ' start line
        Dim EL As Long ' end line
        Dim SC As Long ' start column
        Dim EC As Long ' end column
        Dim Found As Boolean
        
        Set VBProj = ActiveWorkbook.VBProject
        Set VBComp = VBProj.VBComponents("Module1")
        Set CodeMod = VBComp.CodeModule
        
        findwhat = "Const DatabasePath As String = ""\\my\path\to Database\database .mdb"""
        
        With CodeMod
            SL = 1
            EL = .CountOfLines
            SC = 1
            EC = 255
            Found = .Find(target:=findwhat, StartLine:=SL, StartColumn:=SC, _
                EndLine:=EL, EndColumn:=EC, _
                wholeword:=True, MatchCase:=False, patternsearch:=False)
            Do Until Found = False
                Debug.Print "Found at: Line: " & CStr(SL) & " Column: " & CStr(SC)
                EL = .CountOfLines
                SC = EC + 1
                EC = 255
                Found = .Find(target:=findwhat, StartLine:=SL, StartColumn:=SC, _
                    EndLine:=EL, EndColumn:=EC, _
                    wholeword:=True, MatchCase:=False, patternsearch:=False)
            Loop
            MsgBox Found
        End With

Found is coming back False, but the string is 100% definately there and I have copy pasted it into this sub and just added the extra quotes so I know I'm not mistyping it? Any idea why this is? or if your feeling generous, a nudge in the right direction if I'm going around the houses to accomplish what I want!!

Thanks!
Batfink

 

[RoryA]

You appear to have a space in between 'database' and '.mdb' which may well be your issue.

 

[Thebatfink]

The space does actually exist in the filename..

 

[Thebatfink]

Oops, I put my msgbox in the wrong place. SL is returning the codeline

Now to figure out the deleting and inserting. Sorry about that!

 

[Thebatfink]

For anyone else reading .. my solution

Dim findwhat As String
Dim SL As Long ' start line
Dim EL As Long ' end line
Dim SC As Long ' start column
Dim EC As Long ' end column
Dim Found As Boolean
        
Set VBProj = ActiveWorkbook.VBProject
Set VBComp = VBProj.VBComponents("Module1")
Set CodeMod = VBComp.CodeModule
        
findwhat = "Const DatabasePath As String = ""\\path\to\database\database.mdb"""
        
With CodeMod
    SL = 1
    EL = .CountOfLines
    SC = 1
    EC = 255
        Found = .Find(target:=findwhat, StartLine:=SL, StartColumn:=SC, _
            EndLine:=EL, EndColumn:=EC, _
            wholeword:=True, MatchCase:=False, patternsearch:=False)
            Do Until Found = False
                Debug.Print "Found at: Line: " & CStr(SL) & " Column: " & CStr(SC)
                EL = .CountOfLines
                SC = EC + 1
                EC = 255
                Found = .Find(target:=findwhat, StartLine:=SL, StartColumn:=SC, _
                    EndLine:=EL, EndColumn:=EC, _
                    wholeword:=True, MatchCase:=False, patternsearch:=False)
        Loop
End With

With CodeMod
    .DeleteLines SL, 1
    .InsertLines 1, "Const DatabasePath As String = ""\\new\path\to\database\database.mdb"""
End With

Finds the code line through text match, deletes that line number, inserts new line at the top of module (line 1).

Thanks Rorya!