Solved

ActiveVBProject not pointing to active VB Project

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

680 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