Inserting descriptions to UDF's

[dicktimmerman]

Hi, Some months ago I posted the question "how to add descriptions to a UDF.
I'm not "Constantly working on this but today I took the job again.
Till thusfar I came to this code:

Const Pi = 3.14159265359
Const G = 9.81

Function Stress(Optional Force As Double = 0, Optional Area As Double = 0)
    
        Stress = Force / Area
        
End Function
Function Ixx(Section As Single, Optional Width As Double = 0, Optional Height As Double = 0, Optional Diameter As Double = 0) As Double
    
    Select Case Section
        Case "1"
            Ixx = (Width * (Height ^ 3)) / 12
        Case "2"
            Ixx = (Pi * ((Diameter / 2) ^ 4)) / 4
        Case "3"
            Ixx = (Pi * ((Diameter / 2) ^ 4)) / 8
        Case "4"
            Ixx = (Pi * ((Diameter / 2) ^ 4)) / 16
        Case "5"
            Ixx = (Pi * Width * (Height ^ 3)) / 4
        Case "6"
            Ixx = (Width * (Height ^ 3)) / 36
        End Select

End Function
Function Iyy(Section As Single, Optional Width As Double = 0, Optional Height As Double = 0, Optional Diameter As Double = 0) As Double

    Select Case Section
        Case "1"
            Iyy = (Height * (Width ^ 3)) / 12
        Case "2"
            Iyy = (Pi * ((Diameter / 2) ^ 4)) / 4
        Case "3"
            Iyy = (Pi * ((Diameter / 2) ^ 4)) / 8
        Case "4"
            Iyy = (Pi * ((Diameter / 2) ^ 4)) / 16
        Case "5"
            Iyy = (Pi * Height * (Width ^ 3)) / 4
        Case "6"
            Iyy = (Height * (Width ^ 3)) / 36
        End Select
    
End Function

Function Wxx(Section As Single, Optional Width As Double = 0, Optional Height As Double = 0, Optional Diameter As Double = 0) As Double
    
    Select Case Section
        Case "1"
            Wxx = (Width * (Height ^ 2)) / 6
        Case "2"
            Wxx = (Pi * (Diameter ^ 3)) / 32
        Case "3"
            Wxx = (Pi * (Diameter ^ 3)) / 64
        Case "4"
            Wxx = (Pi * (Diameter ^ 3)) / 128
        Case "5"
            Wxx = (Pi * Width * (Height ^ 3)) / 4
        Case "6"
            Wxx = (Width * (Height ^ 3)) / 36
        End Select
    
End Function
Function Wyy(Section As Single, Optional Width As Double = 0, Optional Height As Double = 0, Optional Diameter As Double = 0) As Double

    Select Case Section
        Case "1"
            Wyy = (Height * (Width ^ 2)) / 6
        Case "2"
            Wyy = (Pi * (Diameter ^ 3)) / 32
        Case "3"
            Wyy = (Pi * (Diameter ^ 3)) / 64
        Case "4"
            Wyy = (Pi * (Diameter ^ 3)) / 128
        Case "5"
            Wyy = (Pi * Height * (Width ^ 3)) / 4
        Case "6"
            Wyy = (Heigt * (Width ^ 3)) / 36
        End Select
    
End Function
Function BendStress(Optional Mb As Double = 0, Optional Wb As Double = 0) As Double

    BendStress = Mb / Wb

End Function
Function Elongation(Optional Force As Double = 0, Optional Length As Double = 0, Optional Area As Double = 0, Optional E As Double = 0) As Double

    Elongation = (Force * Length) / (E * Area)
    
End Function
Function BendDeflection(Optional BendingMoment As Double = 0, Optional E As Double = 0, Optional Ixx As Double = 0) As Double

    BendDeflection = BendingMoment / (E * Ixx)
    
End Function
Function BucklingForce(Optional E As Double = 0, Optional I As Double = 0, Optional K As Double = 0, Optional L As Double = 0)
    BucklingForce = ((Pi ^ 2) * E * I) / ((K * L) ^ 2)
End Function
Function SumOfMoments(Startpunt As String, ParamArray Forces() As Variant)
    Dim intI As Single
    
    'Debug.Print
    For intI = 0 To UBound(Forces())
    Debug.Print " "; Forces(intI)
    Next intI
    
End Function
Function FrictionForce(Fn As Double, Optional µs As Double, Optional µk As Double)
    
    If µs <> "" Then
        FrictionForce = µs * Fn
    ElseIf µk <> "" Then
        FrictionForce = µk * Fn
    End If
    
    
    
End Function
Sub AddDescription()
    
    Application.MacroOptions Macro:="Stress", Category:="Structural Engineering", _
    Description:="Calculates the actual stress due to tension, compression, shear or torsion" _
    & vbCrLf & "Force = Enter Force in N (do not use kg) (0 if not supplied)" _
    & vbCrLf & "Area = second input (0 if not supplied)" _

    Application.MacroOptions Macro:="BendStress", Category:="Structural Engineering", _
    Description:="Calculates the stress in a body due to bending" _
    & vbCrLf & "Mb = Enter bending moment in Nm" _
    & vbCrLf & "Wb = Enter the section modulus in mm³"
    
    Application.MacroOptions Macro:="Ixx", Category:="Structural Engineering", _
    Description:="Calculates the 2nd moment of inertia of a section over the X-X axis" _
    & vbCrLf & "Section = 1 for " & Chr(34) & "rectangular" & Chr(34) & ", 2 for " & Chr(34) & "circular" & Chr(34) & ", 3 for " & Chr(34) & "half circle" & Chr(34) & ", 4 for " & Chr(34) & "quarter circle" & Chr(34) & "," _
    & vbCrLf & "                  5 for " & Chr(34) & "elliptic" & Chr(34) & ", 6 for " & Chr(34) & "triangular" & Chr(34) _
    
    Application.MacroOptions Macro:="Iyy", Category:="Structural Engineering", _
    Description:="Calculates the 2nd moment of inertia of a section over the X-X axis" _
    & vbCrLf & "Section = 1 for " & Chr(34) & "rectangular" & Chr(34) & ", 2 for " & Chr(34) & "circular" & Chr(34) & ", 3 for " & Chr(34) & "half circle" & Chr(34) & ", 4 for " & Chr(34) & "quarter circle" & Chr(34) & "," _
    & vbCrLf & "                  5 for " & Chr(34) & "elliptic" & Chr(34) & ", 6 for " & Chr(34) & "triangular" & Chr(34) _
    
    Application.MacroOptions Macro:="Wxx", Category:="Structural Engineering", _
    Description:="Calculates the 2nd moment of inertia of a section over the X-X axis" _
    & vbCrLf & "Section = 1 for " & Chr(34) & "rectangular" & Chr(34) & ", 2 for " & Chr(34) & "circular" & Chr(34) & ", 3 for " & Chr(34) & "half circle" & Chr(34) & ", 4 for " & Chr(34) & "quarter circle" & Chr(34) & "," _
    & vbCrLf & "                  5 for " & Chr(34) & "elliptic" & Chr(34) & ", 6 for " & Chr(34) & "triangular" & Chr(34) _
    
    Application.MacroOptions Macro:="Wyy", Category:="Structural Engineering", _
    Description:="Calculates the 2nd moment of inertia of a section over the X-X axis" _
    & vbCrLf & "Section = 1 for " & Chr(34) & "rectangular" & Chr(34) & ", 2 for " & Chr(34) & "circular" & Chr(34) & ", 3 for " & Chr(34) & "half circle" & Chr(34) & ", 4 for " & Chr(34) & "quarter circle" & Chr(34) & "," _
    & vbCrLf & "                  5 for " & Chr(34) & "elliptic" & Chr(34) & ", 6 for " & Chr(34) & "triangular" & Chr(34) _
    
    Application.MacroOptions Macro:="BendStress", Category:="Structural Engineering", _
    Description:="Calculates the actual stress due to bending forces, necessary input, bending moment and section modulus" _
    & vbCrLf & "Mb = Enter the bending moment here" _
    & vbCrLf & "Wb = Enter the section modulus here"
    
    Application.MacroOptions Macro:="Elongation", Category:="Structural Engineering", _
    Description:="Calculates the elongation due to pulling force of a body" _
    & vbCrLf & "Force          = Pulling force in (k)N" _
    & vbCrLf & "Length       = Length of the body in mm" _
    & vbCrLf & "Area           = The sectional area in mm²" _
    & vbCrLf & "E = The materials E in N/mm²"
    
    Application.MacroOptions Macro:="BendDeflection", Category:="Structural Engineering", _
    Description:="Calculates the deflection of a body due to a bending moment" _
    & vbCrLf & "Bendingmoment = Enter the bending moment which causes deflection" _
    & vbCrLf & "E    = Enter the young modulus for the given material" _
    & vbCrLf & "Ixx                    = Enter the 2nd moment of inertia here"
    
    Application.MacroOptions Macro:="BucklingForce", Category:="Structural Engineering", _
    Description:="Calculates the force at which a slender column will buckle" _
    & vbCrLf & "E = Enter the materials Young modulus" _
    & vbCrLf & "I = Enter the smallest I value here (Ix-x or Iy-y)" _
    & vbCrLf & "L = the length of the column" _
    & vbCrLf & "K = Effective length factor, 0.5, 0.699, 1.0, 2.0, Depending on fixations"
    
    Application.MacroOptions Macro:="MomentOfInertia", Category:="Structural Engineering", _
    Description:="Calculates the moment of inertia for several bodies." _
    & vbCrLf & "Mass = Mass in kilograms" _
    & vbCrLf & "Bodyshape = Cuboid, Cylindrical, Tubular or Spherical" _
    & vbCrLf & "Length, Width = I.c.o. cuboid shape, Length, Width in mm" _
    & vbCrLf & "Radius, thickness = I.c.o. Cylindrical, Tubular shape, in mm."
    
    Application.MacroOptions Macro:="FrictionForce", Category:="Structural Engineering", _
    Description:="Calculates the force which is necessary to start an object moving" _
    & vbCrLf & "Fn = Normal Force which works onto the body due to gravity" _
    & vbCrLf & "µs or µk = The Static or kinetic friction coefficient" _

End Sub

In the "thisworkbook" section I added the following:

Private Sub Workbook_Open()

    AddDescription

End Sub

When I start Excel, the following message appears:

It says, "cannot edit a macro in a hidden workbook. Make the workbook visible with the command "Make visible".
Yes, I have written the whole as an "addin" and I use 365.

What am i doing wrong?

 

[GWteB]

I don't mean to say it's "wrong", but you are trying to change (the properties of) a macro when no workbook is opened yet, which is causing this error.

Fyi, when Excel starts up, all installed AddIns are opened first, after that either the Recent & Templates dialog appears (if Excel is started from the taskbar) or the active worksheet of the just opened workbook is displayed (if opened from the Windows Explorer). Since your workbook is an AddIn your code runs before any other (visible) workbook is opened.

However, what you're doing is actually superfluous, because one only has to add descriptions of a macro once, either via code or manually (using the Object Browser).

If you still want to stay at the safe side for some reason, let your code invoke the AddDescription procedure from the Workbook_AddinInstall event handler rather than from the Workbook_Open event handler. The code then runs once at installation, i.e. when its checkbox gets ticked (Ribbon > Developer Tab > AddIns) which can only take place if a visible workbook is already open.

 

[dicktimmerman]

Hi GWteB, thanks for your answer, I've changed it, The code works but not as expected. On Addin install it makes nicely the requested formula category and places all the functions in the correct category, however, when I ope excel, either with a blank sheet or with an already used workbook, it places all these formulas in the category "Commands". This would mean that I have to install this addin every time to have it placing the functions in the right category. Any suggestions?

 

[GWteB]

Hi @dicktimmerman, just copied your code, made sure it was invoked through the AddinInstall event handler and saved the workbook as AddIn (.xlam).

Then I closed Excel, restarted Excel (with a new Book1), clicked on Developer tab, AddIns, browsed for the addin and got its checkbox checked.
Your code started to argue immediately (run-time error) because no macro named MomentOfInertia was found to be present. After I erased the code block involved and saved the AddIn, everything ran smoothly and as expected.

After closing and restarting Excel, the custom functions were available, the descriptions were present and categorized under Structural Engineering. Fyi, I'm on Excel 2013 and a category called Command doesn't exist in this version.

Even after rebooting my machine and starting Excel (and regardless whether started with a new or existing workbook) the AddIn turned out to be installed (as it should and as expected), the descriptions of its functions were present and categorized under Structural Engineering.

Bottom line, no idea what's going on on your side and I don't have any suggestions either.
Hopefully someone on the forum will read this thread and come up with something I may be overlooking.

 

[dicktimmerman]

To be honest, I expected this kind of answer. I'm on 365 so there is some difference in the coding, at least I've experienced this for several times, new version is new problems to overcome because of changes in the visual basic part. there is even a huge difference between VBA excel and VBA word.

The "Moment of Inertia" part is under development, that's why the code isn't working properly.

I'll wait and see if there is anybody who has the Eureka moment for me. Still building new formulae.

Thanks a lot, your suggestion helped me a step in the right direction.

 

[GWteB]

Weird that the MacroOptions method seems to give some sort of volatile result. Since I don't experience the issue you have I cannot test this, but imo it should work.
The code intercepts the workbook activate event at application level and thus executes your macro every time a workbook is activated, provided your addin is installed.
It may be a bit excessive, but I don't have any other solution at the moment.

This goes in the ThisWorkbook module of your AddIn:

Option Explicit

Private WithEvents xlApp As Excel.Application

'Private Sub Workbook_AddinInstall()
'    AddDescription
'End Sub

Private Sub Workbook_Open()
    Set xlApp = Excel.Application
End Sub

Private Sub xlApp_WorkbookActivate(ByVal Wb As Workbook)
    AddDescription
End Sub