Solved

ActiveVBProject not pointing to active VB Project

Posted on 2007-12-05
2
533 Views
Last Modified: 2011-09-20
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

0
Comment
Question by:PeterFrb
2 Comments
 
LVL 59

Accepted Solution

by:
Chris Bottomley earned 500 total points
ID: 20414239
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
 

Author Closing Comment

by:PeterFrb
ID: 31412936
Thank you!  I've been working on workarounds for this problem and did not realize how simple it is.  
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

861 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question