Create VBA function using worksheet functions?

[anon]

Hello,

How do I create a VBA function using worksheet functions? For example, let's say I use the Macro Recorder and type in a worksheet function. In VBA, it creates a Procedure as opposed to a Function. For example, it may create this:

Sub dayttest()
    ActiveCell.FormulaR1C1 = _
        "=IF(DAY(RC[-11])<10,CONCATENATE(0,DAY(RC[-11])))"
End Sub

How do I convert that into a VBA function? I have a worksheet function that's very long and it would be nice if I just simplify it in VBA.

Thanks!

[Leith Ross]

Hello anon,

You need to know both formulas and VBA to translate from one to the other. The problem here is that you are referencing the ActiveCell, which can be anywhere on the sheet. From that cell you are moving 11 columns to left. If your ActiveCell is not in column "L" or further to the right, an error will occur because you are trying to reference a non existent column.

To really help you with this problem will require seeing the workbook. If you post a copy then it will be possible to provide you with code that actually works.

To Post Your Workbook

1. At the bottom right of the Reply window, Click the button Go Advanced
2. At the top of the Your Message Window, in the first row of icons, Click the Paperclip icon.
3. Click the Add Files button at the top right in the dialog box. This displays the File Manager dialog.
4. Click the Select Files button at the bottom middle of the dialog.
5. In the new window Find the file you want to upload, select it, and Click Open.
6. You will now be back in the File Manager dialog. Click the bottom Middle button Upload File.
7. Wait until the file has completely uploaded before you exit the File Manager dialog.

File Manger Pic

[anon]

Hello,

Thanks for the tips. While VBA is definitely not necessary for what I am trying to do, I would like to do it in VBA so that I can learn VBA. I have attached the file and the right most column you can see my longish formula. Basically I want to condense that into one VBA formula, not procedure.

Thanks!

Anon Workbook.xlsx

[smuzoen]

Create a UDF with the following code (place into a module)

Function MYFUNCTION(stockdate As Date, optionsymbol As String)
Dim tmpStr As String
If Month(stockdate) < 10 Then
    tmpStr = "0" & CStr(Month(stockdate))
    Else
    tmpStr = CStr(Month(stockdate))
End If
If Day(stockdate) < 10 Then
    tmpStr = tmpStr & "0" & CStr(Day(stockdate))
Else
    tmpStr = tmpStr & Day(stockdate)
End If
    
MYFUNCTION = optionsymbol & Right(Year(stockdate), 2) & tmpStr
End Function

So the formula would be =MYFUNCTION(C2,E2)

[anon]

Thanks for the help guys. Both work great. I'll take a look at the VBA code and dissect it to gain a better understanding. The shortern concatenate formula also works great. Didn't know about the text function.

[nilem]

maybe so (for N3)

=CONCATENATE(E3,TEXT(C3,"YYMMDD"))