Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pass Info Between Macros (Re-Visited)
Hello, The last time I posted this question the Suggestion didn't work and I
didn't get much more help so I'm re-asking for help. I have a Dim that us the same in multiple macros shown below Dim RowID RowID = ActiveCell.Row How do I use the same RowID in the Original Sub and when it's done running it passes the same value of RowID to the next sub? Originating Sub - InsertSection() 2nd Sub - SetBorders() 3rd Sub - InsertFormulas() 4th Sub - AddText() 5th Sub - SetColors() 6th Sub - ConditionalFormatting() 7th Sub - SetNames() There's more but you get the point. All of them start off with the very same Dim shown above and they all end with the same Range Selection Range("A" & RowID).Select Any help would be awesome. Thanks in Advance. Rob |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pass Info Between Macros (Re-Visited)
First you intimated that all the subs would use the one rowID variable. So
the responder suggested using a public variable. You responded I didn't know you could do that?? What about the Subs that need to use a different value for RowID? So why ask the same question. For passing the value, you got a good anwer. At the top of the module do Public RowId as Long outside any procedure. Then in any procedure that needs to use this common value of rowID or needs to update a new value for the common rowID variable, do not declare a local version of rowID. In any sub that you want to determine its own rowID and will not need to pass to any other subs, then declare a local variable Sub NeedsUniqueRowID() Dim rowID as Long End Sub Also, in any of the procedures that don't need the common rowID, it might be easier to use a different variable name. Hope that clears it up for you. Look in Excel VBA help at SCOPE of variables. -- Regards, Tom Ogilvy "Rob" wrote in message ... Hello, The last time I posted this question the Suggestion didn't work and I didn't get much more help so I'm re-asking for help. I have a Dim that us the same in multiple macros shown below Dim RowID RowID = ActiveCell.Row How do I use the same RowID in the Original Sub and when it's done running it passes the same value of RowID to the next sub? Originating Sub - InsertSection() 2nd Sub - SetBorders() 3rd Sub - InsertFormulas() 4th Sub - AddText() 5th Sub - SetColors() 6th Sub - ConditionalFormatting() 7th Sub - SetNames() There's more but you get the point. All of them start off with the very same Dim shown above and they all end with the same Range Selection Range("A" & RowID).Select Any help would be awesome. Thanks in Advance. Rob |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pass Info Between Macros (Re-Visited)
Rob wrote: Hello, The last time I posted this question the Suggestion didn't work and I didn't get much more help so I'm re-asking for help. I have a Dim that us the same in multiple macros shown below Dim RowID RowID = ActiveCell.Row How do I use the same RowID in the Original Sub and when it's done running it passes the same value of RowID to the next sub? Originating Sub - InsertSection() 2nd Sub - SetBorders() 3rd Sub - InsertFormulas() 4th Sub - AddText() 5th Sub - SetColors() 6th Sub - ConditionalFormatting() 7th Sub - SetNames() There's more but you get the point. All of them start off with the very same Dim shown above and they all end with the same Range Selection Range("A" & RowID).Select Any help would be awesome. Thanks in Advance. Rob |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pass Info Between Macros (Re-Visited)
Rob wrote: Hello, The last time I posted this question the Suggestion didn't work and I didn't get much more help so I'm re-asking for help. I have a Dim that us the same in multiple macros shown below Dim RowID RowID = ActiveCell.Row How do I use the same RowID in the Original Sub and when it's done running it passes the same value of RowID to the next sub? Originating Sub - InsertSection() 2nd Sub - SetBorders() 3rd Sub - InsertFormulas() 4th Sub - AddText() 5th Sub - SetColors() 6th Sub - ConditionalFormatting() 7th Sub - SetNames() There's more but you get the point. All of them start off with the very same Dim shown above and they all end with the same Range Selection Range("A" & RowID).Select Any help would be awesome. Thanks in Advance. Rob Hi Rob, It appears that you are wanting to use a variable to handle the RowID which will be based on the active cell and will not be constant. If this is the case, then you could assign the row number to the variable in the first procedure and pass that value along to the next procedure. I apologize in advance if I have misunderstood your question but here's what I would do . . . Sub getRowNumber dim myRow as long myRow = ActiveCell.Row 'now we can send this value along to the next procedure 'by using the variable as an argument in the calling procedure Call nextProcedure(myRow) End Sub Sub nextProcedure(myRow) MsgBox myRow 'just to visually see the row number for validation Range("A",myRow).Select End Sub Does this help or have I misunderstood the question? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pass Info Between Macros (Re-Visited)
MrScience,
Sorry for the long wait for a responce from your post, I have been very busy. Yes you understood my plight very well and your answer is exactly what I was hoping for. And it works perfectly too!! I kept getting dod gamn errors so I just gave up, I am so glad I finally saw your responce. Thanks Sooo Much! Rob "MrScience" wrote: Rob wrote: Hello, The last time I posted this question the Suggestion didn't work and I didn't get much more help so I'm re-asking for help. I have a Dim that us the same in multiple macros shown below Dim RowID RowID = ActiveCell.Row How do I use the same RowID in the Original Sub and when it's done running it passes the same value of RowID to the next sub? Originating Sub - InsertSection() 2nd Sub - SetBorders() 3rd Sub - InsertFormulas() 4th Sub - AddText() 5th Sub - SetColors() 6th Sub - ConditionalFormatting() 7th Sub - SetNames() There's more but you get the point. All of them start off with the very same Dim shown above and they all end with the same Range Selection Range("A" & RowID).Select Any help would be awesome. Thanks in Advance. Rob Hi Rob, It appears that you are wanting to use a variable to handle the RowID which will be based on the active cell and will not be constant. If this is the case, then you could assign the row number to the variable in the first procedure and pass that value along to the next procedure. I apologize in advance if I have misunderstood your question but here's what I would do . . . Sub getRowNumber dim myRow as long myRow = ActiveCell.Row 'now we can send this value along to the next procedure 'by using the variable as an argument in the calling procedure Call nextProcedure(myRow) End Sub Sub nextProcedure(myRow) MsgBox myRow 'just to visually see the row number for validation Range("A",myRow).Select End Sub Does this help or have I misunderstood the question? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How days since last visited | Excel Worksheet Functions | |||
How do I pass info from excel to a hyperlinked explorer page | Excel Programming | |||
Using Multiple User Forms to pass info from one to the other | Excel Programming | |||
Pass multiple parameters through macros | Excel Programming | |||
How to pass variable value between macros | Excel Programming |