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

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
38,820
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
In a word, no. You'll have to open them, either manually or in code.
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
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:
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,604
Messages
5,838,326
Members
430,538
Latest member
PedroOliveira

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
Top