Example file How to fill a Combobox / Listbox

1 Populate: Array
1.1 List of strings
1.2 List of numbers
1.3 List of dates
1.4 Dateformat: intern. settings
1.5 Dateformat: own settings

2 Populate: Split
2.1 Files
2.1.1 in a folder
2.1.2 in folder and subfolders
2.1.3 pdf in a folder
2.1.4 pdf in folder and subfolders
2.1.5 name ascending
2.1.6 name descending
2.1.7 size ascending
2.1.8 size descending
2.1.9 date ascending
2.1.10 date descending
2.1.11 creationdate: descending
2.1.12 lastaccessdate: descending
2.1.13 lastsavedate: descending

2.2 Subfolders
2.2.1 files & (1-level) subfolders
2.2.2 files & all subfolders
2.2.3 1-level) subfolders
2.2.4 all subfolders

2.3 Worksheet
2.3.1 all worksheets

3 Populate: Customlist
3.1 weekdaynames:short (en.)
3.2 weekdaynames
3.3 monthnames: short
3.4 monthnames
3.5 own customlist

4 Populate: Worksheetrange
4.1 a column
4.2 a row
4.3 a range
4.4 uniques in column
4.5 uniques sorted in column
4.6 uniques in row
4.7 uniques sorted in row
4.8 uniques in range
4.9 uniques sorted in range

5 Populate: Evaluate
5.1.1 all decimals
5.1.2 Ucase alphabet
5.1.3 Lcase alphabet
5.1.4 numbers interval 5
5.1.5 numbers interval 7

5.2 Monthnames
5.2.1 extended
5.2.2 abbreviated
5.2.3 international

5.3 Weekdaynames
5.3.1 extended
5.3.2 ISO-week extended
5.3.3 abbreviated
5.3.4 ISO-week abbreviated
5.3.5 ISO-week international

5.4 Dates
5.4.2 this ISO week
5.4.2 previous ISO week
5.4.3 next ISO week
5.4.4 this month
5.4.5 previous month
5.4.6 next month
5.4.7 this ISO year
5.4.8 previous ISO year
5.4.9 next ISO year
5.4.10 preceding 28 days
5.4.11 next 28 days
5.4.12 sundays ISO year
5.4.13 mondays ISO year
5.4.14 tuesdays ISO year
5.4.15 wednesdays ISO year
5.4.16 thursdays ISO year
5.4.17 fridays ISO year
5.4.18 saturdays ISO year
5.4.19 wednesday every 6 weeks ISO year

5.5 times
5.5.1 hour in a day
5.5.2 half an hour in a day
5.5.3 quarter of an hour in a day
5.5.4 every 10 minutes in a day
5.5.5 hour form 08:00 to 18:00
5.5.6 10 seconds restricted interval

5.6 Math

Introduction

Everything we'll discuss here applies to Comboboxes as well as Listboxes.
For readability's sake only the combobox will be mentioned.
You can safely substitute the word 'combobox' by 'listbox'.

0.1 Combobox/Listbox features

0.1.1 Lbound & Ubound

A combobox can contain a 1-dimensional Array
the number of rows: ubound(combobox1.list)+1
A combobox can contain a 2-dimensonial Array
the number of rows : ubound(combobox1.list)+1
the number of columns : ubound(combobox1.list,2) + 1

The first row in a combobox is 0 : Lbound(combobox1.List)
The first column in a combobox is 0 : LBound(Combobox1.List,2)

To return the third item in a 1-dimensional Combobox: msgbox Combobox1.List(2)
To return the third item in the 5th column in a 2-dimensional Combobox: msgbox Combobox1.List(2,4)

0.1.2 Style

If you use a combobox to provide with an exclusive list of valid options you'd better prevent the entering of new items by the user.
I you set the property 'style' to 2 (dropdownlist) only the populated items can be chosen.
The combobox is then identical to the listbox.
There's 1 execption: the combobox has a builtin search & filter function based on the characters that the user entered in the inputfield of the combobox.

0.2 Populating methods

0.2.1 Additem

The method Additem is meant to add exactly 1 item to a combobox/listbox.
This method is not apt for adding many items to a combobox/listbox.
If more then 10 items are being added, it diminishes the speed of your code dramatically.
That's probably due to the fact that the new item has to find it's position in the existing list.
So 'Additem' isn't meant to populate a combobox/listbox.

0.2.2 Rowsource

If you use 'rowsource' you make a direct link to a certain range in a worksheet.
Combined with the combobox it can cause Excel to blackout, because any change in the combobox will be transferred directly to the 'source'.
Excel has proven not to be able to do this correctly.
Besides: the changing of the source at every change in the combobox slows down your code.
You should reduce the reading/writing from/to a workbook in your code as much as possible.

0.2.3 Listfillrange

In ActiveX-controls the listfillrange method is an equivalent for the Rowsource method.
The same disadvantages apply to the Listfillrange method.
So preferably you don't use it.

0.2.4 List

The best method to fill a Combobox/listbox is the method 'List'.
You can directly assign any 1-dimensional or 2-dimensional array to a combobox/listbox.
The nice thing is that the combobox/listbox automatically preserves the 'rows' and 'columns' of the array.
If you can't write the items in 1 go into the combobox/listbox you'd better store them in an array first.
When ready you can assign the array to the combobox/listbox.
That's the most efficient way to populate.

0.2.5 Column

The method Column is identical to the method List.
There is 1 exception: the array that is being written to the listbox/combobox will be transposed: rows will become columns, columns will be converted into rows.

1 Populate a combobox using the function Array

1.1 A list of strings

ComboBox1.List = Array("een", "twee", "drie")

1.2 A list of numbers

ComboBox1.List = Array(1, 2, 3)

1.3 A list of dates

ComboBox1.List = Array(Date, Date + 1, Date + 2)

1.4 Dateformat dependent of international settings

ComboBox1.List = Array(FormatDateTime(Date, 2), FormatDateTime(Date + 1, 2), FormatDateTime(Date + 2, 2))

1.5 Dateformat independent of international settings

ComboBox1.List = Array(Format(Date), Format(Date + 1), Format(Date + 2))
ComboBox1.List = Array(Format(Date, "dd-mm-yyyy"), Format(Date + 1, "dd-mm-yyyy"), Format(Date + 2, "dd-mm-yyyy"))

2 The method Split

ComboBox1.List = Split("een twee drie")
ComboBox1.List = Split("een|twee|drie", "|")
ComboBox1.List = Split("1,2,3", ",")
ComboBox1.List = Split(Format(Date) & "," & Format(Date + 1) & "," & Format(Date + 2), ",")

2.1 Files in a folder

In these examples the pathname has been wrapped in quotation marks.
This prevents the code from erroring if a (sub)foldername contains a space.

2.1.1 all files in a certain folder

ComboBox1.List = Filter(Split(CreateObject("wscript.shell").exec("cmd /c Dir ""G:\test folder\"" /b /a-d").stdout.readall, vbCrLf), ".")

2.1.2 all files in a folder and in all it's subfolders

ComboBox1.List = Filter(Split(CreateObject("wscript.shell").exec("cmd /c Dir ""G:\test folder\"" /b /a-d /s").stdout.readall, vbCrLf), ".")

2.1.3 all pdf-files in a folder

ComboBox1.List = Filter(Split(CreateObject("wscript.shell").exec("cmd /c Dir ""G:\test folder\*.pdf"" /b /a-d").stdout.readall, vbCrLf), ".")

2.1.4 all pdf-files in a folder and in it's all subfolders

ComboBox1.List = Filter(Split(CreateObject("wscript.shell").exec("cmd /c Dir ""G:\test folder\*.pdf"" /b /a-d /s").stdout.readall, vbCrLf), ".")

2.1.5 all files in a folder: alfabetically ascending sorted by name

ComboBox1.List = Filter(Split(CreateObject("wscript.shell").exec("cmd /c Dir ""G:\test folder\"" /b /a-d /on").stdout.readall, vbCrLf), ".")

2.1.6 all files in a folder: alfabetically descending sorted by name

ComboBox1.List = Filter(Split(CreateObject("wscript.shell").exec("cmd /c Dir ""G:\test folder\"" /b /a-d /o-n").stdout.readall, vbCrLf), ".")

2.1.7 all files in a folder: ascendingly sorted by size

ComboBox1.List = Filter(Split(CreateObject("wscript.shell").exec("cmd /c Dir ""G:\test folder\"" /b /a-d /os").stdout.readall, vbCrLf), ".")

2.1.8 all files in a folder: descendingly sorted by size

ComboBox1.List = Filter(Split(CreateObject("wscript.shell").exec("cmd /c Dir ""G:\test folder\"" /b /a-d /o-s").stdout.readall, vbCrLf), ".")

2.1.9 all files in a folder: ascendingly sorted by date

ComboBox1.List = Filter(Split(CreateObject("wscript.shell").exec("cmd /c Dir ""G:\test folder\"" /b /a-d /od").stdout.readall, vbCrLf), ".")

2.1.10 all files in a folder: descendingly sorted by date

ComboBox1.List = Filter(Split(CreateObject("wscript.shell").exec("cmd /c Dir ""G:\test folder\"" /b /a-d /o-d").stdout.readall, vbCrLf), ".")

2.1.11 all files in a folder: descendingly sorted by creationdate

ComboBox1.List = Filter(Split(CreateObject("wscript.shell").exec("cmd /c Dir ""G:\test folder\"" /b /a-d /o-d /tc").stdout.readall, vbCrLf), ".")

2.1.12 all files in a folder: descendingly sorted by lastaccessdate

ComboBox1.List = Filter(Split(CreateObject("wscript.shell").exec("cmd /c Dir ""G:\test folder\"" /b /a-d /o-d /ta").stdout.readall, vbCrLf), ".")

2.1.13 all files in a folder: descendingly sorted by lastsavedate

ComboBox1.List = Filter(Split(CreateObject("wscript.shell").exec("cmd /c Dir ""G:\test folder\"" /b /a-d /o-d /tw").stdout.readall, vbCrLf), ".")

2.2 Subfolders

2.2.1 all files and (1-level) subfolders in a certain folder

ComboBox1.List = Split(CreateObject("wscript.shell").exec("cmd /c Dir ""G:\test folder\"" /b").stdout.readall, vbCrLf)

2.2.2 all files and all subfolders in a certain folder

ComboBox1.List = Split(CreateObject("wscript.shell").exec("cmd /c Dir ""G:\test folder\"" /b /s").stdout.readall, vbCrLf)

2.2.3 all (1-level) subfolders in a certain directory/folder

ComboBox1.List = Split(CreateObject("wscript.shell").exec("cmd /c Dir ""G:\test folder\*."" /b").stdout.readall, vbCrLf)

2.2.4 all subfolders of a certain directory/folder

ComboBox1.List = Split(CreateObject("wscript.shell").exec("cmd /c Dir ""G:\test folder\*."" /b /s").stdout.readall, vbCrLf)

2.3 Worksheet

2.3.1 all worksheetnames

For Each sh In Worksheets
c00 = c00 & "|" & sh.Name
Next
ComboBox1.List = Split(Mid(c00, 2), "|")

3 Populate a combobox using a Customlist

3.1 the weekdaynames (first 3 characters (English))

ComboBox1.List = Application.GetCustomListContents(1)

3.2 weekdaynames

ComboBox1.List = Application.GetCustomListContents(2)

3.3 the monthnames (first 3 characters)

ComboBox1.List = Application.GetCustomListContents(3)

3.4 monthnames

ComboBox1.List = Application.GetCustomListContents(4)

3.5 your own customlist

Application.AddCustomList Array("drie", "twee", "een")
ComboBox1.List = Application.GetCustomListContents(Application.CustomListCount)

4 Populate a combobox using a Worksheetrange

4.1 a column

ComboBox1.List = Sheets(1).Cells(1, 4).Resize(10).Value
UBound(ComboBox1.List)=9
UBound(ComboBox1.List, 2)=0

4.2 a row

ComboBox1.List = Sheets(1).Cells(1, 1).Resize(, 10).Value
UBound(ComboBox1.List)=0
UBound(ComboBox1.List, 2)=9
If you want to show all columns you will have to change the property columncount: combobox1.columncount=ubound(combobox1.list,2)+1

4.3 a range

ComboBox1.List = Sheets(1).Cells(1,1).Resize(10,10).Value
UBound(ComboBox1.List)=9
UBound(ComboBox1.List, 2)=9
If you want to show all columns you will have to change the property columncount: combobox1.columncount=ubound(combobox1.list,2)+1

4.4 unique values in a column

ComboBox1.List = Application.Transpose(Filter([transpose(if(countif(offset($A$1,,,row(A1:A100)),A1:A100)=1,A1:A100,"~"))], "~", False))
or
sn = Sheets(1).Range("A1:A100")
With CreateObject("scripting.dictionary")
For Each cl In sn
If cl<>"" And Not .exists(cl) Then .Add cl, Nothing
Next

ComboBox1.List = .keys
End With

4.5 unique sorted values in a column

With CreateObject("System.Collections.ArrayList")
For Each cl In sn
If cl<>"" And Not .contains(cl) Then .Add cl
Next
.Sort

ComboBox1.List = Application.Transpose(.toarray())
End With

4.6 unique values in a row

combobox1.list = Application.Transpose(Filter([index(if(countif(offset($A$1,,,,column(A1:AZ1)),A1:AZ1)=1,A1:AZ1),)], "False", False))
sn = Sheets(1).Range("A1:AZ1")
With CreateObject("scripting.dictionary")
For Each cl In sn
If cl<>"" And Not .exists(cl) Then .Add cl, Nothing
Next

ComboBox1.List = .keys
End With

4.7 unique sorted values in a row

sn= Sheets(1).Range("A1:AZ1")
With CreateObject("System.Collections.ArrayList")
For Each cl In sn
If cl<>"" And Not .contains(cl) Then .Add cl
Next
.Sort

ComboBox1.List = Application.Transpose(.toarray())
End With

4.8 unique values in a range

sn = Sheets(1).Range("A1:AZ100")
With CreateObject("scripting.dictionary")
For Each cl In sn
If cl<>"" And Not .exists(cl) Then .Add cl, Nothing
Next

ComboBox1.List = .keys
End With

4.9 unique sorted values in a range

sn= Sheets(1).Range("A1:AZ100")
With CreateObject("System.Collections.ArrayList")
For Each cl In sn
If cl<>"" And Not .contains(cl) Then .Add cl
Next
.Sort

ComboBox1.List = Application.Transpose(.toarray())
End With

5 Populate a combobox using the method Evaluate

5.1.1 all decimal numbers

ComboBox1.List = Array(0, 1, 2, 3, 4, 5, 6, 7, 8, 9)
ComboBox1.List = Array("0", "1", "2", "3", "4", "5", "6", "7", "8", "9")
ComboBox1.List = Split("0 1 2 3 4 5 6 7 8 9")
ComboBox1.List = [row(1:10)-1]
ComboBox1.List = [index(row(1:10)-1,)]
ComboBox1.List = [index(char(row(48:57)),)]
ComboBox1.List = [transpose(char(row(48:57)))]
ComboBox1.List = Evaluate("row(1:10)-1")
ComboBox1.List = Evaluate("index(char(row(48:57)),)")
ComboBox1.List = Evaluate("transpose(char(row(48:57)))")

5.1.2 Ucase alphabet

ComboBox1.List = [index(char(64+row(1:26)),)]

5.1.3 Lcase alphabet

ComboBox1.List = [index(char(96+row(1:26)),)]

5.1.4 numbers with intervale.g. 0 _ 5 _ 10 _ 15_ etc.

ComboBox1.List = [index(5*(row(1:10)-1),)]

5.1.5 numbers with intervale.g. 0 _ 7 _ 14 _ 21_ etc.

ComboBox1.List = [index(7*(row(1:10)-1),)]

5.2 Monthnames

5.2.1 monthnames extended

ComboBox1.List = [index(text(date(2015,row(1:12),1),"mmmm"),)]

5.2.2 monthnames abbreviated

ComboBox1.List = [index(text(date(2015,row(1:12),1),"mmm"),)]

5.2.3 monthnames in several languages

ComboBox1.List = [index(text(date(2015,row(1:12),1),"[$-401]mmmm"),)]' Arabic - Saudi Arabia 401
ComboBox1.List = [index(text(date(2015,row(1:12),1),"[$-402]mmmm"),)]' Bulgarian 402
ComboBox1.List = [index(text(date(2015,row(1:12),1),"[$-403]mmmm"),)]' Catalan 403
ComboBox1.List = [index(text(date(2015,row(1:12),1),"[$-404]mmmm"),)]' Chinese - Taiwan 404
ComboBox1.List = [index(text(date(2015,row(1:12),1),"[$-405]mmmm"),)]' Czech 405
ComboBox1.List = [index(text(date(2015,row(1:12),1),"[$-406]mmmm"),)]' Danish 406
ComboBox1.List = [index(text(date(2015,row(1:12),1),"[$-407]mmmm"),)]' German - Germany 407
ComboBox1.List = [index(text(date(2015,row(1:12),1),"[$-408]mmmm"),)]' Greek 408
ComboBox1.List = [index(text(date(2015,row(1:12),1),"[$-409]mmmm"),)]' English - United States 409
ComboBox1.List = [index(text(date(2015,row(1:12),1),"[$-410]mmmm"),)]' Italian - Italy 410
ComboBox1.List = [index(text(date(2015,row(1:12),1),"[$-411]mmmm"),)]' Japanese 411
ComboBox1.List = [index(text(date(2015,row(1:12),1),"[$-412]mmmm"),)]' Korean 412
ComboBox1.List = [index(text(date(2015,row(1:12),1),"[$-413]mmmm"),)]' Dutch - Netherlands 413
ComboBox1.List = [index(text(date(2015,row(1:12),1),"[$-414]mmmm"),)]' Norwegian - Bokml 414
ComboBox1.List = [index(text(date(2015,row(1:12),1),"[$-415]mmmm"),)]' Polish 415
ComboBox1.List = [index(text(date(2015,row(1:12),1),"[$-416]mmmm"),)]' Portuguese - Brazil 416
ComboBox1.List = [index(text(date(2015,row(1:12),1),"[$-417]mmmm"),)]' Raeto-Romance 417
ComboBox1.List = [index(text(date(2015,row(1:12),1),"[$-418]mmmm"),)]' Romanian - Romania 418
ComboBox1.List = [index(text(date(2015,row(1:12),1),"[$-419]mmmm"),)]' Russian 419
ComboBox1.List = [index(text(date(2015,row(1:12),1),"[$-420]mmmm"),)]' Urdu 420
ComboBox1.List = [index(text(date(2015,row(1:12),1),"[$-421]mmmm"),)]' Indonesian 421
ComboBox1.List = [index(text(date(2015,row(1:12),1),"[$-422]mmmm"),)]' Ukrainian 422
ComboBox1.List = [index(text(date(2015,row(1:12),1),"[$-423]mmmm"),)]' Belarusian 423
ComboBox1.List = [index(text(date(2015,row(1:12),1),"[$-424]mmmm"),)]' Slovenian 424
ComboBox1.List = [index(text(date(2015,row(1:12),1),"[$-425]mmmm"),)]' Estonian 425
ComboBox1.List = [index(text(date(2015,row(1:12),1),"[$-426]mmmm"),)]' Latvian 426
ComboBox1.List = [index(text(date(2015,row(1:12),1),"[$-427]mmmm"),)]' Lithuanian 427
ComboBox1.List = [index(text(date(2015,row(1:12),1),"[$-428]mmmm"),)]' Tajik 428
ComboBox1.List = [index(text(date(2015,row(1:12),1),"[$-429]mmmm"),)]' Farsi - Persian 429
ComboBox1.List = [index(text(date(2015,row(1:12),1),"[$-430]mmmm"),)]' Sesotho (Sutu) 430
ComboBox1.List = [index(text(date(2015,row(1:12),1),"[$-431]mmmm"),)]' Tsonga 431
ComboBox1.List = [index(text(date(2015,row(1:12),1),"[$-432]mmmm"),)]' Setsuana 432
ComboBox1.List = [index(text(date(2015,row(1:12),1),"[$-433]mmmm"),)]' Venda 433
ComboBox1.List = [index(text(date(2015,row(1:12),1),"[$-434]mmmm"),)]' Xhosa 434
ComboBox1.List = [index(text(date(2015,row(1:12),1),"[$-435]mmmm"),)]' Zulu 435
ComboBox1.List = [index(text(date(2015,row(1:12),1),"[$-436]mmmm"),)]' Afrikaans 436
ComboBox1.List = [index(text(date(2015,row(1:12),1),"[$-437]mmmm"),)]' Georgian 437
ComboBox1.List = [index(text(date(2015,row(1:12),1),"[$-438]mmmm"),)]' Faroese 438
ComboBox1.List = [index(text(date(2015,row(1:12),1),"[$-439]mmmm"),)]' Hindi 439
ComboBox1.List = [index(text(date(2015,row(1:12),1),"[$-440]mmmm"),)]' Kyrgyz - Cyrillic 440
ComboBox1.List = [index(text(date(2015,row(1:12),1),"[$-441]mmmm"),)]' Swahili 441
ComboBox1.List = [index(text(date(2015,row(1:12),1),"[$-442]mmmm"),)]' Turkmen 442
ComboBox1.List = [index(text(date(2015,row(1:12),1),"[$-443]mmmm"),)]' Uzbek - Latin 443
ComboBox1.List = [index(text(date(2015,row(1:12),1),"[$-444]mmmm"),)]' Tatar 444
ComboBox1.List = [index(text(date(2015,row(1:12),1),"[$-445]mmmm"),)]' Bengali - India 445
ComboBox1.List = [index(text(date(2015,row(1:12),1),"[$-446]mmmm"),)]' Punjabi 446
ComboBox1.List = [index(text(date(2015,row(1:12),1),"[$-447]mmmm"),)]' Gujarati 447
ComboBox1.List = [index(text(date(2015,row(1:12),1),"[$-448]mmmm"),)]' Oriya 448
ComboBox1.List = [index(text(date(2015,row(1:12),1),"[$-449]mmmm"),)]' Tamil 449
ComboBox1.List = [index(text(date(2015,row(1:12),1),"[$-450]mmmm"),)]' Mongolian 450
ComboBox1.List = [index(text(date(2015,row(1:12),1),"[$-451]mmmm"),)]' Tibetan 451
ComboBox1.List = [index(text(date(2015,row(1:12),1),"[$-452]mmmm"),)]' Welsh 452
ComboBox1.List = [index(text(date(2015,row(1:12),1),"[$-453]mmmm"),)]' Khmer 453
ComboBox1.List = [index(text(date(2015,row(1:12),1),"[$-454]mmmm"),)]' Lao 454
ComboBox1.List = [index(text(date(2015,row(1:12),1),"[$-455]mmmm"),)]' Burmese 455
ComboBox1.List = [index(text(date(2015,row(1:12),1),"[$-456]mmmm"),)]' Galician 456
ComboBox1.List = [index(text(date(2015,row(1:12),1),"[$-457]mmmm"),)]' Konkani 457
ComboBox1.List = [index(text(date(2015,row(1:12),1),"[$-458]mmmm"),)]' Manipuri 458
ComboBox1.List = [index(text(date(2015,row(1:12),1),"[$-459]mmmm"),)]' Sindhi 459
ComboBox1.List = [index(text(date(2015,row(1:12),1),"[$-460]mmmm"),)]' Kashmiri 460
ComboBox1.List = [index(text(date(2015,row(1:12),1),"[$-461]mmmm"),)]' Nepali 461
ComboBox1.List = [index(text(date(2015,row(1:12),1),"[$-462]mmmm"),)]' Frisian - Netherlands 462
ComboBox1.List = [index(text(date(2015,row(1:12),1),"[$-464]mmmm"),)]' Filipino 464
ComboBox1.List = [index(text(date(2015,row(1:12),1),"[$-465]mmmm"),)]' Divehi; Dhivehi; Maldivian 465
ComboBox1.List = [index(text(date(2015,row(1:12),1),"[$-466]mmmm"),)]' Edo 466
ComboBox1.List = [index(text(date(2015,row(1:12),1),"[$-470]mmmm"),)]' Igbo - Nigeria 470
ComboBox1.List = [index(text(date(2015,row(1:12),1),"[$-474]mmmm"),)]' Guarani - Paraguay 474
ComboBox1.List = [index(text(date(2015,row(1:12),1),"[$-476]mmmm"),)]' Latin 476
ComboBox1.List = [index(text(date(2015,row(1:12),1),"[$-477]mmmm"),)]' Somali 477
ComboBox1.List = [index(text(date(2015,row(1:12),1),"[$-481]mmmm"),)]' Maori 481
ComboBox1.List = [index(text(date(2015,row(1:12),1),"[$-801]mmmm"),)]' Arabic - Iraq 801
ComboBox1.List = [index(text(date(2015,row(1:12),1),"[$-804]mmmm"),)]' Chinese - China 804
ComboBox1.List = [index(text(date(2015,row(1:12),1),"[$-807]mmmm"),)]' German - Switzerland 807
ComboBox1.List = [index(text(date(2015,row(1:12),1),"[$-809]mmmm"),)]' English - Great Britain 809
ComboBox1.List = [index(text(date(2015,row(1:12),1),"[$-810]mmmm"),)]' Italian - Switzerland 810
ComboBox1.List = [index(text(date(2015,row(1:12),1),"[$-813]mmmm"),)]' Dutch - Belgium 813
ComboBox1.List = [index(text(date(2015,row(1:12),1),"[$-814]mmmm"),)]' Norwegian - Nynorsk 814
ComboBox1.List = [index(text(date(2015,row(1:12),1),"[$-816]mmmm"),)]' Portuguese - Portugal 816
ComboBox1.List = [index(text(date(2015,row(1:12),1),"[$-818]mmmm"),)]' Romanian - Moldova 818
ComboBox1.List = [index(text(date(2015,row(1:12),1),"[$-819]mmmm"),)]' Russian - Moldova 819
ComboBox1.List = [index(text(date(2015,row(1:12),1),"[$-843]mmmm"),)]' Uzbek - Cyrillic 843
ComboBox1.List = [index(text(date(2015,row(1:12),1),"[$-845]mmmm"),)]' Bengali - Bangladesh 845
ComboBox1.List = [index(text(date(2015,row(1:12),1),"[$-850]mmmm"),)]' Mongolian 850
ComboBox1.List = [index(text(date(2015,row(1:12),1),"[$-1001]mmmm"),)]' Arabic - Libya 1001
ComboBox1.List = [index(text(date(2015,row(1:12),1),"[$-1004]mmmm"),)]' Chinese - Singapore 1004
ComboBox1.List = [index(text(date(2015,row(1:12),1),"[$-1007]mmmm"),)]' German - Luxembourg 1007
ComboBox1.List = [index(text(date(2015,row(1:12),1),"[$-1036]mmmm"),)]' France 1036
ComboBox1.List = [index(text(date(2015,row(1:12),1),"[$-1009]mmmm"),)]' English - Canada 1009
ComboBox1.List = [index(text(date(2015,row(1:12),1),"[$-1401]mmmm"),)]' Arabic - Algeria 1401
ComboBox1.List = [index(text(date(2015,row(1:12),1),"[$-1404]mmmm"),)]' Chinese - Macau SAR 1404
ComboBox1.List = [index(text(date(2015,row(1:12),1),"[$-1407]mmmm"),)]' German - Liechtenstein 1407
ComboBox1.List = [index(text(date(2015,row(1:12),1),"[$-1409]mmmm"),)]' English - New Zealand 1409
ComboBox1.List = [index(text(date(2015,row(1:12),1),"[$-1801]mmmm"),)]' Arabic - Morocco 1801
ComboBox1.List = [index(text(date(2015,row(1:12),1),"[$-1809]mmmm"),)]' English - Ireland 1809
ComboBox1.List = [index(text(date(2015,row(1:12),1),"[$-2001]mmmm"),)]' Arabic - Oman 2001
ComboBox1.List = [index(text(date(2015,row(1:12),1),"[$-2009]mmmm"),)]' English - Jamaica 2009
ComboBox1.List = [index(text(date(2015,row(1:12),1),"[$-2401]mmmm"),)]' Arabic - Yemen 2401
ComboBox1.List = [index(text(date(2015,row(1:12),1),"[$-2409]mmmm"),)]' English - Caribbean 2409
ComboBox1.List = [index(text(date(2015,row(1:12),1),"[$-2801]mmmm"),)]' Arabic - Syria 2801
ComboBox1.List = [index(text(date(2015,row(1:12),1),"[$-2809]mmmm"),)]' English - Belize 2809
ComboBox1.List = [index(text(date(2015,row(1:12),1),"[$-3001]mmmm"),)]' Arabic - Lebanon 3001
ComboBox1.List = [index(text(date(2015,row(1:12),1),"[$-3009]mmmm"),)]' English - Zimbabwe 3009
ComboBox1.List = [index(text(date(2015,row(1:12),1),"[$-3401]mmmm"),)]' Arabic - Kuwait 3401
ComboBox1.List = [index(text(date(2015,row(1:12),1),"[$-3409]mmmm"),)]' English - Phillippines 3409
ComboBox1.List = [index(text(date(2015,row(1:12),1),"[$-3801]mmmm"),)]' Arabic - United Arab Emirates 3801
ComboBox1.List = [index(text(date(2015,row(1:12),1),"[$-4001]mmmm"),)]' Arabic - Qatar 4001
ComboBox1.List = [index(text(date(2015,row(1:12),1),"[$-4009]mmmm"),)]' English - India 4009

5.3 Weekdaynames

5.3.1 weekdaynames extended: sunday to saturday

ComboBox1.List = [index(text(row(1:7),"dddd"),)]

5.3.2 weekdaynames ISO-week extended: monday to sunday

ComboBox1.List = [index(text(row(2:8),"dddd"),)]

5.3.3 weekdaynames abbreviated: sunday to saturday

ComboBox1.List = [index(text(row(1:7),"ddd"),)]

5.3.4 weekdaynames ISO-week abbreviated: monday to sunday

ComboBox1.List = [index(text(row(2:8),"ddd"),)]

5.3.5 weekdaynames ISO-week in several languages

ComboBox1.List = [index(text(row(2:8),"[$-401]dddd"),)]' Arabic - Saudi Arabia 401
ComboBox1.List = [index(text(row(2:8),"[$-402]dddd"),)]' Bulgarian 402
ComboBox1.List = [index(text(row(2:8),"[$-403]dddd"),)]' Catalan 403
ComboBox1.List = [index(text(row(2:8),"[$-404]dddd"),)]' Chinese - Taiwan 404
ComboBox1.List = [index(text(row(2:8),"[$-405]dddd"),)]' Czech 405
ComboBox1.List = [index(text(row(2:8),"[$-406]dddd"),)]' Danish 406
ComboBox1.List = [index(text(row(2:8),"[$-407]dddd"),)]' German - Germany 407
ComboBox1.List = [index(text(row(2:8),"[$-408]dddd"),)]' Greek 408
ComboBox1.List = [index(text(row(2:8),"[$-409]dddd"),)]' English - United States 409
ComboBox1.List = [index(text(row(2:8),"[$-410]dddd"),)]' Italian - Italy 410
ComboBox1.List = [index(text(row(2:8),"[$-411]dddd"),)]' Japanese 411
ComboBox1.List = [index(text(row(2:8),"[$-412]dddd"),)]' Korean 412
ComboBox1.List = [index(text(row(2:8),"[$-413]dddd"),)]' Dutch - Netherlands 413
ComboBox1.List = [index(text(row(2:8),"[$-414]dddd"),)]' Norwegian - Bokml 414
ComboBox1.List = [index(text(row(2:8),"[$-415]dddd"),)]' Polish 415
ComboBox1.List = [index(text(row(2:8),"[$-416]dddd"),)]' Portuguese - Brazil 416
ComboBox1.List = [index(text(row(2:8),"[$-417]dddd"),)]' Raeto-Romance 417
ComboBox1.List = [index(text(row(2:8),"[$-418]dddd"),)]' Romanian - Romania 418
ComboBox1.List = [index(text(row(2:8),"[$-419]dddd"),)]' Russian 419
ComboBox1.List = [index(text(row(2:8),"[$-420]dddd"),)]' Urdu 420
ComboBox1.List = [index(text(row(2:8),"[$-421]dddd"),)]' Indonesian 421
ComboBox1.List = [index(text(row(2:8),"[$-422]dddd"),)]' Ukrainian 422
ComboBox1.List = [index(text(row(2:8),"[$-423]dddd"),)]' Belarusian 423
ComboBox1.List = [index(text(row(2:8),"[$-424]dddd"),)]' Slovenian 424
ComboBox1.List = [index(text(row(2:8),"[$-425]dddd"),)]' Estonian 425
ComboBox1.List = [index(text(row(2:8),"[$-426]dddd"),)]' Latvian 426
ComboBox1.List = [index(text(row(2:8),"[$-427]dddd"),)]' Lithuanian 427
ComboBox1.List = [index(text(row(2:8),"[$-428]dddd"),)]' Tajik 428
ComboBox1.List = [index(text(row(2:8),"[$-429]dddd"),)]' Farsi - Persian 429
ComboBox1.List = [index(text(row(2:8),"[$-430]dddd"),)]' Sesotho (Sutu) 430
ComboBox1.List = [index(text(row(2:8),"[$-431]dddd"),)]' Tsonga 431
ComboBox1.List = [index(text(row(2:8),"[$-432]dddd"),)]' Setsuana 432
ComboBox1.List = [index(text(row(2:8),"[$-433]dddd"),)]' Venda 433
ComboBox1.List = [index(text(row(2:8),"[$-434]dddd"),)]' Xhosa 434
ComboBox1.List = [index(text(row(2:8),"[$-435]dddd"),)]' Zulu 435
ComboBox1.List = [index(text(row(2:8),"[$-436]dddd"),)]' Afrikaans 436
ComboBox1.List = [index(text(row(2:8),"[$-437]dddd"),)]' Georgian 437
ComboBox1.List = [index(text(row(2:8),"[$-438]dddd"),)]' Faroese 438
ComboBox1.List = [index(text(row(2:8),"[$-439]dddd"),)]' Hindi 439
ComboBox1.List = [index(text(row(2:8),"[$-440]dddd"),)]' Kyrgyz - Cyrillic 440
ComboBox1.List = [index(text(row(2:8),"[$-441]dddd"),)]' Swahili 441
ComboBox1.List = [index(text(row(2:8),"[$-442]dddd"),)]' Turkmen 442
ComboBox1.List = [index(text(row(2:8),"[$-443]dddd"),)]' Uzbek - Latin 443
ComboBox1.List = [index(text(row(2:8),"[$-444]dddd"),)]' Tatar 444
ComboBox1.List = [index(text(row(2:8),"[$-445]dddd"),)]' Bengali - India 445
ComboBox1.List = [index(text(row(2:8),"[$-446]dddd"),)]' Punjabi 446
ComboBox1.List = [index(text(row(2:8),"[$-447]dddd"),)]' Gujarati 447
ComboBox1.List = [index(text(row(2:8),"[$-448]dddd"),)]' Oriya 448
ComboBox1.List = [index(text(row(2:8),"[$-449]dddd"),)]' Tamil 449
ComboBox1.List = [index(text(row(2:8),"[$-450]dddd"),)]' Mongolian 450
ComboBox1.List = [index(text(row(2:8),"[$-451]dddd"),)]' Tibetan 451
ComboBox1.List = [index(text(row(2:8),"[$-452]dddd"),)]' Welsh 452
ComboBox1.List = [index(text(row(2:8),"[$-453]dddd"),)]' Khmer 453
ComboBox1.List = [index(text(row(2:8),"[$-454]dddd"),)]' Lao 454
ComboBox1.List = [index(text(row(2:8),"[$-455]dddd"),)]' Burmese 455
ComboBox1.List = [index(text(row(2:8),"[$-456]dddd"),)]' Galician 456
ComboBox1.List = [index(text(row(2:8),"[$-457]dddd"),)]' Konkani 457
ComboBox1.List = [index(text(row(2:8),"[$-458]dddd"),)]' Manipuri 458
ComboBox1.List = [index(text(row(2:8),"[$-459]dddd"),)]' Sindhi 459
ComboBox1.List = [index(text(row(2:8),"[$-460]dddd"),)]' Kashmiri 460
ComboBox1.List = [index(text(row(2:8),"[$-461]dddd"),)]' Nepali 461
ComboBox1.List = [index(text(row(2:8),"[$-462]dddd"),)]' Frisian - Netherlands 462
ComboBox1.List = [index(text(row(2:8),"[$-464]dddd"),)]' Filipino 464
ComboBox1.List = [index(text(row(2:8),"[$-465]dddd"),)]' Divehi; Dhivehi; Maldivian 465
ComboBox1.List = [index(text(row(2:8),"[$-466]dddd"),)]' Edo 466
ComboBox1.List = [index(text(row(2:8),"[$-470]dddd"),)]' Igbo - Nigeria 470
ComboBox1.List = [index(text(row(2:8),"[$-474]dddd"),)]' Guarani - Paraguay 474
ComboBox1.List = [index(text(row(2:8),"[$-476]dddd"),)]' Latin 476
ComboBox1.List = [index(text(row(2:8),"[$-477]dddd"),)]' Somali 477
ComboBox1.List = [index(text(row(2:8),"[$-481]dddd"),)]' Maori 481
ComboBox1.List = [index(text(row(2:8),"[$-801]dddd"),)]' Arabic - Iraq 801
ComboBox1.List = [index(text(row(2:8),"[$-804]dddd"),)]' Chinese - China 804
ComboBox1.List = [index(text(row(2:8),"[$-807]dddd"),)]' German - Switzerland 807
ComboBox1.List = [index(text(row(2:8),"[$-809]dddd"),)]' English - Great Britain 809
ComboBox1.List = [index(text(row(2:8),"[$-810]dddd"),)]' Italian - Switzerland 810
ComboBox1.List = [index(text(row(2:8),"[$-813]dddd"),)]' Dutch - Belgium 813
ComboBox1.List = [index(text(row(2:8),"[$-814]dddd"),)]' Norwegian - Nynorsk 814
ComboBox1.List = [index(text(row(2:8),"[$-816]dddd"),)]' Portuguese - Portugal 816
ComboBox1.List = [index(text(row(2:8),"[$-818]dddd"),)]' Romanian - Moldova 818
ComboBox1.List = [index(text(row(2:8),"[$-819]dddd"),)]' Russian - Moldova 819
ComboBox1.List = [index(text(row(2:8),"[$-843]dddd"),)]' Uzbek - Cyrillic 843
ComboBox1.List = [index(text(row(2:8),"[$-845]dddd"),)]' Bengali - Bangladesh 845
ComboBox1.List = [index(text(row(2:8),"[$-850]dddd"),)]' Mongolian 850
ComboBox1.List = [index(text(row(2:8),"[$-1001]dddd"),)]' Arabic - Libya 1001
ComboBox1.List = [index(text(row(2:8),"[$-1004]dddd"),)]' Chinese - Singapore 1004
ComboBox1.List = [index(text(row(2:8),"[$-1007]dddd"),)]' German - Luxembourg 1007
ComboBox1.List = [index(text(row(2:8),"[$-1036]dddd"),)]' France 1036
ComboBox1.List = [index(text(row(2:8),"[$-1009]dddd"),)]' English - Canada 1009
ComboBox1.List = [index(text(row(2:8),"[$-1401]dddd"),)]' Arabic - Algeria 1401
ComboBox1.List = [index(text(row(2:8),"[$-1404]dddd"),)]' Chinese - Macau SAR 1404
ComboBox1.List = [index(text(row(2:8),"[$-1407]dddd"),)]' German - Liechtenstein 1407
ComboBox1.List = [index(text(row(2:8),"[$-1409]dddd"),)]' English - New Zealand 1409
ComboBox1.List = [index(text(row(2:8),"[$-1801]dddd"),)]' Arabic - Morocco 1801
ComboBox1.List = [index(text(row(2:8),"[$-1809]dddd"),)]' English - Ireland 1809
ComboBox1.List = [index(text(row(2:8),"[$-2001]dddd"),)]' Arabic - Oman 2001
ComboBox1.List = [index(text(row(2:8),"[$-2009]dddd"),)]' English - Jamaica 2009
ComboBox1.List = [index(text(row(2:8),"[$-2401]dddd"),)]' Arabic - Yemen 2401
ComboBox1.List = [index(text(row(2:8),"[$-2409]dddd"),)]' English - Caribbean 2409
ComboBox1.List = [index(text(row(2:8),"[$-2801]dddd"),)]' Arabic - Syria 2801
ComboBox1.List = [index(text(row(2:8),"[$-2809]dddd"),)]' English - Belize 2809
ComboBox1.List = [index(text(row(2:8),"[$-3001]dddd"),)]' Arabic - Lebanon 3001
ComboBox1.List = [index(text(row(2:8),"[$-3009]dddd"),)]' English - Zimbabwe 3009
ComboBox1.List = [index(text(row(2:8),"[$-3401]dddd"),)]' Arabic - Kuwait 3401
ComboBox1.List = [index(text(row(2:8),"[$-3409]dddd"),)]' English - Phillippines 3409
ComboBox1.List = [index(text(row(2:8),"[$-3801]dddd"),)]' Arabic - United Arab Emirates 3801
ComboBox1.List = [index(text(row(2:8),"[$-4001]dddd"),)]' Arabic - Qatar 4001
ComboBox1.List = [index(text(row(2:8),"[$-4009]dddd"),)]' English - India 4009

5.4 Dates

5.4.2 this ISO week

ComboBox1.List = [index(today()-weekday(today(),2)+row(1:7),)]
ComboBox1.List = [index(Text(today()-weekday(today(),2)+row(1:7),"dd-mm-yyyy"),)]

5.4.2 previous ISO week

ComboBox1.List = [index(today()-weekday(today(),2)-7+row(1:7),)]
ComboBox1.List = [index(Text(today()-weekday(today(),2)-7+row(1:7),"dd-mm-yyyy"),)]

5.4.3 next ISO week

ComboBox1.List = [index(today()-weekday(today(),2)+row(8:14),)]
ComboBox1.List = [index(Text(today()-weekday(today(),2)+row(8:14),"dd-mm-yyyy"),)]

5.4.4 this month

ComboBox1.List = [index(date(year(today()),month(today()),0)+row(offset(A1,,,day(date(year(today()),month(today())+1,0)),1)),)]
ComboBox1.List = [index(text(date(year(today()),month(today()),0)+row(offset(A1,,,day(date(year(today()),month(today())+1,0)),1)),"dd-mm-yyyy"),)]

5.4.5 previous month

ComboBox1.List = [index(date(year(today()),month(today())-1,0)+row(offset(A1,,,day(date(year(today()),month(today()),0)),1)),)]
ComboBox1.List = [index(text(date(year(today()),month(today())-1,0)+row(offset(A1,,,day(date(year(today()),month(today()),0)),1)),"dd-mm-yyyy"),)]

5.4.6 next month

ComboBox1.List = [index(date(year(today()),month(today())+1,0)+row(offset(A1,,,day(date(year(today()),month(today())+2,0)),1)),)]
ComboBox1.List = [index(text(date(year(today()),month(today())+1,0)+row(offset(A1,,,day(date(year(today()),month(today())+2,0)),1)),"dd-mm-yyyy"),)]

5.4.7 this ISO year

ComboBox1.List = [index(date(year(today()),1,4)-weekday(date(year(today()),1,4),2)+row(1:365),)]
ComboBox1.List = [index(text(date(year(today()),1,4)-weekday(date(year(today()),1,4),2)+row(1:365),"dd-mm-yyyy"),)]

5.4.8 previous ISO year

ComboBox1.List = [index(date(year(today())-1,1,4)-weekday(date(year(today())-1,1,4),2)+row(1:365),)]
ComboBox1.List = [index(text(date(year(today())-1,1,4)-weekday(date(year(today())-1,1,4),2)+row(1:365),"dd-mm-yyyy"),)]

5.4.9 next ISO year

ComboBox1.List = [index(date(year(today())+1,1,4)-weekday(date(year(today())+1,1,4),2)+row(1:365),)]
ComboBox1.List = [index(text(date(year(today())+1,1,4)-weekday(date(year(today())+1,1,4),2)+row(1:365),"dd-mm-yyyy"),)]

5.4.10 preceding 28 days from today

ComboBox1.List = [index(today()-29+row(1:28),)]
ComboBox1.List = [index(text(today()-29+row(1:28),"dd-mm-yyyy"),)]

5.4.11 next 28 days from today

ComboBox1.List = [index(today()+row(1:28),)]
ComboBox1.List = [index(text(today()+row(1:28),"dd-mm-yyyy"),)]

5.4.12 all sundays this ISO year

ComboBox1.List = [index(date(year(today()),1,4)-weekday(date(year(today()),1,4),2)+7*(row(1:53)-1),)]
ComboBox1.List = [index(text(date(year(today()),1,4)-weekday(date(year(today()),1,4),2)+7*(row(1:53)-1),"dd-mm-yyyy"),)]

5.4.13 all mondays this ISO year

ComboBox1.List = [index(date(year(today()),1,4)-weekday(date(year(today()),1,4),2)+1+7*(row(1:53)-1),)]
ComboBox1.List = [index(text(date(year(today()),1,4)-weekday(date(year(today()),1,4),2)+1+7*(row(1:53)-1),"dd-mm-yyyy"),)]

5.4.14 all tuesdays this ISO year

ComboBox1.List = [index(date(year(today()),1,4)-weekday(date(year(today()),1,4),2)+2+7*(row(1:53)-1),)]
ComboBox1.List = [index(text(date(year(today()),1,4)-weekday(date(year(today()),1,4),2)+2+7*(row(1:53)-1),"dd-mm-yyyy"),)]

5.4.15 all wednesdays this ISO year

ComboBox1.List = [index(date(year(today()),1,4)-weekday(date(year(today()),1,4),2)+3+7*(row(1:53)-1),)]
ComboBox1.List = [index(text(date(year(today()),1,4)-weekday(date(year(today()),1,4),2)+3+7*(row(1:53)-1),"dd-mm-yyyy"),)]

5.4.16 all thursdays this ISO year

ComboBox1.List = [index(date(year(today()),1,4)-weekday(date(year(today()),1,4),2)+4+7*(row(1:53)-1),)]
ComboBox1.List = [index(text(date(year(today()),1,4)-weekday(date(year(today()),1,4),2)+4+7*(row(1:53)-1),"dd-mm-yyyy"),)]

5.4.17 all fridays this ISO year

ComboBox1.List = [index(date(year(today()),1,4)-weekday(date(year(today()),1,4),2)+5+7*(row(1:53)-1),)]
ComboBox1.List = [index(text(date(year(today()),1,4)-weekday(date(year(today()),1,4),2)+5+7*(row(1:53)-1),"dd-mm-yyyy"),)]

5.4.18 all saturdays this ISO year

ComboBox1.List = [index(date(year(today()),1,4)-weekday(date(year(today()),1,4),2)+6+7*(row(1:53)-1),)]
ComboBox1.List = [index(text(date(year(today()),1,4)-weekday(date(year(today()),1,4),2)+6+7*(row(1:53)-1),"dd-mm-yyyy"),)]

5.4.19 wednesday every 6 weeks this ISO year

ComboBox1.List = [index(date(year(today()),1,4)-weekday(date(year(today()),1,4),2)+3+42*row(1:9),)]
ComboBox1.List = [index(text(date(year(today()),1,4)-weekday(date(year(today()),1,4),2)+3+42*row(1:9),"dd-mm-yyyy"),)]
ComboBox1.List = [transpose(text(date(year(today()),1,4)-weekday(date(year(today()),1,4),2)+3+42*row(1:9),"dd-mm-yyyy"))]

5.5 times

5.5.1 every hour in a day

ComboBox1.List = [index(Text((row(1:24)-1)/24,"hh:mm"),)]
ComboBox1.List = [transpose(Text((row(1:24)-1)/24,"hh:mm"))]

5.5.2 every half an hour in a day

ComboBox1.List = [index(Text((row(1:48)-1)/48,"hh:mm"),)]
ComboBox1.List = [transpose(Text((row(1:48)-1)/48,"hh:mm"))]

5.5.3 every quarter of an hour in a day

ComboBox1.List = [index(Text((row(1:96)-1)/96,"hh:mm"),)]
ComboBox1.List = [transpose(Text((row(1:96)-1)/96,"hh:mm"))]

5.5.4 every 10 minutes in a day

ComboBox1.List = [index(Text((row(1:144)-1)/144,"hh:mm"),)]
ComboBox1.List = [transpose(Text((row(1:144)-1)/144,"hh:mm"))]

5.5.5 every hour starting at 08:00 and ending at 18:00

ComboBox1.List = [index(Text(row(8:18)/24,"hh:mm"),)]
ComboBox1.List = [transpose(Text(row(8:18)/24,"hh:mm"))]

5.5.6 every 10 seconds between 20:12:00 and 20:14:00

ComboBox1.List = [index(Text(row(7272:7284)/8640,"hh:mm:ss"),)]
ComboBox1.List = [transpose(row(7272:7284)/8640,"hh:mm:ss"))]

5.6 Math

ComboBox1.List = [index(2^row(1:20),)]
ComboBox1.List = [index(Row(1:20)^2,)]
ComboBox1.List = [index(64^1/row(1:5),)]