topic Re: Public Variable Accessed by Function? in VBA Forum

Public Variable Accessed by Function?

Anonymous — Tue, 21 Aug 2007 21:25:02 GMT

'I have a public variable:
Public RefZone1Rng As Range

'That I set in a Sub:
Sub CheckZones()
Set RefZone1Rng = Refwrksht.Range(Refwrksht.Cells(2, 1), Refwrksht.Cells(RefZone1_Bot, 1))

'That I would like to be available to a Function that I pass some variables to within the first sub:
ColorNum = Search_ref_zone(Choice, BZone)
End Sub

'The following is the function that I go to. I would like it to recognize that RefZone1Rng has been declaired in the procedure above without having to pass it the variables explicitely.
Function Search_ref_zone(RefZone As Integer, BZone As String)
Dim SelRange As Range
Select Case RefZone
Case 1
SelRange = RefZone1Rng
End Select
End Function

'Is this possible? Thank you!

Re: Public Variable Accessed by Function?

Anonymous — Wed, 22 Aug 2007 06:33:28 GMT

Hi NSL!

Your Problem is that 'Range' is not a type of variable used by AutoCAD but
only used by Excel.
So AutoCAD can't initialize! So use Type Variant and then you succeed.

BERNIE

wrote in message news:5696790@discussion.autodesk.com...
'I have a public variable:
Public RefZone1Rng As Range

'That I set in a Sub:
Sub CheckZones()
Set RefZone1Rng = Refwrksht.Range(Refwrksht.Cells(2, 1),
Refwrksht.Cells(RefZone1_Bot, 1))

'That I would like to be available to a Function that I pass some variables
to within the first sub:
ColorNum = Search_ref_zone(Choice, BZone)
End Sub

'The following is the function that I go to. I would like it to recognize
that RefZone1Rng has been declaired in the procedure above without having to
pass it the variables explicitely.
Function Search_ref_zone(RefZone As Integer, BZone As String)
Dim SelRange As Range
Select Case RefZone
Case 1
SelRange = RefZone1Rng
End Select
End Function

'Is this possible? Thank you!

Re: Public Variable Accessed by Function?

Anonymous — Wed, 22 Aug 2007 10:33:57 GMT

Bernie, that's not it....

It is simply that an Excel 'Range' is an object, not a variable, so you have to...

SET SelRange = RefZone1Rng

Cheers,
Dave

Re: Public Variable Accessed by Function?

dgorsman — Wed, 22 Aug 2007 15:46:04 GMT

Also, if this code is running inside AutoCAD, its probably a good idea to at least partially specify objects from other applications e.g. Excel.Range, rather than just Range. If somebody else has to work on the code, they don't have to scratch their head about what a Range object is.

Re: Public Variable Accessed by Function?

Anonymous — Wed, 22 Aug 2007 20:23:16 GMT

Blast... It still doesn't work. It at least recognizes RefZone1Rng in the 2nd procedure though.

It successfully sets the range in the Check_Zones procedure, but once ownership goes to the Search_ref_Zone procedure, the RefZone1Rng range goes to nothing.

It doesn't make any sense...
Anyways, Thanks SysRq for the help! Thanks everyone else too!

Re: Public Variable Accessed by Function?

Anonymous — Wed, 22 Aug 2007 21:17:46 GMT

My bad. I figured it out. I accidentally Double defined the public ranges. Once outside of the procedures. Then 2nd, inside the first procedure.

Cool beans. Thanks again!