Excel 2010 VBA - Using Public Variables and Constants in other workbooks?

Posted on 2014-08-03
Last Modified: 2014-08-18
Can Public Variables be used in workbooks other than the one they are declared in?
Workbook 1 is currently open and has a public variable declared TOTALSALES
Workbook 2 is also open.  Can this workbook use the TOTALSALES variable declared in Workbook 1?

How about using constants?   If constants are declared as PUBLIC?
Question by:brothertruffle880
    LVL 44

    Accepted Solution

    No they can't be but you may be able to create functions  in the other workbook to return values.
    LVL 29

    Expert Comment

    I don't think you can declare a constant as Public in an object module (such as a workbook) so the answer there is obviously no.  

    As for Public variables, you certainly can refer to one from another worksheet following a convention similar to this:
    MsgBox Sheets("Sheet1").TOTALSALES

    Open in new window

    LVL 44

    Expert Comment

    by:Martin Liss
    He's asking about sharing between workbooks.
    LVL 29

    Expert Comment

    Oops my bad, my mind was thinking worksheets:-)

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
    If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
    The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
    This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

    746 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

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now