ActiveVBProject not pointing to active VB Project

I am using code (see attached snippet) that I wish would give me the workbook connected to the active code module.  However, there is a "hidden" code module that automatically comes attached with any VB program I write in Excel called "Insert Visio Button.XLS", which is obviously an addon from Visio.  I say "hidden" because the Excel file is not visible from the Excel application, but it shows up in the Project Explorer window.  What I'm finding is that the code below most often returns the workbook connected to code, but it sometimes returns the workbook connected to "Insert Visio Button.XLS", which, in my view, should never truly be the active code module.  
If someone has a better solution to achieve the desired result, I'm listening.
Thank you, Peter Ferber

Function WorkbookConnectedToCode(UseApp As Excel.Application) As Workbook
    Set WorkbookConnectedToCode = UseApp.Workbooks(GetFullFileName(UseApp.VBE.ActiveVBProject.Filename))
End Function
 
Function GetFullFileName(strFullPath As String) As String
    Dim iLastSlash As Integer
 
    iLastSlash = InStrRev(strFullPath, "\")
    GetFullFileName = Mid(strFullPath, iLastSlash + 1)
End Function

Open in new window

PeterFrbWeb development, Java scripting, Python TrainingAsked:
Who is Participating?
 
Chris BottomleyConnect With a Mentor Software Quality Lead EngineerCommented:
thisworkbook relates to teh parent excel file for a specific macro therefore where you would call your function just use the literal thisworkbook.

Chris
0
 
PeterFrbWeb development, Java scripting, Python TrainingAuthor Commented:
Thank you!  I've been working on workarounds for this problem and did not realize how simple it is.  
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.