+ Reply to Thread
Results 1 to 9 of 9

How to define Global Variable please help

  1. #1
    Forum Contributor
    Join Date
    01-03-2013
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    211

    How to define Global Variable please help

    Hello

    this code generate emails and from a Pivot table, I am trying to make it user friendly so user can type in the message that would be included in the body of the message. I was told I need to define a global variable, can anyone help please ?

    Thanks

    Please Login or Register  to view this content.

  2. #2
    Forum Contributor
    Join Date
    01-31-2007
    Location
    Aschaffenburg, Germany
    MS-Off Ver
    Office 2013
    Posts
    104

    Re: How to define Global Variable please help

    A global variable is probably to be understood as a Public declaration
    Outside of your Sub, you make that declaration by
    Please Login or Register  to view this content.
    This Public variable is valid across Subs or between Sub and UserForm without handing it over as a parameter.
    Does that look like what you are trying to find?
    Mike 1001
    _________________________
    Let's keep trying, one day we'll succeed!

  3. #3
    Forum Contributor
    Join Date
    01-03-2013
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    211

    Re: How to define Global Variable please help

    yes broadly speaking. the variables I need to define need to be in Sub Remit_generate_mail() which is the starting macro, asking user to input the body message
    but I want Sub AttachtoEmail() to recognise them and enter them on each email...make sense ?

  4. #4
    Forum Guru Izandol's Avatar
    Join Date
    03-29-2012
    Location
    *
    MS-Off Ver
    Excel 20(03|10|13)
    Posts
    2,581

    Re: How to define Global Variable please help

    It is better to pass variables to routine directly.

    Please Login or Register  to view this content.
    this routine may now use sBody variable for message. Main routine will call in this way:
    Please Login or Register  to view this content.
    • Please remember to mark threads Solved with Thread Tools link at top of page.
    • Please use code tags when posting code: [code]Place your code here[/code]
    • Please read Forum Rules

  5. #5
    Forum Contributor
    Join Date
    01-31-2007
    Location
    Aschaffenburg, Germany
    MS-Off Ver
    Office 2013
    Posts
    104

    Re: How to define Global Variable please help

    Don't confuse declaration of a variable with assigning a value to it!
    That Public declaration would have to be outside of any Sub; assigning the value to it, however, could be done in your Remit_generate_mail() and would keep that value in AttachtoEmail() as well.

  6. #6
    Forum Contributor
    Join Date
    01-03-2013
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    211

    Re: How to define Global Variable please help

    Hi Izandol, do you mind excusing my ignorance , I tried to apply your suggestion but I got an error "Function call on-hand side of assignment must return variant or object..can you see where I wrong please ?

    Please Login or Register  to view this content.

  7. #7
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,620

    Re: How to define Global Variable please help

    This should fix that error, and some others.
    Please Login or Register  to view this content.
    If posting code please use code tags, see here.

  8. #8
    Forum Contributor
    Join Date
    01-03-2013
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    211

    Re: How to define Global Variable please help

    Quote Originally Posted by Mike1001 View Post
    Don't confuse declaration of a variable with assigning a value to it!
    That Public declaration would have to be outside of any Sub; assigning the value to it, however, could be done in your Remit_generate_mail() and would keep that value in AttachtoEmail() as well.
    Really ? that is good if it can be done..do you mind putting it in the Macros ,,,I am totally lost here ..

  9. #9
    Forum Contributor
    Join Date
    01-03-2013
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    211

    Re: How to define Global Variable please help

    Quote Originally Posted by Norie View Post
    This should fix that error, and some others.
    Please Login or Register  to view this content.
    you are genius..Thank you
    Last edited by bimo; 10-23-2014 at 02:18 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Global variable declared in Userform. Variable value is not sticking
    By timtim89 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 05-25-2012, 08:50 AM
  2. [SOLVED] Define global worksheet name for a userform
    By zbor in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 05-22-2012, 10:22 AM
  3. How to define a Global variable
    By welchs101 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-16-2011, 10:44 AM
  4. Replies: 0
    Last Post: 09-06-2005, 01:05 AM
  5. Replies: 1
    Last Post: 07-08-2005, 09:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1