Subroutine question and public variables
 

I am rather new to excel programming and I am
trying to figure out some basic things. First, I am not sure how to
declare and set a public variable. For example, in my code, I have 2
constants that represent spreadsheets. The code looks like this:


Code:
--------------------
Public sh As Worksheet
Public ls As Worksheet
Set sh = Worksheets("Assemblies")
Set ls = Worksheets("Assemblylist")
--------------------


I am not sure as to why this doesn't work.

Secondly I am trying to create a subroutine that looks for a column,
row, or both that meet certain criteria. This could be a column that
matches another field in my userform or has nothing in it. I wrote the
following code that should work, but I don't know where to put it or how
to call it once it is put there.


Code:
--------------------
Sub lookup()
Dim direction As Integer '1 for columns 2 for rows 3 for both
Dim y As Integer 'row integer
Dim x As Integer 'column integer
Dim z As String 'Don't really know what to declare this as, could be a word or a number


Do While sh.cells(y, x) < z
If direction = 1 Then x = x + 1
If direction = 2 Then y = y + 1
If direction = 3 Then x = x + 1
If direction = 3 Then y = y + 1
Loop
--------------------


Any help would be greatly appreciated.


--
musictech
------------------------------------------------------------------------
musictech's Profile: http://www.excelforum.com/member.php...o&userid=24851
View this thread: http://www.excelforum.com/showthread...hreadid=383979

Subroutine question and public variables
 

The public variables should be declared at the head of the module, outside
of a sub, but should be set within a sub

Public sh As Worksheet
Public ls As Worksheet

Sub mySub()

Set sh = Worksheets("Assemblies")
Set ls = Worksheets("Assemblylist")

End Sub

Not sure about the other, but I assume that you want the x & y values, so
maybe do something like

myX=0:myY=0
Call lookup(myX,myY)

and then use myX and myY on return



Sub lookup(x,y)
Dim direction As Integer '1 for columns 2 for rows 3 for both
Dim z As Variant

Do While sh.cells(y, x) < z
If direction = 1 Then x = x + 1
If direction = 2 Then y = y + 1
If direction = 3 Then x = x + 1
If direction = 3 Then y = y + 1
Loop



--
HTH

Bob Phillips

"musictech" wrote
in message ...

I am rather new to excel programming and I am
trying to figure out some basic things. First, I am not sure how to
declare and set a public variable. For example, in my code, I have 2
constants that represent spreadsheets. The code looks like this:


Code:
--------------------
Public sh As Worksheet
Public ls As Worksheet
Set sh = Worksheets("Assemblies")
Set ls = Worksheets("Assemblylist")
--------------------


I am not sure as to why this doesn't work.

Secondly I am trying to create a subroutine that looks for a column,
row, or both that meet certain criteria. This could be a column that
matches another field in my userform or has nothing in it. I wrote the
following code that should work, but I don't know where to put it or how
to call it once it is put there.


Code:
--------------------
Sub lookup()
Dim direction As Integer '1 for columns 2 for rows 3 for both
Dim y As Integer 'row integer
Dim x As Integer 'column integer
Dim z As String 'Don't really know what to declare this as, could be a
word or a number


Do While sh.cells(y, x) < z
If direction = 1 Then x = x + 1
If direction = 2 Then y = y + 1
If direction = 3 Then x = x + 1
If direction = 3 Then y = y + 1
Loop
--------------------


Any help would be greatly appreciated.


--
musictech
------------------------------------------------------------------------
musictech's Profile:
http://www.excelforum.com/member.php...o&userid=24851
View this thread: http://www.excelforum.com/showthread...hreadid=383979