Input Values Instead of Hard Coded Values
 

Hi Everybody,

I have a Macro that Works Well. The Only Problem is that ALL the
Values ( Data ) I Use are Hard Coded in the Macro.
What would I Need to do ( and How would I go About it ) if I Wanted
the Macro to Pick Up the Values ( Data ) from a Table or Something
Instead of having them Hard Coded in the Macro. The Hard Coded Values
( Data ) Used are NOT in the Workbook and will Change Constantly. I
Really want to be Able to Input the Values Somewhere and then Run the
Macro as Often as Necessary.
Below are 2 Examples of Working Functions :-


Function Difference1()
Difference1 = False
If _
A = 1 And A <= 3 And _
B = 2 And B <= 7 And _
C = 3 And C <= 10 And _
D = 5 And D <= 13 And _
E = 12 And E <= 17 And _
F = 16 And F <= 20 Then _
Difference1 = True
End Function

Function Difference2()
Difference2 = False
If _
B - A <= 5 And _
C - B <= 7 And _
D - C <= 8 And _
E - D <= 10 And _
F - E <= 12 Then _
Difference2 = True
End Function

Any Help will be Greatly Appreciated.
All the Best
Paul
Windows XP Home - XL2002

Input Values Instead of Hard Coded Values
 

Here is one way. Put the values into A1:L1 on a worksheet named data. Then
use


Dim aryValues
Function Difference1()
aryValues = Worksheets("data").Range("A1:L1")
Difference1 = False
If _
a = aryValues(1, 1) And a <= aryValues(1, 3) And _
B = aryValues(1, 2) And B <= aryValues(1, 5) And _
C = aryValues(1, 3) And C <= aryValues(1, 6) And _
D = aryValues(1, 4) And D <= aryValues(1, 8) And _
E = aryValues(1, 7) And E <= aryValues(1, 10) And _
F = aryValues(1, 9) And F <= aryValues(1, 11) Then _
Difference1 = True
End Function

Function Difference2()
aryValues = Worksheets("data").Range("A1:L1")
Difference2 = False
If _
B - a <= aryValues(1, 4) And _
C - B <= aryValues(1, 5) And _
D - C <= aryValues(1, 12) And _
E - D <= aryValues(1, 6) And _
F - E <= aryValues(1, 7) Then _
Difference2 = True
End Function


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Paul Black" wrote in message
...
Hi Everybody,

I have a Macro that Works Well. The Only Problem is that ALL the
Values ( Data ) I Use are Hard Coded in the Macro.
What would I Need to do ( and How would I go About it ) if I Wanted
the Macro to Pick Up the Values ( Data ) from a Table or Something
Instead of having them Hard Coded in the Macro. The Hard Coded Values
( Data ) Used are NOT in the Workbook and will Change Constantly. I
Really want to be Able to Input the Values Somewhere and then Run the
Macro as Often as Necessary.
Below are 2 Examples of Working Functions :-


Function Difference1()
Difference1 = False
If _
A = 1 And A <= 3 And _
B = 2 And B <= 7 And _
C = 3 And C <= 10 And _
D = 5 And D <= 13 And _
E = 12 And E <= 17 And _
F = 16 And F <= 20 Then _
Difference1 = True
End Function

Function Difference2()
Difference2 = False
If _
B - A <= 5 And _
C - B <= 7 And _
D - C <= 8 And _
E - D <= 10 And _
F - E <= 12 Then _
Difference2 = True
End Function

Any Help will be Greatly Appreciated.
All the Best
Paul
Windows XP Home - XL2002

Input Values Instead of Hard Coded Values
 

Thanks VERY Much Bob,

That is Certainly Something I Can Use, Especially Since there are
About a 100 Hard Coded Values Throughout the Macro.
Once I Changed the References to :-

Function Difference1()
aryValues = Worksheets("Data").Range("A1:L1")
Difference1 = False
If _
A = aryValues(1, 1) And A <= aryValues(1, 2) And _
B = aryValues(1, 3) And B <= aryValues(1, 4) And _
C = aryValues(1, 5) And C <= aryValues(1, 6) And _
D = aryValues(1, 7) And D <= aryValues(1, 8) And _
E = aryValues(1, 9) And E <= aryValues(1, 10) And _
F = aryValues(1, 11) And F <= aryValues(1, 12) Then _
Difference1 = True
End Function

It Worked like a Dream.
I think I will SetUp a Sheet ( Called Data ) with ALL the Values I
Need to Use, and then Use your Solution to Reference ALL the Values. I
can See how the References Work.
Does Calling Data this way Slow Down the Macro to Any Degree.

Thanks Again Bob.
All the Best
Paul



"Bob Phillips" wrote in message ...
Here is one way. Put the values into A1:L1 on a worksheet named data. Then
use


Dim aryValues
Function Difference1()
aryValues = Worksheets("data").Range("A1:L1")
Difference1 = False
If _
a = aryValues(1, 1) And a <= aryValues(1, 3) And _
B = aryValues(1, 2) And B <= aryValues(1, 5) And _
C = aryValues(1, 3) And C <= aryValues(1, 6) And _
D = aryValues(1, 4) And D <= aryValues(1, 8) And _
E = aryValues(1, 7) And E <= aryValues(1, 10) And _
F = aryValues(1, 9) And F <= aryValues(1, 11) Then _
Difference1 = True
End Function

Function Difference2()
aryValues = Worksheets("data").Range("A1:L1")
Difference2 = False
If _
B - a <= aryValues(1, 4) And _
C - B <= aryValues(1, 5) And _
D - C <= aryValues(1, 12) And _
E - D <= aryValues(1, 6) And _
F - E <= aryValues(1, 7) Then _
Difference2 = True
End Function


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Paul Black" wrote in message
...
Hi Everybody,

I have a Macro that Works Well. The Only Problem is that ALL the
Values ( Data ) I Use are Hard Coded in the Macro.
What would I Need to do ( and How would I go About it ) if I Wanted
the Macro to Pick Up the Values ( Data ) from a Table or Something
Instead of having them Hard Coded in the Macro. The Hard Coded Values
( Data ) Used are NOT in the Workbook and will Change Constantly. I
Really want to be Able to Input the Values Somewhere and then Run the
Macro as Often as Necessary.
Below are 2 Examples of Working Functions :-


Function Difference1()
Difference1 = False
If _
A = 1 And A <= 3 And _
B = 2 And B <= 7 And _
C = 3 And C <= 10 And _
D = 5 And D <= 13 And _
E = 12 And E <= 17 And _
F = 16 And F <= 20 Then _
Difference1 = True
End Function

Function Difference2()
Difference2 = False
If _
B - A <= 5 And _
C - B <= 7 And _
D - C <= 8 And _
E - D <= 10 And _
F - E <= 12 Then _
Difference2 = True
End Function

Any Help will be Greatly Appreciated.
All the Best
Paul
Windows XP Home - XL2002

Input Values Instead of Hard Coded Values
 

Paul,

It will inevitably take more to load the array from a range, but unless you
are doing it thousands of times, I wouldn't expect it to be noticeable.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Paul Black" wrote in message
...
Thanks VERY Much Bob,

That is Certainly Something I Can Use, Especially Since there are
About a 100 Hard Coded Values Throughout the Macro.
Once I Changed the References to :-

Function Difference1()
aryValues = Worksheets("Data").Range("A1:L1")
Difference1 = False
If _
A = aryValues(1, 1) And A <= aryValues(1, 2) And _
B = aryValues(1, 3) And B <= aryValues(1, 4) And _
C = aryValues(1, 5) And C <= aryValues(1, 6) And _
D = aryValues(1, 7) And D <= aryValues(1, 8) And _
E = aryValues(1, 9) And E <= aryValues(1, 10) And _
F = aryValues(1, 11) And F <= aryValues(1, 12) Then _
Difference1 = True
End Function

It Worked like a Dream.
I think I will SetUp a Sheet ( Called Data ) with ALL the Values I
Need to Use, and then Use your Solution to Reference ALL the Values. I
can See how the References Work.
Does Calling Data this way Slow Down the Macro to Any Degree.

Thanks Again Bob.
All the Best
Paul



"Bob Phillips" wrote in message
...
Here is one way. Put the values into A1:L1 on a worksheet named data.
Then
use


Dim aryValues
Function Difference1()
aryValues = Worksheets("data").Range("A1:L1")
Difference1 = False
If _
a = aryValues(1, 1) And a <= aryValues(1, 3) And _
B = aryValues(1, 2) And B <= aryValues(1, 5) And _
C = aryValues(1, 3) And C <= aryValues(1, 6) And _
D = aryValues(1, 4) And D <= aryValues(1, 8) And _
E = aryValues(1, 7) And E <= aryValues(1, 10) And _
F = aryValues(1, 9) And F <= aryValues(1, 11) Then _
Difference1 = True
End Function

Function Difference2()
aryValues = Worksheets("data").Range("A1:L1")
Difference2 = False
If _
B - a <= aryValues(1, 4) And _
C - B <= aryValues(1, 5) And _
D - C <= aryValues(1, 12) And _
E - D <= aryValues(1, 6) And _
F - E <= aryValues(1, 7) Then _
Difference2 = True
End Function


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Paul Black" wrote in message
...
Hi Everybody,

I have a Macro that Works Well. The Only Problem is that ALL the
Values ( Data ) I Use are Hard Coded in the Macro.
What would I Need to do ( and How would I go About it ) if I Wanted
the Macro to Pick Up the Values ( Data ) from a Table or Something
Instead of having them Hard Coded in the Macro. The Hard Coded Values
( Data ) Used are NOT in the Workbook and will Change Constantly. I
Really want to be Able to Input the Values Somewhere and then Run the
Macro as Often as Necessary.
Below are 2 Examples of Working Functions :-


Function Difference1()
Difference1 = False
If _
A = 1 And A <= 3 And _
B = 2 And B <= 7 And _
C = 3 And C <= 10 And _
D = 5 And D <= 13 And _
E = 12 And E <= 17 And _
F = 16 And F <= 20 Then _
Difference1 = True
End Function

Function Difference2()
Difference2 = False
If _
B - A <= 5 And _
C - B <= 7 And _
D - C <= 8 And _
E - D <= 10 And _
F - E <= 12 Then _
Difference2 = True
End Function

Any Help will be Greatly Appreciated.
All the Best
Paul
Windows XP Home - XL2002

Input Values Instead of Hard Coded Values
 

Thanks Bob,

All the Best
Paul



"Bob Phillips" wrote in message ...
Paul,

It will inevitably take more to load the array from a range, but unless you
are doing it thousands of times, I wouldn't expect it to be noticeable.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Paul Black" wrote in message
...
Thanks VERY Much Bob,

That is Certainly Something I Can Use, Especially Since there are
About a 100 Hard Coded Values Throughout the Macro.
Once I Changed the References to :-

Function Difference1()
aryValues = Worksheets("Data").Range("A1:L1")
Difference1 = False
If _
A = aryValues(1, 1) And A <= aryValues(1, 2) And _
B = aryValues(1, 3) And B <= aryValues(1, 4) And _
C = aryValues(1, 5) And C <= aryValues(1, 6) And _
D = aryValues(1, 7) And D <= aryValues(1, 8) And _
E = aryValues(1, 9) And E <= aryValues(1, 10) And _
F = aryValues(1, 11) And F <= aryValues(1, 12) Then _
Difference1 = True
End Function

It Worked like a Dream.
I think I will SetUp a Sheet ( Called Data ) with ALL the Values I
Need to Use, and then Use your Solution to Reference ALL the Values. I
can See how the References Work.
Does Calling Data this way Slow Down the Macro to Any Degree.

Thanks Again Bob.
All the Best
Paul



"Bob Phillips" wrote in message
...
Here is one way. Put the values into A1:L1 on a worksheet named data.
Then
use


Dim aryValues
Function Difference1()
aryValues = Worksheets("data").Range("A1:L1")
Difference1 = False
If _
a = aryValues(1, 1) And a <= aryValues(1, 3) And _
B = aryValues(1, 2) And B <= aryValues(1, 5) And _
C = aryValues(1, 3) And C <= aryValues(1, 6) And _
D = aryValues(1, 4) And D <= aryValues(1, 8) And _
E = aryValues(1, 7) And E <= aryValues(1, 10) And _
F = aryValues(1, 9) And F <= aryValues(1, 11) Then _
Difference1 = True
End Function

Function Difference2()
aryValues = Worksheets("data").Range("A1:L1")
Difference2 = False
If _
B - a <= aryValues(1, 4) And _
C - B <= aryValues(1, 5) And _
D - C <= aryValues(1, 12) And _
E - D <= aryValues(1, 6) And _
F - E <= aryValues(1, 7) Then _
Difference2 = True
End Function


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Paul Black" wrote in message
...
Hi Everybody,

I have a Macro that Works Well. The Only Problem is that ALL the
Values ( Data ) I Use are Hard Coded in the Macro.
What would I Need to do ( and How would I go About it ) if I Wanted
the Macro to Pick Up the Values ( Data ) from a Table or Something
Instead of having them Hard Coded in the Macro. The Hard Coded Values
( Data ) Used are NOT in the Workbook and will Change Constantly. I
Really want to be Able to Input the Values Somewhere and then Run the
Macro as Often as Necessary.
Below are 2 Examples of Working Functions :-


Function Difference1()
Difference1 = False
If _
A = 1 And A <= 3 And _
B = 2 And B <= 7 And _
C = 3 And C <= 10 And _
D = 5 And D <= 13 And _
E = 12 And E <= 17 And _
F = 16 And F <= 20 Then _
Difference1 = True
End Function

Function Difference2()
Difference2 = False
If _
B - A <= 5 And _
C - B <= 7 And _
D - C <= 8 And _
E - D <= 10 And _
F - E <= 12 Then _
Difference2 = True
End Function

Any Help will be Greatly Appreciated.
All the Best
Paul
Windows XP Home - XL2002