PDA

View Full Version : Solved: Storing Values/Data beyond a subroutine



Sock
07-07-2013, 11:09 PM
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!

joms
07-08-2013, 01:28 AM
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.

:)

snb
07-08-2013, 03:39 AM
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

Sock
07-08-2013, 04:37 PM
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.

joms
07-08-2013, 05:53 PM
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.

Sock
07-08-2013, 06:21 PM
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.

Sock
07-08-2013, 10:29 PM
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!

joms
07-09-2013, 12:47 AM
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

snb
07-09-2013, 12:52 AM
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

joms
07-09-2013, 02:13 AM
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!!

Sock
07-10-2013, 05:43 PM
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 :)