How to "store" a value for another macro

[wbstadeli]

Hi all,

Im trying to store a value that i derive when i run my Sub Activate_And_Dropdown_WO_Certification_ComboBox(), that gets a number from the right side of the "clicked on" shape name after the "_". This is working good, and here is the code im using for that. The "i" is the value i would like stored. However i have another macro that i need to use this derived value, and im not sure how to store/save this in my first macro so i can use it in my second. Also i dont want to run the second macro inside of the first one, it needs to be ran at a separate time. Any suggestions?

Sub Activate_And_Dropdown_WO_Certification_ComboBox()

Dim pos As Integer
Dim strlen As Integer
Dim i As String

Set Sel_Shape = ActiveSheet.Shapes(Application.Caller)

pos = InStrRev(Sel_Shape.Name, "_")
strlen = Len(Sel_Shape.Name)
i = Right(Sel_Shape.Name, strlen - pos) 'This "i" is the value i want to store'

 

[Joe4]

Also i dont want to run the second macro inside of the first one, it needs to be ran at a separate time

When you say at a separate time, do you mean during the same Excel session though?
If so, you can use Global variables (see: How do I declare a global variable in VBA?

If you want to calculate it, but then maybe close your workbook and re-open it later and still access the value calculated in an earlier session, you wll need to store the value in a worksheet cell somewhere.

 

[wbstadeli]

When you say at a separate time, do you mean during the same Excel session though?
If so, you can use Global variables (see: How do I declare a global variable in VBA?

If you want to calculate it, but then maybe close your workbook and re-open it later and still access the value calculated in an earlier session, you wll need to store the value in a worksheet cell somewhere.

Thanks for the response Joe, yes only needed in the same session. The link gives me some great info but im not sure how to get the "i" to be stored in the "public". See my code, where am i going wrong? I call this function Item_Number from the first macro, it runs the funtion, gets the correct "i" value, but when the funtion is ended the "i" is not stored. I tested this by "msgbox i" in my macro after the function was ran.

Public i As String 
Function Item_Number()

Dim pos As Integer
Dim strlen As Integer
Dim i As String

Set Sel_Shape = ActiveSheet.Shapes(Application.Caller)

pos = InStrRev(Sel_Shape.Name, "_")
strlen = Len(Sel_Shape.Name)
i = Right(Sel_Shape.Name, strlen - pos)

End Function

 

[Joe4]

You have it declared twice:

Public i As String 
Function Item_Number()

Dim pos As Integer
Dim strlen As Integer
Dim i As String

Get rid of the second declaration, leaving only the Public one.

 

[wbstadeli]

Perfect, thanks!!

 

[Joe4]

You are welcome.

 

[wbstadeli]

I have another question on this topic, I am creating new rows with a macro, and assigning formulas into certain cells of the new row. I have named cells with different "i" values at the end so that i dont get duplicate names. However im not sure how to specify a variable in a formula, is there a way to do this? Basically this used to work for my single range, now i want to be able to dynamically add as many as needed ranges, so i can't hard code a exact named, it needs to have the name plus the variable (i).. See code:

......
.Rows(newrow).Columns(7).Resize(, 1).Select
            Set Per_Piece = .Rows(newrow).Columns(7)
            Set Per_Lot = .Rows(newrow).Columns(9)
            Set Totals = .Rows(newrow).Columns(11)
            Totals.Value = "=iferror(if(" & Per_Lot.Address & "=""""," & Per_Piece.Address & "*sum(Total_Qty_Shipped_)," & Per_Lot.Address & "),0)" 
'This last line is the line im having trouble with, in the code it is hardcoded to the name without the variable, i need it to be the name with the 
variable so "Total_Qty_Shipped_ & i" with "i" being the value i set with the function. Is this possible?

 

[Joe4]

I have another question on this topic, I am creating new rows with a macro, and assigning formulas into certain cells of the new row. I have named cells with different "i" values at the end so that i dont get duplicate names. However im not sure how to specify a variable in a formula, is there a way to do this? Basically this used to work for my single range, now i want to be able to dynamically add as many as needed ranges, so i can't hard code a exact named, it needs to have the name plus the variable (i).. See code:

That is a distinctly different question that your original question, and as such, should be posted to its own, new thread.

 

[wbstadeli]

will do, thanks!