-1

I am using VBA to automate some tasks in and between some Word and Excel files (Office 2010). Right now I have the VBA code that is used only by Word stored in the Word file, and the VBA code that is used only by Excel stored in the Excel file, with the cross-application VBA stuff stored in the Word file. However, there are some user-defined types and constants that I would like to use in both the Word-specific and Excel-specific VBA code. Is there an application-neutral way I can store these shared data types and constants in a separate module, such as maybe in VBscript, where it can be used by both Excel and Word, without having to define them in both places? If so, how do I reference that outside file in each template?

Deduplicator
  • 44,692
  • 7
  • 66
  • 118
user3681913
  • 1
  • 1
  • You could store it in a file someplace. But I have found that sometimes it is useful to store certain constants as a Custom Property of one or both documents – Ron Rosenfeld Jan 06 '16 at 19:39

1 Answers1

2

You could store them in a .bas file, and import an update. For excel, import using:

Public Sub GetModules()
    ThisWorkbook.VBProject.VBComponents("modCommons").Name = "DELETEME"
    ThisWorkbook.VBProject.VBComponents.Remove ThisWorkbook.VBProject.VBComponents("DELETEME")
    ThisWorkbook.VBProject.VBComponents.Import ("C:\modCommons.bas")
End Sub

modCommons.bas

Attribute VB_Name = "modCommons"
    Public Const itest As Integer = 101
    Public Const itest2 As Integer = 102

    Public Sub TestSub
        msgbox "test success"
    End Sub

For Word, replace ThisWorkbook with ThisDocument.

BArms
  • 21
  • 4
  • Thank you! This worked great except for initially I kept getting a run-time error 1004, something about VBproject object failed. I searched that error and found that I needed to go into the Trust Center options in my Excel file and check the option to Trust access to VBA Project Object Model. Once I did that, everything was great. – user3681913 Jan 07 '16 at 03:49