I am new to VBA and I am working on a module to read in data from a spreadsheet and calculate values based on dates from the spreadsheet. I read in the variables as a String and then am currently changing the values to a Date using CDate. However I just ran across DateValue and I was wondering what the difference between the two functions were and which one is the better one to use.
Excel – Difference between DateValue and CDate in VBA
excelvba
Related Solutions
You can use a library called ExcelLibrary. It's a free, open source library posted on Google Code:
This looks to be a port of the PHP ExcelWriter that you mentioned above. It will not write to the new .xlsx format yet, but they are working on adding that functionality in.
It's very simple, small and easy to use. Plus it has a DataSetHelper that lets you use DataSets and DataTables to easily work with Excel data.
ExcelLibrary seems to still only work for the older Excel format (.xls files), but may be adding support in the future for newer 2007/2010 formats.
You can also use EPPlus, which works only for Excel 2007/2010 format files (.xlsx files). There's also NPOI which works with both.
There are a few known bugs with each library as noted in the comments. In all, EPPlus seems to be the best choice as time goes on. It seems to be more actively updated and documented as well.
Also, as noted by @АртёмЦарионов below, EPPlus has support for Pivot Tables and ExcelLibrary may have some support (Pivot table issue in ExcelLibrary)
Here are a couple links for quick reference:
ExcelLibrary - GNU Lesser GPL
EPPlus - GNU (LGPL) - No longer maintained
EPPlus 5 - Polyform Noncommercial - Starting May 2020
NPOI - Apache License
Here some example code for ExcelLibrary:
Here is an example taking data from a database and creating a workbook from it. Note that the ExcelLibrary code is the single line at the bottom:
//Create the data set and table
DataSet ds = new DataSet("New_DataSet");
DataTable dt = new DataTable("New_DataTable");
//Set the locale for each
ds.Locale = System.Threading.Thread.CurrentThread.CurrentCulture;
dt.Locale = System.Threading.Thread.CurrentThread.CurrentCulture;
//Open a DB connection (in this example with OleDB)
OleDbConnection con = new OleDbConnection(dbConnectionString);
con.Open();
//Create a query and fill the data table with the data from the DB
string sql = "SELECT Whatever FROM MyDBTable;";
OleDbCommand cmd = new OleDbCommand(sql, con);
OleDbDataAdapter adptr = new OleDbDataAdapter();
adptr.SelectCommand = cmd;
adptr.Fill(dt);
con.Close();
//Add the table to the data set
ds.Tables.Add(dt);
//Here's the easy part. Create the Excel worksheet from the data set
ExcelLibrary.DataSetHelper.CreateWorkbook("MyExcelFile.xls", ds);
Creating the Excel file is as easy as that. You can also manually create Excel files, but the above functionality is what really impressed me.
While Issun has answered your question as asked I had enough detail that I wanted to post to provide a further answer as opposed to comment.
The string versions are significantly faster ~ approx 10-30% depending on the data type from my testing over the years. While this is not normally noticeable, it is a performance difference when running on large datasets. So for me it's a no-brainer to use the string rather than variant version.
The sample below works on strings so it shows a speed advantage at the higher end of this range
I have used these functions in combination with variant arrays in both of my public addins as these programs are typically used on entire worksheets even entire workbooks
This link is an excellent reference, for your question, and well beyond
- Comparing in
vbBinaryCompare
rather thanvbTextCompare
- Optimising empty strings
- Testing before replacing
- Using built in constants,
VbNullString
is faster than""
, although both will miss a cell that contains'
whereasIsEmpty
picks this up - Optimising Loops (break
AND
into two separateIF
s to give an early escape) - Optimising If tests to return the most common Boolean result first rather than run through the
Else
path (ie aFalse
test may be more appropriate thanTrue
) Using
Mid$
on the left hand side of an assignment. From hidden features of VBASub QuickTimer1() Dim lngRow As Long Dim dbTime As Double Dim strSample As String Dim strShort As String strSample = "random string" dbTime = Timer() For lngRow = 1 To 50000000 strShort = Left$(strSample, 6) Next lngRow MsgBox Timer() - dbTime End Sub Sub QuickTimer2() Dim lngRow As Long Dim dbTime As Double Dim strSample As String Dim strShort As String strSample = "random string" dbTime = Timer() For lngRow = 1 To 50000000 strShort = Left(strSample, 6) Next lngRow MsgBox Timer() - dbTime End Sub
Best Answer
DateValue
will return only the date.CDate
will preserve the date and time:Similarly you can use
TimeValue
to return only the time portion:Also, as guitarthrower says,
DateValue
(andTimeValue
) will only acceptString
parameters, whileCDate
can handle numbers as well. To emulate these functions for numeric types, useCDate(Int(num))
andCDate(num - Int(num))
.