Getting & Setting Date/Time in Shapes

[Visio Guy]

I've been getting questions about working with dates in Visio shapes, using VBA.

I worked through some code, and thought I'd share it with you here.

Setup:

1. Create a shape that has the Shape Data field: Prop.date
2. Make sure the field has the "Date" format
3. If you create the field via the User-interface, make sure you change the corresponding ShapeSheet row-name to Prop.date.

Shape Data fields that work with the date format seem to always have the DATETIME formula as a value. The argument is a serial date, for instance Prop.date = DATETIME(39647.67836)

In VBA, you can get the current date/time with the NOW() function, but VBA always tries to format it nicely. In order to get a serial date, you can anti-format it yourself, like this:

Debug.Print  Format(Now(), "00000.00000")

This anti-formatted, serial date can then be used in an expression to set the Shape Data fields formula.

When you set Visio cells, you can either set values or formulas. In this case, it's better to work with formulas.

Here's some play-around code that might be instructive:

Code Select Expand


Sub GetDate()

  ' Get a shape that has a Prop.date
  Dim shp As Visio.Shape
  Set shp = Visio.ActivePage.Shapes.Item(1)
 
  ' Get Today from VBA:
  Dim serialDate As String
 
  ' Format it as a serial date:
  serialDate = Format(Now(), "00000.00000")
 
  ' Set the shape's data field with this value by making a
  ' formula that looks like this: DATETIME(39647.67457)
  shp.Cells("Prop.date").FormulaForceU = "DATETIME(" & serialDate & ")"
 
  ' Output the date in various forms:
  With shp.Cells("Prop.date")
 
    Debug.Print "Get the date from the shape by various means:"
    Debug.Print
    Debug.Print "ResultIU = " & .ResultIU
    Debug.Print "Result(visDate) = " & .Result(Visio.VisUnitCodes.visDate)
    Debug.Print "Result(visNoCast) = " & .Result(Visio.VisUnitCodes.visNoCast)
    Debug.Print "ResultStr(visNoCast) = " & .Result(Visio.VisUnitCodes.visNoCast)
    Debug.Print "Formula = " & .Formula
    Debug.Print
    Debug.Print "Format the result from the Visio shape:"
    Debug.Print Format(.Result(visNoCast), "YYYY.MM.DD - HH:mm:ss")

  End With
 
  'Sample output:
  '  Get the date from the shape by various means:
  '
  '  ResultIU             = 39647.67836
  '  Result(visDate)      = 39647.67836
  '  Result(visNoCast)    = 39647.67836
  '  ResultStr(visNoCast) = 39647.67836
  '  Formula              = DateTime(39647.67836)
  '
  '  Format the result from the Visio shape:
  '  2008.07.18 - 16:16:50
 
End Sub


[vojo]

why not use the functions provided for that in the shapesheets?

[Visio Guy]

Huh? We're talking about going between ShapeSheet and VBA. The questions related to reading/writing date values to and from the ShapeSheet.

[vojo]

dont need VBA to do this...that is my only point (unless you are doing something really odd with dates and such).

[Visio Guy]

The people asking the question are trying to programmatically get/set date info in shapes, using VBA. VBA is the prerequisite in this case.

[aledlund]

one of the places this needs to be understood is when your working with the timeline addin and setting fields programmatically.
al