Excel 2007 VBA UDF Argument Limits

[djt76010]

I am writing some VBA UDF's in Excel 2007 and was wondering if there are limits on the number of variables in a UDF as well as a limit on the number of characters.

 

[mikerickson]

1. To allow the user to click into spreadsheet cells for references that could be used in the formula. I would like the user to be able to click on a cell (i.e. : A1), and have that cell reference (A1) be passed into that field instead of the value held in A1

Have you looked at a RefEdit control?

If you put code like this in the userform's code module, pressing cmd+t (mac) will toggle through row/column absolute/relative for RefEdit1.

Private Sub RefEdit1_KeyDown(KeyCode As Integer, ByVal Shift As Integer)
    Dim strFirstCellReference As String
    Static refType As Long
    
    If KeyCode & "-" & Shift = "84-4" Then
        Rem cmd+t on mac
        
        With RefEdit1
            
            On Error Resume Next
            If TypeName(Range(RefEdit1.Text)) <> "Range" Then
                On Error GoTo 0
                Rem entry is not a range address, do nothing
            Else
                On Error GoTo 0
                strFirstCellReference = Split(.Text, ":")(0)
                
                Select Case Len(strFirstCellReference) - Len(Replace(strFirstCellReference, "$", vbNullString))
                    Case 0: refType = xlRelative
                    Case 2: refType = xlAbsolute
                End Select
                
                refType = (refType Mod 4) + 1
                
                .Value = Application.ConvertFormula(.Text, xlA1, xlA1, refType)
            End If
        End With
    End If
End Sub

 

[mikerickson]

Here's another way to input a formula, using an Input Box.

' in userform code module

Private Sub CommandButton2_Click()
     Dim uiString As String
     
    Me.Hide
    uiString = Application.InputBox("Enter a Formula, mouse allowed.", Type:=0)
    
    If uiString = "False" Then
        MsgBox "cancel pressed"
    Else
        uiString = Application.ConvertFormula(uiString, xlR1C1, xlA1)
        MsgBox uiString
    End If
    
    Me.Show
End Sub