Calling a Custom Function within VBA

[Bowlnhokie]

I have created a custom Function in excel. I would like to create a custom button so I can run this funtion with a Click of a button unstead of typing it in or clicking on the insert function button. I am not sure if this is possible to have VB call a custom function. Below is my VB for the Custom Function. I basically want the custom button to open the formula in excel. Can anyone help?

Public Function CreateFlexstring(Company As String, Cost_Center As String, _
                                        Division As String, Geography As String)
                                        
CreateFlexstring = Company & "-" & Cost_Center & "-" & Division & "-" & Geography

End Function

 

[Joe4]

Not quite sure what you are trying to do, but I see a few potential problems:

1. Your Function requires 4 arguments to be passed to it. How do you propose passing these arguments by clicking a button (where would these arguments come from)?

2. Do you want this function to be populated in a specific cell? Or do you just want to return the value of the function in a MsgBox.

Perhaps this is what you are after.

Tie your command button to some other VBA code that has a series of InputBoxes asking for inputs for each of your 4 arguments (and possibly the cell you want the formula to go into, if that is what you are after), and then either do the calculation, or enter the Function and designated arguments into your specified cell.

 

[Bowlnhokie]

All I want the button to do is open the Formula window (Functions Arguments) which will allow me to then select my 4 pieces of the argument.

 

[Joe4]

Hmmm...

I don't know if that is possible (I am not saying it isn't, I just don't know if it is).

As an alternative, you could do something like I mentioned above. Here is some code that you can attach to your command button that will reference your UDF and create your formula:

Sub CallMyFunction()
    Dim myCompany As String
    Dim myCost_Center As String
    Dim myDivision As String
    Dim myGeography As String
    Dim myCell As String
    
    myCompany = InputBox("Enter Company")
    myCost_Center = InputBox("Enter Cost Center")
    myDivision = InputBox("Enter Division")
    myGeography = InputBox("Enter Geography")
    myCell = InputBox("Enter cell you would like to put formula in")
    
    Range(myCell).Formula = "=CreateFlexstring(" & Chr(34) & myCompany & Chr(34) & "," & Chr(34) & myCost_Center & Chr(34) & "," & _
        Chr(34) & myDivision & Chr(34) & "," & Chr(34) & myGeography & Chr(34) & ")"
    
End Sub

(of course, since your Function is pretty simple and straightforward, you could actually skip the Function altogether and just use this Macro to put your formula/result directly in the specified cell.

Another alternative is that you may be able to create a User Form in VBA that would resemble the Formula window and allow the user to enter all the inputs.

 

[Bowlnhokie]

Thank you for your help.

The VB you provided requires me to type in the data, where as I need to select a specific cell that contains my data.

 

[Joe4]

The VB you provided requires me to type in the data, where as I need to select a specific cell that contains my data.

I do not know if that is possible. You would either need to:
1. Invoke the Formula window (like you said);
2. Somehow create a User Form to emulate the Formula window.

I haven't figured out a way to pull up the formula window by code.

You may already be aware of this, but you can use the actual Formula window to pull up your User Defined Function (it is in there under the USer Defined category).

One last note on the code I posted. With a few simple modifications, we could make it so the user just has to type in the cell references instead of typing in values.

 

[Bowlnhokie]

Not sure if this is the most efficient way but the below worked for me:

Sub CallCreateFlexstring()

ActiveCell.Value = "=PERSONAL.XLS!CreateFlexstring()"

Application.SendKeys ("%I")
Application.SendKeys ("F")
End Sub

 

[Joe4]

Ah yes, SendKeys. Didn't think of that (I personally never use them myself, but they do have their uses).

Thanks for posting that back. I learned something new today.

 

[Bowlnhokie]

Joe,

Thanx for helping

 

[Joe4]

Thanx for helping

I am not really sure who helped who, but your welcome!