Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Late to the game here (mwolfe02 answered this a year ago!) but the issue is still real, there are alternative approaches worth investigating, and StackOverflow is the place to find them: so here's my own answer...</p> <p>I got tripped up by VBA.IsDate() on this very issue a few years ago, and coded up an extended function to cover cases that VBA.IsDate() handles badly. The worst one is that floats and integers return FALSE from IsDate, even though date serials are frequently passed as Doubles (for DateTime) and Long Integers (for dates).</p> <p>A point to note: your implementation might not require the ability to check array variants. If not, feel free to strip out the code in the indented block that follows <code>Else ' Comment this out if you don't need to check array variants</code>. However, you should be aware that some third-party systems (including realtime market data clients) return their data in arrays, even single data points.</p> <p>More information is in the code comments. </p> <p><strong>Here's the Code:</strong></p> <pre><code>Public Function IsDateEx(TestDate As Variant, Optional LimitPastDays As Long = 7305, Optional LimitFutureDays As Long = 7305, Optional FirstColumnOnly As Boolean = False) As Boolean 'Attribute IsDateEx.VB_Description = "Returns TRUE if TestDate is a date, and is within ± 20 years of the system date. 'Attribute IsDateEx.VB_ProcData.VB_Invoke_Func = "w\n9" Application.Volatile False On Error Resume Next ' Returns TRUE if TestDate is a date, and is within ± 20 years of the system date. ' This extends VBA.IsDate(), which returns FALSE for floating-point numbers and integers ' even though the VBA Serial Date is a Double. IsDateEx() returns TRUE for variants that ' can be parsed into string dates, and numeric values with equivalent date serials. All ' values must still be ±20 years from SysDate. Note: locale and language settings affect ' the validity of day- and month names; and partial date strings (eg: '01 January') will ' be parsed with the missing components filled-in with system defaults. ' Optional parameters LimitPastDays/LimitFutureDays vary the default ± 20 years boundary ' Note that an array variant is an acceptable input parameter: IsDateEx will return TRUE ' if all the values in the array are valid dates: set FirstColumnOnly:=TRUE if you only ' need to check the leftmost column of a 2-dimensional array. ' * THIS CODE IS IN THE PUBLIC DOMAIN ' * ' * Author: Nigel Heffernan, May 2005 ' * http://excellerando.blogspot.com/ ' * ' * ' * ********************************* Dim i As Long Dim j As Long Dim k As Long Dim jStart As Long Dim jEnd As Long Dim dateFirst As Date Dim dateLast As Date Dim varDate As Variant dateFirst = VBA.Date - LimitPastDays dateLast = VBA.Date + LimitFutureDays IsDateEx = False If TypeOf TestDate Is Excel.Range Then TestDate = TestDate.Value2 End If If VarType(TestDate) &lt; vbArray Then If IsDate(TestDate) Or IsNumeric(TestDate) Then If (dateLast &gt; TestDate) And (TestDate &gt; dateFirst) Then IsDateEx = True End If End If Else ' Comment this out if you don't need to check array variants k = ArrayDimensions(TestDate) Select Case k Case 1 IsDateEx = True For i = LBound(TestDate) To UBound(TestDate) If IsDate(TestDate(i)) Or IsNumeric(TestDate(i)) Then If Not ((dateLast &gt; CVDate(TestDate(i))) And (CVDate(TestDate(i)) &gt; dateFirst)) Then IsDateEx = False Exit For End If Else IsDateEx = False Exit For End If Next i Case 2 IsDateEx = True jStart = LBound(TestDate, 2) If FirstColumnOnly Then jEnd = LBound(TestDate, 2) Else jEnd = UBound(TestDate, 2) End If For i = LBound(TestDate, 1) To UBound(TestDate, 1) For j = jStart To jEnd If IsDate(TestDate(i, j)) Or IsNumeric(TestDate(i, j)) Then If Not ((dateLast &gt; CVDate(TestDate(i, j))) And (CVDate(TestDate(i, j)) &gt; dateFirst)) Then IsDateEx = False Exit For End If Else IsDateEx = False Exit For End If Next j Next i Case Is &gt; 2 ' Warning: For... Each enumerations are SLOW For Each varDate In TestDate If IsDate(varDate) Or IsNumeric(varDate) Then If Not ((dateLast &gt; CVDate(varDate)) And (CVDate(varDate) &gt; dateFirst)) Then IsDateEx = False Exit For End If Else IsDateEx = False Exit For End If Next varDate End Select End If End Function </code></pre> <p><b>A Tip for people still using Excel 2003:</b></p> <p>If you (or your users) are going to call IsDateEx() from a worksheet, put these two lines in, immediately below the function header, <em>using a text editor in an exported .bas file and reimporting the file, because VB Attributes are useful, but they are not accessible to the code editor in Excel's VBA IDE</em>:</p> <pre><code>Attribute IsDateEx.VB_Description = "Returns TRUE if TestDate is a date, and is within ± 20 years of the system date.\r\nChange the defaulte default ± 20 years boundaries by setting values for LimitPastDays and LimitFutureDays\r\nIf you are checking an array of dates, ALL the values will be tested: set FirstColumnOnly TRUE to check the leftmost column only." </code></pre> <p>That's all one line: watch out for line-breaks inserted by the browser! ...And this line, which puts isDateEX into the function Wizard in the 'Information' category, alongside ISNUMBER(), ISERR(), ISTEXT() and so on:</p> <pre><code>Attribute IsDateEx.VB_ProcData.VB_Invoke_Func = "w\n9" </code></pre> <p>Use "w\n2" if you prefer to see it under the Date &amp; Time functions: beats hell outta losing it in the morass of 'Used Defined' functions from your own code, and all those third-party add-ins developed by people who don't do <em>quite</em> enough to help occasional users.</p> <p>I have no idea whether this still works in Office 2010.</p> <p><b>Also, you might need the source for ArrayDimensions:</b></p> <p>This API declaration is required in the module header:</p> <pre><code>Private Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" _ (Destination As Any, _ Source As Any, _ ByVal Length As Long) </code></pre> <p>…And here's the function itself: </p> <pre><code>Private Function ArrayDimensions(arr As Variant) As Integer '----------------------------------------------------------------- ' will return: ' -1 if not an array ' 0 if an un-dimmed array ' 1 or more indicating the number of dimensions of a dimmed array '----------------------------------------------------------------- ' Retrieved from Chris Rae's VBA Code Archive - http://chrisrae.com/vba ' Code written by Chris Rae, 25/5/00 ' Originally published by R. B. Smissaert. ' Additional credits to Bob Phillips, Rick Rothstein, and Thomas Eyde on VB2TheMax Dim ptr As Long Dim vType As Integer Const VT_BYREF = &amp;H4000&amp; 'get the real VarType of the argument 'this is similar to VarType(), but returns also the VT_BYREF bit CopyMemory vType, arr, 2 'exit if not an array If (vType And vbArray) = 0 Then ArrayDimensions = -1 Exit Function End If 'get the address of the SAFEARRAY descriptor 'this is stored in the second half of the 'Variant parameter that has received the array CopyMemory ptr, ByVal VarPtr(arr) + 8, 4 'see whether the routine was passed a Variant 'that contains an array, rather than directly an array 'in the former case ptr already points to the SA structure. 'Thanks to Monte Hansen for this fix If (vType And VT_BYREF) Then ' ptr is a pointer to a pointer CopyMemory ptr, ByVal ptr, 4 End If 'get the address of the SAFEARRAY structure 'this is stored in the descriptor 'get the first word of the SAFEARRAY structure 'which holds the number of dimensions '...but first check that saAddr is non-zero, otherwise 'this routine bombs when the array is uninitialized If ptr Then CopyMemory ArrayDimensions, ByVal ptr, 2 End If End Function </code></pre> <p>Please keep the acknowledgements in your source code: as you progress in your career as a developer, you will come to appreciate your own contributions being acknowledged.</p> <p>Also: I would advise you to keep that declaration private. If you must make it a public Sub in another module, insert the <code>Option Private Module</code> statement in the module header. You really don't want your users calling any function with CopyMemoryoperations and pointer arithmetic.</p>
    singulars
    1. This table or related slice is empty.
    1. POIsDate function returns unexpected results
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USMike Mertsock
    1. USNigel Heffernan
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. CO+1 Looks good. I just posted another question inspired by your answer: [Add user-defined function (UDF) to built-in category in Excel](http://stackoverflow.com/q/11145497/154439).
      singulars
      1. PO
      1. USmwolfe02
    2. COSome of the code in your answer is being truncated, but I was able to pull it from the source. Works great with dates that are Strings of the serialized dates.
      singulars
      1. PO
      1. USwilsjd
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload