none
How to Call a Function (VBA) RRS feed

  • Question

  • Hi,

    Could you recall me how to call a Function: The calling proc send a string. The function returns an error : "Argument not optional". 

    Any idea ? Thanks for your help

    Sub SaiPasFaire()

    Dim Arg As String
    Arg = "ABC group"       '
    Call RetreiveDataCDP(Arg)
    Debug.Print RetreiveDataCDP 'Compile error: Argument not optional
    End Sub
    ---------------------------
    Function RetreiveDataCDP(ByVal Arg$) As String
        Dim docProps As Office.DocumentProperties
        Dim docProp As Office.DocumentProperty
        
        Set docProps = ActiveProject.CustomDocumentProperties
        
        For Each docProp In docProps
            If (docProp.Name = Arg) Then
                RetreiveDataCDP = docProp.Value
            End If
        Next docProp
    End Function


    • Edited by WLID1966 Sunday, November 6, 2016 7:14 PM
    Sunday, November 6, 2016 6:20 PM

Answers

  • If you want to see the return value set a string variable to it:

    Dim strOutput as String

    strOutput = RetrieveDate(Arg) ' function arguments have to be in parens

    Debug.Print strOutput

    Also,

    I think that you can use the Property name as an index into the DocumentProperties collection.

    • Proposed as answer by John - Project Monday, November 7, 2016 2:01 AM
    • Marked as answer by WLID1966 Monday, November 7, 2016 9:31 AM
    Monday, November 7, 2016 1:03 AM
  • WLID1966,

    Bill already answered your basic question. As far as your existing code is concerned, try this:

    Sub SaiPasFaire()
    Dim Arg As String
    Arg = "ABC group"
    Debug.Print RetreiveDataCDP(Arg)
    End Sub

    Function RetreiveDataCDP(ByVal Arg$) As String
        Dim docProps As Office.DocumentProperties
        Dim docProp As Office.DocumentProperty
        
        Set docProps = ActiveProject.CustomDocumentProperties
        
        For Each docProp In docProps
            If (docProp.Name = Arg) Then
                RetreiveDataCDP = docProp.Value
            End If
        Next docProp
    End Function

    John


    • Edited by John - Project Monday, November 7, 2016 2:01 AM credit
    • Marked as answer by WLID1966 Monday, November 7, 2016 9:31 AM
    Monday, November 7, 2016 1:48 AM

All replies

  • If you want to see the return value set a string variable to it:

    Dim strOutput as String

    strOutput = RetrieveDate(Arg) ' function arguments have to be in parens

    Debug.Print strOutput

    Also,

    I think that you can use the Property name as an index into the DocumentProperties collection.

    • Proposed as answer by John - Project Monday, November 7, 2016 2:01 AM
    • Marked as answer by WLID1966 Monday, November 7, 2016 9:31 AM
    Monday, November 7, 2016 1:03 AM
  • WLID1966,

    Bill already answered your basic question. As far as your existing code is concerned, try this:

    Sub SaiPasFaire()
    Dim Arg As String
    Arg = "ABC group"
    Debug.Print RetreiveDataCDP(Arg)
    End Sub

    Function RetreiveDataCDP(ByVal Arg$) As String
        Dim docProps As Office.DocumentProperties
        Dim docProp As Office.DocumentProperty
        
        Set docProps = ActiveProject.CustomDocumentProperties
        
        For Each docProp In docProps
            If (docProp.Name = Arg) Then
                RetreiveDataCDP = docProp.Value
            End If
        Next docProp
    End Function

    John


    • Edited by John - Project Monday, November 7, 2016 2:01 AM credit
    • Marked as answer by WLID1966 Monday, November 7, 2016 9:31 AM
    Monday, November 7, 2016 1:48 AM