+ Reply to Thread
Results 1 to 2 of 2

Subroutine question and public variables

  1. #1
    Registered User
    Join Date
    07-01-2005
    Posts
    7

    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:

    Please Login or Register  to view this content.
    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.

    Please Login or Register  to view this content.
    Any help would be greatly appreciated.

  2. #2
    Bob Phillips
    Guest

    Re: 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" <[email protected]> wrote
    in message news:[email protected]...
    >
    > 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
    >




+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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