xlf | GetDVEnumName function


GetDVEnumName - about the function


Description: returns the Data Validation Allow TypeName as a string

Syntax: GetDVEnumName(typeValue).

Arguments: typeValue (required): the TypeValue as an integer in the range 0 to 7.


GetDVEnumName - the VBA code


In this example, the xlDVtype array is declared as a user defined type in the module declarations section, see code 1.



Code 1: Type xlDVtype declares a custom data type in the module declarations 
Option Explicit

Type xlDVtype
    xlDVtypeValue As Integer
    xlDVtypeName As String
End Type

The array arrxlDVtype is populated in rows 5 to 20 in code 2. xlDVtypeValue is the enumeration, and xlDVtypeName is the return string. An If...Then construct (rows 22 to 25) is used to return the Name to the function.



Code 2: Function GetDVEnumName returns the string value of the data validation type name from the associated enumeration 
Function GetDVEnumName(typeValue As Integer) As String
Dim arrxlDVtype(0 To 7) As xlDVtype
Dim i As Integer
Dim j As Integer
    arrxlDVtype(0).xlDVtypeValue = xlValidateInputOnly
    arrxlDVtype(0).xlDVtypeName = "xlValidateInputOnly"
    arrxlDVtype(1).xlDVtypeValue = xlValidateWholeNumber
    arrxlDVtype(1).xlDVtypeName = "xlValidateWholeNumber"
    arrxlDVtype(2).xlDVtypeValue = xlValidateDecimal
    arrxlDVtype(2).xlDVtypeName = "xlValidateDecimal"
    arrxlDVtype(3).xlDVtypeValue = xlValidateList
    arrxlDVtype(3).xlDVtypeName = "xlValidateList"
    arrxlDVtype(4).xlDVtypeValue = xlValidateDate
    arrxlDVtype(4).xlDVtypeName = "xlValidateDate"
    arrxlDVtype(5).xlDVtypeValue = xlValidateTime
    arrxlDVtype(5).xlDVtypeName = "xlValidateTime"
    arrxlDVtype(6).xlDVtypeValue = xlValidateTextLength
    arrxlDVtype(6).xlDVtypeName = "xlValidateTextLength"
    arrxlDVtype(7).xlDVtypeValue = xlValidateCustom
    arrxlDVtype(7).xlDVtypeName = "xlValidateCustom"
For i = LBound(arrxlDVtype) To UBound(arrxlDVtype)
    If arrxlDVtype(i).xlDVtypeValue = typeValue Then
        GetDVEnumName = arrxlDVtype(i).xlDVtypeName
        Exit For
    End If
Next i
End Function