Hardcode Userfields or place in Worksheet in Add-in

[NickAtNight]

What is the better practice?

I am learning how to write an excel add-in to provide User Defined Functions (UDF).

On the initial pass, I had the MacroOptions hard coded into the VBA code.

In this test version, I put the input for the MacroOptions into a sheet on the Add-in sheet.

The code is functional. Each of the functions gets added with the correct additional help.

This design separates the functions from the code.

There does not seem to be an initialization problem. So perhaps the sheets are loaded before the code is run.

It is much easier to enter the String information into an Excel sheet than to try to enter it into VBA. Especially when there are quote marks in the sheet.

It makes it much easier to find and change the necessary text.

So are there any drawbacks to this method?
... File size? Speed of operation?

I have some combo boxes. I was considering moving the entries onto the Add-in worksheet as well.

Should each entry bee on its own sheet? or should we use one sheet for all?

I am thinking One sheet for each and the sheet name tells you the purpose of the list.

I also experimented with putting in a CustomUI so that I can use the Ribbon commands. That is working well. I added in a section of code to kick the Add-in into/out of Addin mode so that the sheet can be edited easily.

[Sub RefpropDebug(control As IRibbonControl)
    
    If ThisWorkbook.IsAddin = True Then
        ThisWorkbook.IsAddin = False
        
    Else
        ThisWorkbook.IsAddin = True
        
    End If
    
End Sub/CODE]

1: Is it easy to toggle the color of the Button on the Ribbon to show if it is in Debug or Regular mode?  Or otherwise highlight/gray the button?

2: The CustomMenu follows the Debug mode. So that is a minor annoyance.



N.

===============================
Worksheet Open:
[CODE][Private Sub Workbook_Open()
'    Application.ScreenUpdating = False
    ThisWorkbook.IsAddin = False
    MyRegister
    ThisWorkbook.IsAddin = True
'    Application.ScreenUpdating = True
End Sub/CODE]

MyRegisterSubRoutine
[CODE]

Sub Test1MyRegister()
    Dim strFunction As String
    Dim strCategory As String
    Dim strHelpFile As String
    Dim strDescriptionBase As String
    Dim strDescriptionExample As String
    Dim Description As String
    Dim ArgFluid As String
    Dim ArgInpCode As String
    Dim ArgUints As String
    Dim ArgProp1 As String
    Dim ArgProp2 As String
    
    Dim i As Integer
    Dim LastRow As Integer
    Dim sh As Worksheet
    Set sh = ThisWorkbook.Sheets("Formulae")
    
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    
    LastRow = sh.Range("A1", sh.Range("A1").End(xlDown)).Rows.Count
    
    Debug.Print LastRow
    
    For i = 2 To LastRow
    
    Range("A1").Select
    
    If ThisWorkbook.Sheets("Formulae").Cells(i, 1).Value = "" Then
        'End Sub
        
    Else
        
    
    strFunction = ThisWorkbook.Sheets("Formulae").Cells(i, 1).Value
    strCategory = ThisWorkbook.Sheets("Formulae").Cells(i, 2).Value
    strHelpFile = ThisWorkbook.Sheets("Formulae").Cells(i, 3).Value
    strDescriptionBase = ThisWorkbook.Sheets("Formulae").Cells(i, 4).Value
    strDescriptionExample = ThisWorkbook.Sheets("Formulae").Cells(i, 5).Value
    strDescription = strDescriptionBase & strDescriptionExample
    strArgFluid = ThisWorkbook.Sheets("Formulae").Cells(i, 6).Value
    strArgInpCode = ThisWorkbook.Sheets("Formulae").Cells(i, 7).Value
    strArgUnits = ThisWorkbook.Sheets("Formulae").Cells(i, 8).Value
    strArgProp1 = ThisWorkbook.Sheets("Formulae").Cells(i, 9).Value
    strArgProp2 = ThisWorkbook.Sheets("Formulae").Cells(i, 10).Value
                
    Debug.Print i, strCategory, strFunction, strCategory,
    Debug.Print strHelpFile
    Debug.Print strDescription
    Debug.Print strArgFluid
    Debug.Print strArgInpCode
    Debug.Print strArgUnits
                
    Application.MacroOptions _
      Macro:=strFunction, Description:=strDescription, Category:=strCategory, _
      HelpFile:=strHelpFile, HelpContextID:=833, _
      HasShortcutKey:=True, ShortcutKey:="T", _
      ArgumentDescriptions:=Array(strArgFluid, strArgInpCode, strArgUnits, strArgProp1, strArgProp2)
    End If
    Next
    
End Sub