Hi SpreedsheetCrusader
First a very quick point – I think you have a typo in post #1 Code: This line is rubbish
Code:
Dim lastrowlastrow = Cells(3, 6).SpecialCells(xlCellTypeLastCell).Row
_ ...............
_ I am not sure exactly what you are trying to do, or maybe you are trying to do something along the lines of what I was here:
Store Array created by VBA macro “Internally” for use in same VBA macro by next run..or..- [SOLVED]
_ Maybe like me you are trying to understand some basic ideas...
_ But Possibly you are confusing two different things ( or I am ).
_ 1) What I think you want to do:
_ 1a ) Consider this.
Copy the following to any Normal or sheet Module in File
Code:
[color=blue]Option[/color] [color=blue]Explicit[/color]
[color=blue]Dim[/color] rngA1 [color=blue]As[/color] Range [color=darkgreen]'Dim or Private I think works the same to make the variable available in this module[/color]
[color=blue]Private[/color] lr [color=blue]As[/color] [color=blue]Long[/color], cnt [color=blue]As[/color] [color=blue]Long[/color]
[color=darkgreen]'[/color]
[color=blue]Sub[/color] SubAnyOneOfmanySubroutines()
[color=blue]If[/color] lr = 0 [color=blue]Then[/color] [color=blue]Call[/color] SetLetStuff
[color=blue]Let[/color] cnt = cnt + 1 [color=darkgreen]'A count for how many times you ran the code[/color]
Let rngA1.Value = "Run Number " & cnt & ". Last cell in column A of Third Sheet with something in it is " & lr & "" [color=darkgreen]'you can use this code line in any [color=blue]Sub[/color], without Defining rngA1 as long as SetLetStuff() was run once[/color]
[color=blue]End[/color] [color=blue]Sub[/color]
[color=darkgreen]'[/color]
'
'
[color=blue]Sub[/color] Set[color=blue]Let[/color]Stuff()
[color=blue]Set[/color] rngA1 = ThisWorkbook.Worksheets.Item(3).Range("A1") [color=darkgreen]'Set RngA1 to first cell in Third Sheet[/color]
Let lr = ThisWorkbook.Worksheets.Item(1).Cells(Rows.Count, 1).End(xlUp).Row [color=darkgreen]'Let lr = last cell in column A with somethiong in it[/color]
[color=blue]End[/color] Sub
Run the
Sub SubAnyOneOfmanySubroutines() a few times in debug ( F8 ) mode and see what happens.
You will see it only calls
Sub SetLetStuff() the first time.
Once
Sub SetLetStuff() has run once you can use lr and rngA1 in any other Procedures
You will also see that if you look at the first cell in the Third Tab (Third sheet) and run the code, then the value in the first cell is updated.
That is basically I think all there is to using variables in different Procedures without repeating the
Dim ,
Let , and
Set stuff. So in your case for what I think you want , you would just change the lr or rngA1 in the
Sub SetLetStuff(), save the file , close it, reopen , and start again and there you have the change you want without changing all your procedures using lr and rngA1
_ 1b) You could do it a bit better, I think.
Probably having a code in ThisWorkbook Module of the
Private Sub Workbook_Open() type which did the
Dim and
Let Set stuff. But i tried and could not figure out how!
_ ..................
_ 2) ByVal ByRef Stuff ( What i think you do not want to do particularly )
This is Passing Variables from one Procedure to another and is something else (. It does not sound like what you are interested in here.) It concerns how Variables from a calling Procedure are “taken” into the Procedure it calls.
_ No Dimensioning is done outside any Procedure . After the calling procedure stops all variables die. - So I do not think that is what you want....But very briefly, in words,
_
ByVal which is the default, means use for each
Sub Procedure taking that variable at the
"Signature Line"** a copy of that variable is made, with the value that is in it, in it . In each Procedure it is a different variable which lives and dies for the lifetime of the Procedure. It will not change the value of the variable with the same name given in the original Procedure. The Copy Variable “dies” when the called Procedur ends.
_
ByRef “points” or “references to a specific Variable” or carries across that actually variable as it were at the
"Signature Line"**. Although I have never seen it so explained, somehow when the called procedure stops, The variable does not die as in the
ByVal case, but somehow the actual variable ( with the original, or, if modified, the modified value, in it ) “drops back” into the calling Procedure
####
Maybe these typical Example codes will help to explain this a bit. Copy all three to any Module and run the first two Codes
Code:
[color=blue]Option[/color] [color=blue]Explicit[/color]
[color=blue]Sub[/color] CallByRefByValCheck()
[color=blue]Dim[/color] varByRef [color=blue]As[/color] Long: [color=blue]Let[/color] varByRef = 1
[color=blue]Dim[/color] varByVal [color=blue]As[/color] Long: [color=blue]Let[/color] varByVal = 2
MsgBox Prompt:="In the calling Sub ( CallByRefByValCheck() ) " & vbLf & "value in varByRef is " & varByRef & vbLf & "value in varByVal is " & varByVal & vbLf & ". I am now taking value 2 and variable varByRef " & vbLf & "across into another Sub " & vbCr & ""
[color=blue]Call[/color] ByRefByValCheck(varByRef, varByVal)
MsgBox Prompt:="I came back, and now " & vbLf & "value in varByRef is " & varByRef & vbLf & "value in varByVal is still " & varByVal
[color=blue]End[/color] [color=blue]Sub[/color]
[color=blue]Sub[/color] WhatIsvarByValandvarByRefNow()
MsgBox Prompt:="varByRef is " & varByRef & vbLf & "VarByVal is " & varByVal
[color=blue]End[/color] [color=blue]Sub[/color]
[color=darkgreen]'[/color]
'
'
Sub ByRefByValCheck(ByRef Rf [color=blue]As[/color] [color=blue]Long[/color], ByVal Vl [color=blue]As[/color] [color=blue]Long[/color]) 'This is the [B][COLOR="#800080"]"Signature Line"**[/COLOR][/B] Rf holds varByRef , Vl holds a copy of varByVal
[color=blue]Let[/color] Rf = 10 [color=darkgreen]'The variable "in" Rf ( which is the actual varByRef from calling [color=blue]Sub[/color] ) is changed[/color]
[color=blue]Let[/color] Vl = 20 [color=darkgreen]'The variable "in" Vl ( which is just a copy of varByVal, not the original ) is changed[/color]
[color=blue]End[/color] Sub
_ ...............................................
I am learning myself and there may be more to it.
In particular I would be very interested if a Profi out there could do the bit I tried in _1b). After a few hours of Googling and Experimenting i could not see how to do that.
Hope that helps a bit...
Alan
#### P.s. Just in the unlikely case that you did follow my last explanation.... - A very important learning point here is, I think, that using a procedure in such a
ByRef way gives you a way to sort of use it like a
Function , but better, as you can return as many Variables as you wish , that is to say as many as you have defined
ByRef in the
"Signature Line"**. ( A
Function I believe only returns in typical use one value (
unless it returns Variant Type with an Array() in it) VBA incidentally does often not like “taking across ” collections ( Arrays ) so there may be an interesting connection here that you find an Array() will only be taken ByRef
Store Array created by VBA macro “Internally” for use in same VBA macro by next run..or..- [SOLVED] - Page 3
....... )
)
