Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combo Box / Hard Coded List | Excel Discussion (Misc queries) | |||
Sum function when the values in an xls are color coded | Excel Worksheet Functions | |||
Totalling coded values | Excel Worksheet Functions | |||
Convert Hard-coded cell values to constants | Excel Discussion (Misc queries) | |||
Find Hard Coded Cells | Excel Programming |