Re: $$Excel-Macros$$ how to declare define global range in vba
Hi Ayush, Do the following steps 1. crate a function for your range ex: Public Function nRange() As Range 'Dim rng As Range 'Set rng = Sheets(M-RAW).Range(A1:A15) Set nRange = Sheets(M-RAW).Range(A1:A15) End Function 2) Now call this function where you want to use the defined range ex: Sub myMethod() Dim rng, cel As Range Set rng = nRange For Each cel In nRange MsgBox (cel.Value) Next End Sub After create a function you can call it in any module I hope this will solve your problem If have any query let me know. Regards, Swapnil. On Wed, Feb 3, 2010 at 9:44 AM, ayush jain jainayus...@gmail.com wrote: Hi Paul, I have been using the Standard module. The idea is to create an add-in. The problem is that I have to use a particular range in different modules and different procedures but i don't want to define them in each procedure. Any idea ? Thank you. On Tue, Feb 2, 2010 at 10:59 PM, Paul Schreiner schreiner_p...@att.netwrote: What module are you putting the declaration in? For instance, if you right-click on a sheet tab and select View Code it opens up the VBA Sheet Module. You'll notice a module called ThisWorkBook If you create standard Modules, there will be a folder called Modules and a list of modules (default names are Module1, Module2) A standard module will be created if you record a macro. You cannot declare Public variables in Sheet modules. They MUST be in 'standard' modules. Although they MIGHT be allowed in 'class' modules, but not in form modules. Next. You can DECLARE it using: Public but I think you have to SET it within a macro. I THINK basically it's because sheets and ranges are dynamic and you're trying to treat a variable as a constant But I could be wrong. Paul -- *From:* Ayush jainayus...@gmail.com *To:* MS EXCEL AND VBA MACROS excel-macros@googlegroups.com *Sent:* Tue, February 2, 2010 11:24:36 AM *Subject:* $$Excel-Macros$$ how to declare define global range in vba Hello Group, How can we declare and define Global range in vba , so that it can be used across procedures modules ? Public rngNetLineAmt as range Set rngNetLineAmt = Sheets(M-RAW).Range(AQ:AQ) If i use above code outside the procedure..it does not work and throws error. Kindly advise. Thank you so much. -Ayush Jain -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at http://www.excelitems.com 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com If you find any spam message in the group, please send an email to: Ayush Jain @ jainayus...@gmail.com HELP US GROW !! We reach over 6,700 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at http://www.excelitems.com 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com If you find any spam message in the group, please send an email to: Ayush Jain @ jainayus...@gmail.com HELP US GROW !! We reach over 6,700 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at http://www.excelitems.com 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com If you find any spam message in the group, please send an email to: Ayush Jain @ jainayus...@gmail.com HELP US GROW !! We reach over 6,700 subscribers worldwide and receive many nice notes about the learning and support from
Re: $$Excel-Macros$$ how to declare define global range in vba
What module are you putting the declaration in? For instance, if you right-click on a sheet tab and select View Code it opens up the VBA Sheet Module. You'll notice a module called ThisWorkBook If you create standard Modules, there will be a folder called Modules and a list of modules (default names are Module1, Module2) A standard module will be created if you record a macro. You cannot declare Public variables in Sheet modules. They MUST be in 'standard' modules. Although they MIGHT be allowed in 'class' modules, but not in form modules. Next. You can DECLARE it using: Public but I think you have to SET it within a macro. I THINK basically it's because sheets and ranges are dynamic and you're trying to treat a variable as a constant But I could be wrong. Paul From: Ayush jainayus...@gmail.com To: MS EXCEL AND VBA MACROS excel-macros@googlegroups.com Sent: Tue, February 2, 2010 11:24:36 AM Subject: $$Excel-Macros$$ how to declare define global range in vba Hello Group, How can we declare and define Global range in vba , so that it can be used across procedures modules ? Public rngNetLineAmt as range Set rngNetLineAmt = Sheets(M-RAW).Range(AQ:AQ) If i use above code outside the procedure..it does not work and throws error. Kindly advise. Thank you so much. -Ayush Jain -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at http://www.excelitems.com 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com If you find any spam message in the group, please send an email to: Ayush Jain @ jainayus...@gmail.com HELP US GROW !! We reach over 6,700 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at http://www.excelitems.com 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com If you find any spam message in the group, please send an email to: Ayush Jain @ jainayus...@gmail.com HELP US GROW !! We reach over 6,700 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
RE: $$Excel-Macros$$ how to declare define global range in vba
Hi Ayush, What do you mean by outside the procedure'? Dave. Date: Tue, 2 Feb 2010 08:24:36 -0800 Subject: $$Excel-Macros$$ how to declare define global range in vba From: jainayus...@gmail.com To: excel-macros@googlegroups.com Hello Group, How can we declare and define Global range in vba , so that it can be used across procedures modules ? Public rngNetLineAmt as range Set rngNetLineAmt = Sheets(M-RAW).Range(AQ:AQ) If i use above code outside the procedure..it does not work and throws error. Kindly advise. Thank you so much. -Ayush Jain -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at http://www.excelitems.com 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com If you find any spam message in the group, please send an email to: Ayush Jain @ jainayus...@gmail.com HELP US GROW !! We reach over 6,700 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe _ Time for a new car? Sell your old one fast! http://clk.atdmt.com/NMN/go/157637060/direct/01/ -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at http://www.excelitems.com 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com If you find any spam message in the group, please send an email to: Ayush Jain @ jainayus...@gmail.com HELP US GROW !! We reach over 6,700 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
Re: $$Excel-Macros$$ how to declare define global range in vba
Hi Paul, I have been using the Standard module. The idea is to create an add-in. The problem is that I have to use a particular range in different modules and different procedures but i don't want to define them in each procedure. Any idea ? Thank you. On Tue, Feb 2, 2010 at 10:59 PM, Paul Schreiner schreiner_p...@att.netwrote: What module are you putting the declaration in? For instance, if you right-click on a sheet tab and select View Code it opens up the VBA Sheet Module. You'll notice a module called ThisWorkBook If you create standard Modules, there will be a folder called Modules and a list of modules (default names are Module1, Module2) A standard module will be created if you record a macro. You cannot declare Public variables in Sheet modules. They MUST be in 'standard' modules. Although they MIGHT be allowed in 'class' modules, but not in form modules. Next. You can DECLARE it using: Public but I think you have to SET it within a macro. I THINK basically it's because sheets and ranges are dynamic and you're trying to treat a variable as a constant But I could be wrong. Paul -- *From:* Ayush jainayus...@gmail.com *To:* MS EXCEL AND VBA MACROS excel-macros@googlegroups.com *Sent:* Tue, February 2, 2010 11:24:36 AM *Subject:* $$Excel-Macros$$ how to declare define global range in vba Hello Group, How can we declare and define Global range in vba , so that it can be used across procedures modules ? Public rngNetLineAmt as range Set rngNetLineAmt = Sheets(M-RAW).Range(AQ:AQ) If i use above code outside the procedure..it does not work and throws error. Kindly advise. Thank you so much. -Ayush Jain -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at http://www.excelitems.com 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com If you find any spam message in the group, please send an email to: Ayush Jain @ jainayus...@gmail.com HELP US GROW !! We reach over 6,700 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at http://www.excelitems.com 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com If you find any spam message in the group, please send an email to: Ayush Jain @ jainayus...@gmail.com HELP US GROW !! We reach over 6,700 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at http://www.excelitems.com 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com If you find any spam message in the group, please send an email to: Ayush Jain @ jainayus...@gmail.com HELP US GROW !! We reach over 6,700 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
