Appendix Thread. ( Codes for other Threads, etc.) Event Coding Drpdown Data validation

[DocAElstein]

In support of answer to this main excelfox Excel Forum Thread: http://www.excelfox.com/forum/showth...1188#post11188

Sample file:
_____ Workbook: MainMacroFile.xlsm ( Using Excel 2007 32 bit )

Row\Col	A	B	C	D	E
1	expiry date	mark Brand	value
2	27.06.2019	a	1
3	26.06.2019	b	2
4	25.06.2019	c	3
5	24.06.2019	d	4
6	23.06.2019	e	5
7	22.06.2019	f	6
8	21.06.2019	g	7
9	20.06.2019	h	8
10	27.06.2019	i	9
11	26.06.2019	j	10
12
13

Worksheet: Tabelle1


_____ Workbook: MainMacroFile.xlsm ( Using Excel 2007 32 bit )

Row\Col	A	B	C	D	E
1	expiry date	mark Brand	value
2	=TODAY() -(ROW()-3)	a	1
3	=TODAY() -(ROW()-3)	b	2
4	=TODAY() -(ROW()-3)	c	3
5	=TODAY() -(ROW()-3)	d	4
6	=TODAY() -(ROW()-3)	e	5
7	=TODAY() -(ROW()-3)	f	6
8	=TODAY() -(ROW()-3)	g	7
9	=TODAY() -(ROW()-3)	h	8
10	27.06.2019	i	9
11	26.06.2019	j	10
12
13

Worksheet: Tabelle1

Data analysis using VBA arrays
I personally like to work with VBA arrays. So I put our data into an array, with this code line:
ThisWorkbook.Worksheets.Item(1).Range("A1").CurrentRegion.Value2
You can see what is in our arrData() if you step through the coding from within the VB Editor ( Hit key F8 with the cursor in the routine) , then before the run is finished and after the above code line ( Let arrData() ThisWorkbook.Worksheets.Item(1).Range("A1").CurrentRegion.Value2 ) , select any arrData(), and then hit key F9. This will add the array, arrData() to a watch window:
F9 arrData().JPG: https://imgur.com/02xZas2
F9 __ arrData().JPG: https://imgur.com/1QKwEb4
The CurrentRegion
The CurrentRegion range property of a range ( in this example the range is range A1 ), returns the range connected to that range which can be bordered by either empty columns and rows, or the spreadsheet boundaries. In this example , the CurrentRegion range associated with range A1, is that range enclosed by row 12, column D and the left and top spreadsheet boundaries
_____ Workbook: MainMacroFile.xlsm ( Using Excel 2007 32 bit )

Row\Col	A	B	C	D	E
1	expiry date	mark Brand	value
2	27.06.2019	a	1
3	26.06.2019	b	2
4	25.06.2019	c	3
5	24.06.2019	d	4
6	23.06.2019	e	5
7	22.06.2019	f	6
8	21.06.2019	g	7
9	20.06.2019	h	8
10	27.06.2019	i	9
11	26.06.2019	j	10
12
13

Worksheet: Tabelle1

So this is effectively what our arrData() looks like:

expiry date	mark Brand	value
43643	a	1
43642	b	2
43641	c	3
43640	d	4
43639	e	5
43638	f	6
43637	g	7
43636	h	8
43643	i	9
43642	j	10

So , for example, arrData(5, 2) has a value of d, and arrData(5, 3) value is 4
Effectively a VBA array is a fixed size spreadsheet, ( usually much smaller than a full spreadsheet ) . You cannot see its contents directly, but you can see it using the Watch Window, as discussed above. It can only have limited infomation - you cannot hold in it things like cell size and color infomation. We are using it to hold the .Value2 . .Value2 is the most fundamental value. .Value2 is usually the simple value that you see in the spreadsheet. One exception to this is with dates. The .Value2 of a date is that number held by Excel internally, which is a whole number starting at 1 for the date of January 1, 1900, and increasing by 1 for every day since then.
So , for example, the .Value2 of January 5, 1900 is 5
The .Value2 for the current day as I write this is 43643,which I can see if I step through the routine which is given in the example file , and hover over the variable , DteAujourd_hui , which the coding fills with the whole number part of the current date and time
43643.jpg : https://imgur.com/mMC42MI
The exact number you see will likely be slightly different , depending on where you are and when you run the routine