Is it possible to edit excel vba code without opening workbook?

jmacleary

Well-known Member
Joined
Oct 5, 2015
Messages
1,060
Office Version
  1. 365
  2. 2007
Platform
  1. Windows
Hi folks.

I have inherited a complex set of 200+ workbooks which all have references to a master workbook embedded in the VBA. This reference is coded as a constant as follows:
Code:
Public Const ValidName As String = "masterbookname.xls"
Is there any way I can edit the VBA code without opening each workbook? Ideally I want to do a find and replace type operation to replace "masterbookname.xls" with "newmasterbook.xlsm".

Any help or ideas much appreciated.
 
Last edited:

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
In a word, no. You'll have to open them, either manually or in code.
 
Upvote 0
Here is an excellent page but it may put you off trying to automate
http://www.cpearson.com/excel/vbe.aspx

Here is code that allows you to search the active vbProject. Results are written to immediate window.
credit : https://www.devhut.net/2016/02/24/vba-find-term-in-vba-modulescode/
Code:
Sub TestSearch()
    Dim sText As String
    sText = "[COLOR=#ff0000][I]Search for thi[/I]s[/COLOR]"
    Call findWordInModules(sText)
End Sub

Public Sub findWordInModules(ByVal sSearchTerm As String)
    Dim oComponent            As Object
    For Each oComponent In Application.VBE.ActiveVBProject.VBComponents
        If oComponent.CodeModule.Find(sSearchTerm, 1, 1, -1, -1, False, False, False) = True Then
            Debug.Print "Module: " & oComponent.Name  'Name of the current module in which the term was found (at least once)
            'Need to execute a recursive listing of where it is found in the module since it could be found more than once
            Call listLinesinModuleWhereFound(oComponent, sSearchTerm)
        End If
    Next oComponent
End Sub
 
Sub listLinesinModuleWhereFound(ByVal oComponent As Object, ByVal sSearchTerm As String)
    Dim lTotalNoLines         As Long   'total number of lines within the module being examined
    Dim lLineNo               As Long   'will return the line no where the term is found
 
    lLineNo = 1
    With oComponent
        lTotalNoLines = .CodeModule.CountOfLines
        Do While .CodeModule.Find(sSearchTerm, lLineNo, 1, -1, -1, False, False, False) = True
            Debug.Print vbTab & "Line No:" & lLineNo & Trim(.CodeModule.Lines(lLineNo, 1))  'Remove any padding spaces
            lLineNo = lLineNo + 1    'Restart the search at the next line looking for the next occurence
        Loop
    End With
End Sub
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top