Using a defined variable from outside the current function

Question

I'm having trouble getting a function to recognise defined variables from the sub that runs the function. Essentially what I'm trying to do is have a macro for each data type where all that is defined is 6 different times:

Sub F3DataSort()

zone1 = "00:26:00"
zone2 = "00:32:00"
zone3 = "00:38:00"
zone4 = "00:44:00"
zone5 = "00:50:00"
zone6 = "00:56:00"

ColumnSort

DrawGraph

End Sub

There is also a F1datasort, F2datasort, etc up to 4. Columnsort needs to use the values defined here to fill in cells with the appropriate formulas. Drawgraph can be ignored for now.

The important part of columnsort is as follows:

Function ColumnSort()
 'Peak value
    Range("E2").Value = "=MAX(RC[-1]:R[9993]C[-1])"
    'Peak time
    Range("F2").Value = "=INDEX(RC[-3]:R[9998]C[-3],MATCH(RC[-1],RC[-2]:R[9998]C[-2],0))"
    'Milestone 1
    Range("F5").Value = zone1
    'Milestone 1 temp formula
    Range("E5").Value = "=INDEX(R[-3]C[-1]:R[9995]C[-1],MATCH(RC[1],R[-3]C[-2]:R[9995]C[-2],1))"
    'Milestone 2
    Range("F6").Value = zone2
    'Milestone 2 temp formula
    Range("E6").Value = "=INDEX(R[-4]C[-1]:R[9994]C[-1],MATCH(RC[1],R[-4]C[-2]:R[9994]C[-2],1))"
    'Milestone 3
    Range("F7").Value = zone3
    'Milestone 3 temp formula
    Range("E7").Value = "=INDEX(R[-5]C[-1]:R[9993]C[-1],MATCH(RC[1],R[-5]C[-2]:R[9993]C[-2],1))"
    'Milestone 4
    Range("F8").Value = zone4
    'Milestone 4 temp formula
    Range("E8").Value = "=INDEX(R[-6]C[-1]:R[9992]C[-1],MATCH(RC[1],R[-6]C[-2]:R[9992]C[-2],1))"
    'Milestone 5
    Range("F9").Value = zone5
    'Milestone 5 temp formula
    Range("E9").Value = "=INDEX(R[-7]C[-1]:R[9991]C[-1],MATCH(RC[1],R[-7]C[-2]:R[9991]C[-2],1))"
    'Milestone 6
    Range("F10").Value = zone6
    'Milestone 6 temp formula
    Range("E10").Value = "=INDEX(R[-8]C[-1]:R[9990]C[-1],MATCH(RC[1],R[-8]C[-2]:R[9990]C[-2],1))"

End Function

This just tells it to look up what the data reading is at a particular time defined by zone1, zone2, etc. This is the kind of thing I'm going for, I'm just trying to simplify it so that as much of the code as possible need only be written once.

https://www.dropbox.com/s/13agn2zihmxzwbu/example%20for%20code.jpg

As you can probably tell, I am a total beginner for this and just winging it as I go along. I've tried searching for solutions to this but had no luck finding a situation like mine.

use public variables outside the sub: http://stackoverflow.com/questions/2722146/how-do-i-declare-a-global-variable-in-vba — Dmitry Pavliv, Mar 17 '14 at 15:11
You should not use a function to do that, you should use a sub. In functions you should never use variables defined outside the function, it is a very bad programming practice. Use a sub, and, if you really need it, declare those variables as public outside the sub, or just transmit the variables using sub arguments (the best way). — CRondao, Mar 17 '14 at 15:51

Rbit · Accepted Answer · 2014-03-17T16:37:58.160

0

Try something like this...

Sub f3datasort()

Dim z1 As Date
Dim z2 As Date
Dim z3 As Date
Dim z4 As Date
Dim z5 As Date

z1 = "00:26:00"
z2 = "00:32:00"
z3 = "00:38:00"
z4 = "00:44:00"
z5 = "00:50:00"

Columnsort z1, z2, z3, z4, z5

End Sub

Sub Columnsort(ByVal z1 As Date, ByVal z2 As Date, ByVal z3 As Date, ByVal z4 As Date, ByVal z5 As Date)
     Range("E2").Value = "=MAX(RC[-1]:R[9993]C[-1])"
    'Peak time
    Range("F2").Value = "=INDEX(RC[-3]:R[9998]C[-3],MATCH(RC[-1],RC[-2]:R[9998]C[-2],0))"
    'Milestone 1
    Range("F5").Value = zone1
    'Milestone 1 temp formula
    Range("E5").Value = "=INDEX(R[-3]C[-1]:R[9995]C[-1],MATCH(RC[1],R[-3]C[-2]:R[9995]C[-2],1))"
    'Milestone 2
    Range("F6").Value = zone2
    'Milestone 2 temp formula
    Range("E6").Value = "=INDEX(R[-4]C[-1]:R[9994]C[-1],MATCH(RC[1],R[-4]C[-2]:R[9994]C[-2],1))"
    'Milestone 3
    Range("F7").Value = zone3
    'Milestone 3 temp formula
    Range("E7").Value = "=INDEX(R[-5]C[-1]:R[9993]C[-1],MATCH(RC[1],R[-5]C[-2]:R[9993]C[-2],1))"
    'Milestone 4
    Range("F8").Value = zone4
    'Milestone 4 temp formula
    Range("E8").Value = "=INDEX(R[-6]C[-1]:R[9992]C[-1],MATCH(RC[1],R[-6]C[-2]:R[9992]C[-2],1))"
    'Milestone 5
    Range("F9").Value = zone5
    'Milestone 5 temp formula
    Range("E9").Value = "=INDEX(R[-7]C[-1]:R[9991]C[-1],MATCH(RC[1],R[-7]C[-2]:R[9991]C[-2],1))"
    'Milestone 6
    Range("F10").Value = zone6
    'Milestone 6 temp formula
    Range("E10").Value = "=INDEX"
End Sub

edited Mar 17 '14 at 16:37

answered Mar 17 '14 at 15:21

Rbit

231
1
7

VBA doesn't allow variable names starting from underscore: `_zone1` – Dmitry Pavliv Mar 17 '14 at 15:27
you can call the variable name anything you like: the principle is the same: either set parameters in the function call, or set global parameters (my second example) and use them in your function. – Rbit Mar 17 '14 at 15:29
I can't seem to use the phrase dim zone1 = "00:26:00" without getting a compile error (expected end of statement). Do you suggest F3datasort should be a function rather than a sub? I only need it to fill in cells and not return any figures. – tantalus_blank Mar 17 '14 at 15:45
try "dim zone1 as datetime" then use 'zone1 = "00:26:00"' – Rbit Mar 17 '14 at 15:51
Thanks for the help - unfortunately I still can't seem to figure this out (I've no experience of using classes). Where you have "Function F3DataSort" in your first comment, is that a typo or are you suggesting I change it to a function? My sub is called F3DataSort and the function is columnsort - each of the zone times will change with the sub, but I am trying to get them running the same code to make it easier if I need to make changes. The macros will be stored on the personal sheet and I'm using shortcuts to activate a macro depending on the set of zone times needed. – tantalus_blank Mar 17 '14 at 16:08
oops - my mistake! According to your code it should be Sub F3DataSort. Sub is a bit of code that does not return a value, function is a bit of code that returns a value. I recommend you look up the syntax of calling a sub with parameters and then pass the zones in as parameters to your sub. – Rbit Mar 17 '14 at 16:14
ok I've changed my answer - your code should look (and work) pretty much like that although I may have missed a couple of your lines of code off the end. Notice how I defined and called the Columnsort sub – Rbit Mar 17 '14 at 16:38
Ha! it worked! Thanks for the help Rbit - really appreciated. I would upvote but it won't let me with <15 reputation. – tantalus_blank Mar 17 '14 at 17:14

Using a defined variable from outside the current function

1 Answers1