Results 1 to 14 of 14
  1. #1
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Cape Cod
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Passing Object Variable to a Second Macro (Excel2000)

    When trying to pass an Object reference from one macro to another I get the error message--"CompileError--wrong number of arguments or invalid property assignment.
    I set SelectCell as an object variable using Microsoft Input Box in SubTransfer().
    I am attempting to transfer the value of SelectCell to another macro-SubFinalCopy()
    In the receiving macro (subFinalCopy())I inserted the entry "Transfer SelectCell". According to what I can find in the books I should be able to use SelectCell as the active cell to begin an Offset but I get the error message.

    Paul B .

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Passing Object Variable to a Second Macro (Excel2000)

    You should define Transfer as a function:

    Function Transfer() As Range
    ...
    Transfer = SelectCell
    End Function

    You can then use this function elsewhere:

    Sub FinalCopy()
    MsgBox Transfer.Address
    End Sub

    Or you could make SelectCell a module-level variable, i.e. declare it at the top of the module, before all Subs and Functions. It will then be available in all Subs and Functions in the module.

  3. #3
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Cape Cod
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Passing Object Variable to a Second Macro (Excel2000)

    Hans, I've been trying for two weeks to get this code to work:
    Function StartingCell() As Range
    Dim SelectCell As Range
    Sheets("FinalSort").Select
    Set SelectCell=Application.InputBox(Prompt:="Enter Starting Cell" ,Type:=8)
    End Function

    Sub Transfer()
    Dim SelectCell As Range
    Dim Range1 AS Range
    Call StartingCell
    Set Range1= Range(SelectCell,SelectCell.Offset(7,4)
    The cursor on Range1 indicates "nothing". On Range(etc) the error message 91 ==Object variable or With Block Not Set

    Paul B

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Passing Object Variable to a Second Macro (Excel2000)

    That is because you don't set the return value of StartingCell. The procedure Transfer doesn't "know" the variable SelectCell that you define in the StartingCell function, only its own variable of that name. The two variables named SelectCell have nothing to do with each other, both are local variables.

    Try this:

    Function StartingCell() As Range
    Set StartingCell=Application.InputBox(Prompt:="Enter Starting Cell" ,Type:=8)
    End Function

    Sub Transfer()
    Dim SelectCell As Range
    Dim Range1 As Range
    Sheets("FinalSort").Select
    Set SelectCell = StartingCell
    Set Range1 = Range(SelectCell, SelectCell.Offset(7, 4))
    ...

    Note that I put the code to activate another sheet in the Transfer procedure. I don't think it's a good idea to switch sheets in the function.

  5. #5
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Cape Cod
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Passing Object Variable to a Second Macro (Excel2000)

    I made the changes and still get the same error message

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Passing Object Variable to a Second Macro (Excel2000)

    It worked OK when I tested it. Can you post (a stripped down version of) your workbook? (Remove or replace sensitive data)

  7. #7
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Cape Cod
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Passing Object Variable to a Second Macro (Excel2000)

    Hans, enclosed sheet1--partial code

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Passing Object Variable to a Second Macro (Excel2000)

    Your attachment didn't make it. Make sure that
    a) The file is less than 100 KB in size (zip it if necessary).
    [img]/forums/images/smilies/cool.gif[/img] You specify the attachment just before clicking Post It. If you preview your reply, the attachment box will be cleared.

  9. #9
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Cape Cod
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Passing Object Variable to a Second Macro (Excel2000)

    Hello Hans, I seemed to have solved my problem by including (SelectCell) in the function name

    Function StartingCell(SelectCell) as Range
    and I used Call StartingCell(SelectCell) just before Set Range1=

    However after the Function and Sub have run there is another sub --- Sub FinalCopy().How can I pass the value of SelectCell to this sub without entering the cell in an InputBox?

    PaulB

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Passing Object Variable to a Second Macro (Excel2000)

    You could make SelectCell a module-level variable, i.e. put the declaration

    Dim SelectCell As Range

    at the top of the module, before the Subs and Functions.

    Once you set SelectCell, it will remain available during that session.

  11. #11
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Cape Cod
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Passing Object Variable to a Second Macro (Excel2000)

    When I type Dim SelectCell As Range above the Sub() and hit enter the line ends up above the line after End Sub and I get the error message ""Only comments allowed after Sub End."

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Passing Object Variable to a Second Macro (Excel2000)

    You should put that line near the top of the module, above *all* Subs and Functions.

  13. #13
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Cape Cod
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Passing Object Variable to a Second Macro (Excel2000)

    Hans, thanks to your help my project is moving right along. I am thinking of ways to improve on it. For example instead of using the Application.ImputBox to get a cell address is it possible to just click the cell? Also to draw a line under a certain row. Is this UserForm andn ListBox time?

    Paul B

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Passing Object Variable to a Second Macro (Excel2000)

    If you want to use the active cell instead of letting the user select a cell in the macro, you can use ActiveCell.

    You can set borders to one or more cells in code - type Borders in the Visual Basic Editor and press F1.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •