6 Replies - 1650 Views - Last Post: 09 October 2014 - 11:49 AM Rate Topic: -----

#1 Landrum   User is offline

  • New D.I.C Head

Reputation: 3
  • View blog
  • Posts: 41
  • Joined: 27-August 14

How to get the name of the vba project in excel

Posted 09 October 2014 - 08:18 AM

I am now working on a excel application level addin. I have opened a workbook and renamed the vba project to "Syntactical_CPIFM", I can't seem to figure out how to I get a list of the current vba projects of the workbook. The reason I am doing this is because if the workbook hasn't been set up for this particular function I don't want to show the button. I am not using vba at all in the document I was just wanting to use the project name to tell me if the workbook has been set up for this particular button. I could just write a value to a cell and pull the test from there but that seemed a little more likely to have someone accidently delete it or change it. Are there other properties of a workbook that I can change to store information to test if this workbook has been setup? I also thought I could kinda use the vba projects as a way to store a lot of parameters to the workbook is this crazy?


Private Sub Workbook_opening() Handles Application.WorkbookOpen
        If Application.ActiveWorkbook.HasVBProject = True Then
            'below is the error
            If Application.ThisWorkbook.VBProject = "Syntactical_CPIFM" Then
                Globals.Ribbons.Ribbon1.CPI_DATA_Button.Visible = True
            Else
                Globals.Ribbons.Ribbon1.CPI_DATA_Button.Visible = False
            End If
        End If
    End Sub



Is This A Good Question/Topic? 0
  • +

Replies To: How to get the name of the vba project in excel

#2 andrewsw   User is offline

  • RequestedRangeNotSatisfiable
  • member icon

Reputation: 6561
  • View blog
  • Posts: 26,607
  • Joined: 12-December 12

Re: How to get the name of the vba project in excel

Posted 09 October 2014 - 08:29 AM

You should tell us what any error messages are.

It is workbookObject.VBProject.Name but you can only read this if the option has been checked (in Excel Options, Trust Centre, Macro Settings) to trust access to the VBA project settings. It is probably better to use some error handling code and try a call a dummy procedure in the file; if this fails, the project doesn't exist.

I confirmed the above information by first testing in the Immediate Window of the VBA Editor. You should make use of this environment, and the macro recorder, when working with Excel.

Quote

I also thought I could kinda use the vba projects as a way to store a lot of parameters to the workbook is this crazy?

I suppose you could call VBA macros in the workbook, which then stores values in a hidden worksheet, but as you have access to the workbook you could do this directly anyway.

This post has been edited by andrewsw: 09 October 2014 - 08:30 AM

Was This Post Helpful? 1
  • +
  • -

#3 Landrum   User is offline

  • New D.I.C Head

Reputation: 3
  • View blog
  • Posts: 41
  • Joined: 27-August 14

Re: How to get the name of the vba project in excel

Posted 09 October 2014 - 09:25 AM

Thanks a bunch andrewsw I took your advice on having it run a dummy procedure. In my workbook VBA I added a module with the function
Public Function Test_book() As Boolean
Test_book = True
End Function



Then in my application level addin I added these two so even if multiple workbooks are open they can't click the button on the wrong workbook.
    Private Sub Workbook_opening() Handles Application.WorkbookOpen

        Try
'trys to call the procedure
            If Application.Run("Test_book") = True Then

                Globals.Ribbons.Ribbon1.CPI_DATA_Button.Visible = True
            Else
                Globals.Ribbons.Ribbon1.CPI_DATA_Button.Visible = False
            End If
        Catch ex As Exception
            Globals.Ribbons.Ribbon1.CPI_DATA_Button.Visible = False
        End Try

    End Sub
    Private Sub Workbook_activating() Handles Application.WorkbookActivate

        Try
            If Application.Run("Test_book") = True Then
                Globals.Ribbons.Ribbon1.CPI_DATA_Button.Visible = True
            Else
                Globals.Ribbons.Ribbon1.CPI_DATA_Button.Visible = False
            End If
        Catch ex As Exception
            Globals.Ribbons.Ribbon1.CPI_DATA_Button.Visible = False
        End Try

    End Sub



That worked like a charm! I like this technique would've never thought of that! Really neat, thanks man!
Was This Post Helpful? 0
  • +
  • -

#4 andrewsw   User is offline

  • RequestedRangeNotSatisfiable
  • member icon

Reputation: 6561
  • View blog
  • Posts: 26,607
  • Joined: 12-December 12

Re: How to get the name of the vba project in excel

Posted 09 October 2014 - 09:31 AM

No worries ;)
Was This Post Helpful? 0
  • +
  • -

#5 rusoaica   User is offline

  • They're watching you, Neo!
  • member icon

Reputation: 217
  • View blog
  • Posts: 689
  • Joined: 10-March 12

Re: How to get the name of the vba project in excel

Posted 09 October 2014 - 11:09 AM

Just as a side note,

Public Function Test_book() As Boolean
   Test_book = True
End Function



if a functions doesn't return anything, than it is not a function, but a Sub
Was This Post Helpful? 0
  • +
  • -

#6 andrewsw   User is offline

  • RequestedRangeNotSatisfiable
  • member icon

Reputation: 6561
  • View blog
  • Posts: 26,607
  • Joined: 12-December 12

Re: How to get the name of the vba project in excel

Posted 09 October 2014 - 11:45 AM

View Postrusoaica, on 09 October 2014 - 06:09 PM, said:

if a functions doesn't return anything, than it is not a function, but a Sub

This Function is returning the value True, that's how VBA returns a value, by assigning it to the Function-name.

This post has been edited by andrewsw: 09 October 2014 - 11:46 AM

Was This Post Helpful? 1
  • +
  • -

#7 rusoaica   User is offline

  • They're watching you, Neo!
  • member icon

Reputation: 217
  • View blog
  • Posts: 689
  • Joined: 10-March 12

Re: How to get the name of the vba project in excel

Posted 09 October 2014 - 11:49 AM

Aww, my bad! I read it as straight VB.NET. Sorry! :)
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1