Solved

ActiveVBProject not pointing to active VB Project

Posted on 2007-12-05
2
528 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

809 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