View Full Version : Solved: Storing Values/Data beyond a subroutine
Hi everyone,
Right now I have a macro that grabs cells references and various detail about the cells to perform calculations. The macro works fine, however each time I want to run the macro, I have to re-select the cells and the descriptive data.
Ideally I'd like to just add the cell reference and its descriptive data through some other means and then run the macro. This would be a great time-saver.
At this moment I am at a loss to how to do this. Does it have to do with classes, or something else?
Please let me know if any clarification is needed.
Again, thank you all so much for all your help. I would be completely lost without this forum!
make a Public Sub to select the cells and the descriptive data and just call the public sub, anywhere in your code to run it.
:)
Please, post your code here and tell us to what cells it applies.
Kenneth Hobs
07-08-2013, 05:40 AM
Dim acRange As Range
Sub ResetACrange()
Set acRange = Nothing
End Sub
Sub ActOnACRange()
If acRange Is Nothing Then Set acRange = Selection
acRange.Value = "Ken"
End Sub
I apologize for my slow response. I've been trying to access this site throughout the day with no luck :(
make a Public Sub to select the cells and the descriptive data and just call the public sub, anywhere in your code to run it.
:)
Thanks for the response, joms. My question about this is, should I dim the variables as 'static'? This way I can select the cells and frolic around excel and other tasks before running the rest of the code with all the calculations and such? To my understanding, if I ran a public sub as-is, the variables' lives end when the sub is complete, right? Or is there something I'm misunderstanding?
Please, post your code here and tell us to what cells it applies.
Hi, snb. Thanks for the reply! Actually, it doesn't apply to specific cells in a worksheet, rather it applies to any number of selected cells. In this case, the cells are defined as a range as 'x(y)', where 'y' is 1 to any number of selected cells. The code iterates values through these cells.
I will try to whip up a simplified version for you if I find the time (I'm still very slow at this), as the rest of my code is hundreds of lines long. I'll see about extracting just the relevant bits, of course :)
Code
Thanks, Ken! I think I would pose the same question to you as I did with joms, however. This does raise a good point that I should clarify, however.
Ideally, what I want to do is store the cell reference, along with descriptive data about the cell in some multi-column list (maybe a listbox?) wherein I can recall the data in a larger procedure later.
To my understanding, if I ran a public sub as-is, the variables' lives end when the sub is complete, right?
Hi Sock, If you use a public sub, then you should declare the variable used in the public sub as public as well.
like:
option explicit
Public myGlobVar as String 'place this at the very top of your code
the myGlobVar variable every time the public sub is called anywhere in your code it will have the value.
To my understanding, if I ran a public sub as-is, the variables' lives end when the sub is complete, right?
Hi Sock, If you use a public sub, then you should declare the variable used in the public sub as public as well.
like:
option explicit
Public myGlobVar as String 'place this at the very top of your code
the myGlobVar variable every time the public sub is called anywhere in your code it will have the value.
Hm, I think I'm misunderstanding something, or I've miscommunicated. I've used public declarations before, but I'm uncertain how I can have those "keep" my cells and the descriptive data through multiple runs of the main sub. Because if I run the main sub and call these public subs, then each time I run the main sub I'd have to re-enter the cells and their data again, right?
For example, would this work with, say, a button called "Add Cell" where I click the button and it allows me to add a cell along with the data, continue work in excel, go for lunch, work in excel, click the button and add another cell and its data, watch a movie, work in excel, etc. and then later click a different button for 'run main sub' which uses those cell references (and data)? As you can see, I'm a little confused... :( I'm sorry.
Edit: This is what I mean:
Public basiccell As Range
Public Sub addcell
set basiccell = "A1" 'Note 1: It would be some selection of the user and this is an array
'to account for multiple cells added, so the code would be more complicated
'Note 2: More information on the cell would be added as well
End Sub
Sub MainProc
Codecodecode
basiccell = numbers, calculations, etc
'This would involve loops and other code all based on the cells that were added
End Sub
If I have to run the main sub first and call the adding of cells, then every time I wanted to run the main sub, I'd have to re-enter the cells. However, I want those cells to stay put with their data; I don't want to re-enter them every time I run the sub. Ideally I'd like to add them to a list for visibility purposes.
Edit 2: Where are my manners? Huge huge thank you to you! :)
Kenneth Hobs
07-08-2013, 07:02 PM
What is it you are trying to achieve? Post an example workbook.
You can use an array as a public variable.
mikerickson
07-08-2013, 09:17 PM
It looks like rather than a sub, you want a Function that returns a range object.
What is it you are trying to achieve? Post an example workbook.
You can use an array as a public variable.
I've attached a quick workbook example that is incredibly simplified.
The buttons have nothing going on. It's just for the example purposes. I have notes saying what I'm trying to do.
Programming the buttons and such isn't the problem. I'm just trying to be able to make it so I can click "add cell" and do this without initializing the main sub ('run algorithm').
This way changes to the algorithm and its related options (a much bigger operation) can be made and then re-run without having to reselct the cells and their parameters each time one wants to run the algorithm.
I really hope this makes sense. I'm starting to struggle with how to describe this. I can try to work on some dummy code and data later that better demonstrates it, however, work beckons, as well as sleep (damn).
Thanks much for your interest! :)
It looks like rather than a sub, you want a Function that returns a range object.
I know this is a very 'noob' question, but in what way does a function differ from a sub? I know functions "return a value", but I'm curious about the scope and persistence in this case. Thank you for your input!
Hi Sock, basically you just want to reuse the cell reference and the descriptive data.
Make life simple and not complicated, but I don't know whether this one will work for you.
Here's my idea, put in another worksheet all the cell reference and whatever data that you need.
Make a new worksheet, hide it using vba.
Make a new procedure, then call the values on the hidden worksheet.
Here's a basic code to start:
Worksheets("sheet4").Visible = False
Worksheets("Sheet1").Range("A1").Value = Worksheets("Sheet4").Range("A1").VALUE
If you have selected some cells, you can store that selection using the VBA code:
Sub M_snb()
selection.name="snb_001"
end sub
Now it's a 'named range' that will be stored in your workbook and can be reused ad nauseam.
E.g.
Sub tst()
[snb_001].interior.colorindex=5
End Sub
If you have selected some cells, you can store that selection using the VBA code:
Sub M_snb()
selection.name="snb_001"
end sub
Now it's a 'named range' that will be stored in your workbook and can be reused ad nauseam.
E.g.
Sub tst()
[snb_001].interior.colorindex=5
End Sub
hi snb, nice code storing array on brackets. it reminds me of something, I was writing a code somewhat complex and a friend of mine told me to use this method.
Store the values in array and enclosed them on brackets. Cool!!
Hi Sock, basically you just want to reuse the cell reference and the descriptive data.
Make life simple and not complicated, but I don't know whether this one will work for you.
Here's my idea, put in another worksheet all the cell reference and whatever data that you need.
Make a new worksheet, hide it using vba.
Make a new procedure, then call the values on the hidden worksheet.
Here's a basic code to start:
Worksheets("sheet4").Visible = False
Worksheets("Sheet1").Range("A1").Value = Worksheets("Sheet4").Range("A1").VALUE
Hey, thank you! I think I'll use this as my method. It sounds fairly simple to implement. I suppose this follows the "KISS" methodology, right? LOL
If you have selected some cells, you can store that selection using the VBA code:
Sub M_snb()
selection.name="snb_001"
end sub
Now it's a 'named range' that will be stored in your workbook and can be reused ad nauseam.
E.g.
Sub tst()
[snb_001].interior.colorindex=5
End Sub
Thank you very much, snb. This was a great learning experience for me. I had no idea what the brackets meant, but upon looking it up, it's short for the "evaluate" function, I think? Very, very cool stuff. I think I have to hone my skills a little more to implement a solution like this, but I am very grateful for your input on this.
Thanks again to everyone, I think this leaves me with a good amount to go off of :)
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.