locked
adding and removing function categories RRS feed

  • Question

  • I have added UDF functions using

    Application.MacroOptions macro:="'add-in name.xla'!FunctionName", _
            Category:="My Functions"

    1) how do you remove a category

    2) Does Excel cache the function categories? I

    I removed the calls to MacroOpitions. Saved the .xla. Disable the .xla.

    The "My Functions" category does not appear - correct behavior.

    Closed Excel. Re-open Excel. ReEnable the .xla (File -> options-> Add-ins--> Excel Add-ins-->

    Browse to  my .xla and select. "A file named 'c:\temp\myUDFs.xla' already exists in this location. Do you wnt to replace it?"  --> Yes

    My UDFs appear under the "My Functions" category. Without any MacroOptions code execution. The code does not exist in the .xla

     the category and functions  do not appear - this is expected.

    If I remove the code from my .xla that adds the

    Monday, October 8, 2012 2:03 PM

Answers

  • Take look on that:

    Private Sub workbook_open() 
    Dim sDescripion_AZP As String
    sDescripion_AZP = "description in function window."
    
    With Application
        .MacroOptions Macro:="Akronim_z_przystankami", _
            Description:=sDescripion_AZP, Category:=9 'you can use 4th parameter but is not compatible with earlier versions
    End With
    End Sub
    
    Private Sub auto_close()
    With Application
        .MacroOptions Macro:="Akronim_z_przystankami", Description:=Empty, Category:=Empty
    End With
    End Sub
    
    Public Function Akronim_z_przystankami(tekst$, Optional opcja% = 0)
       Dim i&, znak$, tempz$, cyfra As Boolean
    
       znak = Mid(tekst, 1, 1)
       If znak <> Chr(32) Then
          Akronim_z_przystankami = Akronim_z_przystankami & znak
       End If
    
       For i = 1 To Len(tekst)
          znak = Mid(tekst, i, 1)
          If znak = Chr(32) Then
             tempz = Mid(tekst, i + 1, 1)
             If tempz <> Chr(32) Then
                Akronim_z_przystankami = Akronim_z_przystankami & tempz
                cyfra = True
             End If
          End If
    
          If znak = Chr(46) Or znak = Chr(44) Or znak = Chr(45) Then
             Akronim_z_przystankami = Akronim_z_przystankami & znak & Mid(tekst, i + 1, 1)
             cyfra = True
          End If
    
          For II = 48 To 57
    
             If znak = Chr(II) Then   'if values
                If cyfra = True Then
                   cyfra = False
                Else
                   Akronim_z_przystankami = Akronim_z_przystankami & znak
                End If
    
                tempz = Mid(tekst, i + 1, 1)
                If tempz = Chr(32) Then
                   Akronim_z_przystankami = Akronim_z_przystankami & tempz
                End If
             End If
          Next
    
       Next
       select case opcja
            case 0: Akronim_z_przystankami = Akronim_z_przystankami
            case 1: Akronim_z_przystankami = UCase(Akronim_z_przystankami)
            case 2: Akronim_z_przystankami = LCase(Akronim_z_przystankami)
            case else: MsgBox "Write 0,1 or 2", vbExclamation, "VBATools.pl"
       End select
    End Function

    This is one of my solutions using category ;]


    Oskar Shon, Office System MVP

    Press if Helpful; Answer when a problem solved

    • Marked as answer by E Benson Tuesday, October 9, 2012 1:38 PM
    Monday, October 8, 2012 5:31 PM
    Answerer

All replies

  • The details are stored in a hidden header of the function. Export the code module with the macro and open the *.bas in a text editor. Look for "Attribute".

    As to what happened when you tried to add the xla to the addins collection, I guess a file must have already existed in the collection with that name and path, IOW had been added before. However whether or not the file exists in the Addins collection is unrelated to anything to do with MacroOptions. You can simply open an xla from file, it doesn't need to exist in the addins collection.

    Peter Thornton

    Monday, October 8, 2012 4:15 PM
  • Take look on that:

    Private Sub workbook_open() 
    Dim sDescripion_AZP As String
    sDescripion_AZP = "description in function window."
    
    With Application
        .MacroOptions Macro:="Akronim_z_przystankami", _
            Description:=sDescripion_AZP, Category:=9 'you can use 4th parameter but is not compatible with earlier versions
    End With
    End Sub
    
    Private Sub auto_close()
    With Application
        .MacroOptions Macro:="Akronim_z_przystankami", Description:=Empty, Category:=Empty
    End With
    End Sub
    
    Public Function Akronim_z_przystankami(tekst$, Optional opcja% = 0)
       Dim i&, znak$, tempz$, cyfra As Boolean
    
       znak = Mid(tekst, 1, 1)
       If znak <> Chr(32) Then
          Akronim_z_przystankami = Akronim_z_przystankami & znak
       End If
    
       For i = 1 To Len(tekst)
          znak = Mid(tekst, i, 1)
          If znak = Chr(32) Then
             tempz = Mid(tekst, i + 1, 1)
             If tempz <> Chr(32) Then
                Akronim_z_przystankami = Akronim_z_przystankami & tempz
                cyfra = True
             End If
          End If
    
          If znak = Chr(46) Or znak = Chr(44) Or znak = Chr(45) Then
             Akronim_z_przystankami = Akronim_z_przystankami & znak & Mid(tekst, i + 1, 1)
             cyfra = True
          End If
    
          For II = 48 To 57
    
             If znak = Chr(II) Then   'if values
                If cyfra = True Then
                   cyfra = False
                Else
                   Akronim_z_przystankami = Akronim_z_przystankami & znak
                End If
    
                tempz = Mid(tekst, i + 1, 1)
                If tempz = Chr(32) Then
                   Akronim_z_przystankami = Akronim_z_przystankami & tempz
                End If
             End If
          Next
    
       Next
       select case opcja
            case 0: Akronim_z_przystankami = Akronim_z_przystankami
            case 1: Akronim_z_przystankami = UCase(Akronim_z_przystankami)
            case 2: Akronim_z_przystankami = LCase(Akronim_z_przystankami)
            case else: MsgBox "Write 0,1 or 2", vbExclamation, "VBATools.pl"
       End select
    End Function

    This is one of my solutions using category ;]


    Oskar Shon, Office System MVP

    Press if Helpful; Answer when a problem solved

    • Marked as answer by E Benson Tuesday, October 9, 2012 1:38 PM
    Monday, October 8, 2012 5:31 PM
    Answerer
  • Thank you for the replies. I used this code within Workbook_Close

    With Application
        .MacroOptions Macro:="MyMacro", Description:=Empty, Category:=Empty
    End With

    Tuesday, October 9, 2012 1:54 PM
  • My welcome ;]

    Regards


    Oskar Shon, Office System MVP

    Press if Helpful; Answer when a problem solved

    Tuesday, October 9, 2012 2:01 PM
    Answerer