Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Declare a Public Array
Per Jessen has provided me with a great deal of help, and one of the thinks
he has helped me with is to use an array of ranges not separate variables. Among the lines of code he has provided one was: Dim rngArr() As Range It became apparent in the course of my work that I would need to refer to that array in additional modules that were called from the userform in which rngArr() As Range dimensioned. He told me to declare rngArr() As Range as public and outside the module. By outside the module I assumed that to mean that the line Public rngArr() As Range should be placed above all other code for the userform (before the first sub routine). I have done that but get the following error message: Compile Error Constants, fixed-length strings, arrays, user-defined types and Declare Statements not allowed as Public members or object modules So is there any way in which to declare this array as Public? Below is everything I am declaring (what I believe to be outside the module) above the first sub routine. Option Explicit Dim rMyCell As Range Public rng As Range Dim c As Range Dim Counter As Long Dim myRng As Range Dim my1Rng As Range Public MissData As Boolean Public BlkProc As Boolean Private Sub ComboBox1001_Change() |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Declare a Public Array
Sorry I copied from a version before I added the Public rngArr() As Range
line. Below is whit I intended to send: Option Explicit Dim rMyCell As Range Dim c As Range Dim Counter As Long Dim myRng As Range Dim my1Rng As Range Public rng As Range Public rngArr() As Range Public MissData As Boolean Public BlkProc As Boolean Private Sub ComboBox1001_Change() "Patrick C. Simonds" wrote in message ... Per Jessen has provided me with a great deal of help, and one of the thinks he has helped me with is to use an array of ranges not separate variables. Among the lines of code he has provided one was: Dim rngArr() As Range It became apparent in the course of my work that I would need to refer to that array in additional modules that were called from the userform in which rngArr() As Range dimensioned. He told me to declare rngArr() As Range as public and outside the module. By outside the module I assumed that to mean that the line Public rngArr() As Range should be placed above all other code for the userform (before the first sub routine). I have done that but get the following error message: Compile Error Constants, fixed-length strings, arrays, user-defined types and Declare Statements not allowed as Public members or object modules So is there any way in which to declare this array as Public? Below is everything I am declaring (what I believe to be outside the module) above the first sub routine. Option Explicit Dim rMyCell As Range Public rng As Range Dim c As Range Dim Counter As Long Dim myRng As Range Dim my1Rng As Range Public MissData As Boolean Public BlkProc As Boolean Private Sub ComboBox1001_Change() |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Declare a Public Array
Are you sure that he was suggesting setting the array as type Range, as that
means that it will store several ranges, and would be loaded something like this ReDim rngArr(1 To 2) Set rngArr(1) = Range("A1:A10") Set rngArr(2) = Range("C1:C10") I would have thought he was sayin g set it up as type variant and drop a range into it and process that array. Like this Option Explicit Dim rMyCell As Range Dim c As Range Dim Counter As Long Dim myRng As Range Dim my1Rng As Range Public rng As Range Public rngArr() As Variant Public MissData As Boolean Public BlkProc As Boolean Private Sub ComboBox1001_Change() rngArr = Range("A1:A10") End Sub and you can then use rngArr elsewhere -- __________________________________ HTH Bob "Patrick C. Simonds" wrote in message ... Sorry I copied from a version before I added the Public rngArr() As Range line. Below is whit I intended to send: Option Explicit Dim rMyCell As Range Dim c As Range Dim Counter As Long Dim myRng As Range Dim my1Rng As Range Public rng As Range Public rngArr() As Range Public MissData As Boolean Public BlkProc As Boolean Private Sub ComboBox1001_Change() "Patrick C. Simonds" wrote in message ... Per Jessen has provided me with a great deal of help, and one of the thinks he has helped me with is to use an array of ranges not separate variables. Among the lines of code he has provided one was: Dim rngArr() As Range It became apparent in the course of my work that I would need to refer to that array in additional modules that were called from the userform in which rngArr() As Range dimensioned. He told me to declare rngArr() As Range as public and outside the module. By outside the module I assumed that to mean that the line Public rngArr() As Range should be placed above all other code for the userform (before the first sub routine). I have done that but get the following error message: Compile Error Constants, fixed-length strings, arrays, user-defined types and Declare Statements not allowed as Public members or object modules So is there any way in which to declare this array as Public? Below is everything I am declaring (what I believe to be outside the module) above the first sub routine. Option Explicit Dim rMyCell As Range Public rng As Range Dim c As Range Dim Counter As Long Dim myRng As Range Dim my1Rng As Range Public MissData As Boolean Public BlkProc As Boolean Private Sub ComboBox1001_Change() |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Declare a Public Array
Patrick,
Maybe I wasn't explicit in my explanation. As you have realized you can not declare a public variable as a Range in a userform module, but if you declare the public range variable(s) in a general module everything is working fine. Regards, Per "Patrick C. Simonds" skrev i meddelelsen ... Sorry I copied from a version before I added the Public rngArr() As Range line. Below is whit I intended to send: Option Explicit Dim rMyCell As Range Dim c As Range Dim Counter As Long Dim myRng As Range Dim my1Rng As Range Public rng As Range Public rngArr() As Range Public MissData As Boolean Public BlkProc As Boolean Private Sub ComboBox1001_Change() "Patrick C. Simonds" wrote in message ... Per Jessen has provided me with a great deal of help, and one of the thinks he has helped me with is to use an array of ranges not separate variables. Among the lines of code he has provided one was: Dim rngArr() As Range It became apparent in the course of my work that I would need to refer to that array in additional modules that were called from the userform in which rngArr() As Range dimensioned. He told me to declare rngArr() As Range as public and outside the module. By outside the module I assumed that to mean that the line Public rngArr() As Range should be placed above all other code for the userform (before the first sub routine). I have done that but get the following error message: Compile Error Constants, fixed-length strings, arrays, user-defined types and Declare Statements not allowed as Public members or object modules So is there any way in which to declare this array as Public? Below is everything I am declaring (what I believe to be outside the module) above the first sub routine. Option Explicit Dim rMyCell As Range Public rng As Range Dim c As Range Dim Counter As Long Dim myRng As Range Dim my1Rng As Range Public MissData As Boolean Public BlkProc As Boolean Private Sub ComboBox1001_Change() |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Declare a Public Array
The key word in the error message is
or object modules An object module is any one of the following: a Class module, the ThisWorkbook module, one of the Sheet modules, a Userform's code module. In these types of modules, you cannot declare public arrays. The array can be declared as Private, and it will work fine but will not be accessible (read or write) to code outside of the module in which it was declared. If you declare the array variable in a regular code module, it will be accessible from any procedure in any module, including object modules. By outside the module I assumed that to mean that the line Public rngArr() As Range should be placed above all other code That is correct. The term "Scope" refers to where a variable is declared and from where it can be accessed (written to or read from). For a detailed discussion of Scope as it applies to both variables and procedures, see http://www.cpearson.com/excel/Scope.aspx . Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Sun, 27 Sep 2009 22:31:05 -0700, "Patrick C. Simonds" wrote: Per Jessen has provided me with a great deal of help, and one of the thinks he has helped me with is to use an array of ranges not separate variables. Among the lines of code he has provided one was: Dim rngArr() As Range It became apparent in the course of my work that I would need to refer to that array in additional modules that were called from the userform in which rngArr() As Range dimensioned. He told me to declare rngArr() As Range as public and outside the module. By outside the module I assumed that to mean that the line Public rngArr() As Range should be placed above all other code for the userform (before the first sub routine). I have done that but get the following error message: Compile Error Constants, fixed-length strings, arrays, user-defined types and Declare Statements not allowed as Public members or object modules So is there any way in which to declare this array as Public? Below is everything I am declaring (what I believe to be outside the module) above the first sub routine. Option Explicit Dim rMyCell As Range Public rng As Range Dim c As Range Dim Counter As Long Dim myRng As Range Dim my1Rng As Range Public MissData As Boolean Public BlkProc As Boolean Private Sub ComboBox1001_Change() |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Declare a Public Array
Thank you sir. The link you provided lead me in the right direction. I
declared rngArr() As Range under ThisWorkbook and gave it a project scope thereby solving my problem. "Chip Pearson" wrote in message ... The key word in the error message is or object modules An object module is any one of the following: a Class module, the ThisWorkbook module, one of the Sheet modules, a Userform's code module. In these types of modules, you cannot declare public arrays. The array can be declared as Private, and it will work fine but will not be accessible (read or write) to code outside of the module in which it was declared. If you declare the array variable in a regular code module, it will be accessible from any procedure in any module, including object modules. By outside the module I assumed that to mean that the line Public rngArr() As Range should be placed above all other code That is correct. The term "Scope" refers to where a variable is declared and from where it can be accessed (written to or read from). For a detailed discussion of Scope as it applies to both variables and procedures, see http://www.cpearson.com/excel/Scope.aspx . Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Sun, 27 Sep 2009 22:31:05 -0700, "Patrick C. Simonds" wrote: Per Jessen has provided me with a great deal of help, and one of the thinks he has helped me with is to use an array of ranges not separate variables. Among the lines of code he has provided one was: Dim rngArr() As Range It became apparent in the course of my work that I would need to refer to that array in additional modules that were called from the userform in which rngArr() As Range dimensioned. He told me to declare rngArr() As Range as public and outside the module. By outside the module I assumed that to mean that the line Public rngArr() As Range should be placed above all other code for the userform (before the first sub routine). I have done that but get the following error message: Compile Error Constants, fixed-length strings, arrays, user-defined types and Declare Statements not allowed as Public members or object modules So is there any way in which to declare this array as Public? Below is everything I am declaring (what I believe to be outside the module) above the first sub routine. Option Explicit Dim rMyCell As Range Public rng As Range Dim c As Range Dim Counter As Long Dim myRng As Range Dim my1Rng As Range Public MissData As Boolean Public BlkProc As Boolean Private Sub ComboBox1001_Change() |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Declare and Set Public variables | Excel Discussion (Misc queries) | |||
how to declare a public workbook? | Excel Programming | |||
Public declare for Set.... | Excel Programming | |||
How to declare variable as public. | Excel Programming | |||
PUBLIC DECLARE | Excel Programming |