• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 170
  • Last Modified:

Reference a Defined Name in VBA in an Excel AddIn

I have added a Defined Name called "ConnectionString" on "Sheet1" in an Excel AddIn I have written.  What is the proper way to reference that Defined Name within the VBA of the AddIn itself?

ActiveWorkbook.Range("ConnectionString").Value   and   ThisWorkBook.Range("ConnectionString").Value
are not correct because they will look for the defined name in the file the user is working with and not the AddIn workbook.   I want to reference the name inside the VBA of the AddIn.

Thanks,
Jerry
0
Jerry Paladino
Asked:
Jerry Paladino
1 Solution
 
gowflowCommented:
when you reach Sheet1 in VBA of the Addin
Dim WS as worksheet
set WS = sheets("Sheet1")
WS.Range("ConnectionString").Value  .... should reference what you want.

gowflow
0
 
Glenn RayExcel VBA DeveloperCommented:
I believe you'll need to activate the Add-In file in order to retrieve the defined name.  For example:

Sub ProcessIt()
    Dim strActiveWB as String

    strActiveWB = ActiveWorkbook.Name

    Application.Workbooks("addinname").Activate 'replace with actual filename in quotes including extension - xlam
    strCS = Sheets("Sheet1").Range("ConnectionString").Value

.
.
    Application.Workbooks(strActiveWB).Activate

End Sub

Open in new window


Regards,
-Glenn
0
 
gowflowCommented:
or I should rather say

ActiveWorkbook.Sheets("Sheet1").Range("ConnectionString").Value

gowflow
0
 
Wayne Taylor (webtubbs)Commented:
You can get the value of a range in a worksheet of the Add-In in the same way you would any other workbook....

    Workbooks("AddInName.xlam").Worksheets("Sheet1").Range("ConnectionString").Value
0
 
Jerry PaladinoAuthor Commented:
Wayne,

Thank you...   Yes, this works well for what I am trying to accomplish.   A nice plus is that since the AddIn is already loaded I can reference the AddInName without hard coding the path or using Application.UserLibraryPath to get the path.  Much appreciated.

gowflow & Glenn - thank you both for your suggestions.

Thanks,
Jerry
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.

Join & Write a Comment

Featured Post

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now