Writing a User Defined Function using Year().

  • Thread starter Thread starter John Wirt
  • Start date Start date
J

John Wirt

I want to write a User Defined Function that returns the Year as an integer.
Using the VBA Editor, this UDF apparently cannot employ the built-in Year
worksheet function. Is this correct?

I tried this vastly simplified version of what I need:

---------------------
Public Function YcellE(NumRow As String) As Integer
Dim YearB As Integer

cellB = Replace("$B" & NumRow, " ", "")
YearB = Worksheets("Timeline").WorksheetFunction.Year(cellB)

End Function
------------------

I get error #438 "Object does not support this Property or Method."

Also, "Year" does not show up in the Editor as one of the options after
entering "WorksheetFunction."

If fact, very few of the vast collection of built-in functions show up.

Thanks.

John Wirt
 
John,

VBA has its own Year function, so Excel's Year function isn't
available through Application.WorksheetFunction.

Your syntax is wrong. WorksheetFunction is part of the Application
object, not the Worksheet object, so your code wouldn't work even
if Year were available in WorksheetFunction.

Change
YearB = Worksheets("Timeline").WorksheetFunction.Year(cellB)
to
YearB = Year(cellB)


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com (e-mail address removed)
 
Thank you, Chip.

The formula I want is:

numLR is an integer
formulaLink = "=IF(MONTH($B" & numLR & ")&YEAR($B" & numLR &
")=MONTH(T$3)&YEAR(T$3)," & strCollected & ",(IF(AND(MONTH($C" & numLR &
")=MONTH(T$3),YEAR($C" & numLR & ")=YEAR(T$3))," & strAvailable & ",IF(OR(S"
& numLR & "=" & strCollected & ",T" & numLR & "=" & strBars & ")," & strBars
& "," & strDQuote & "))))"

Do you think I will be better off simply entering this as a formula in each
cell or should I convert it to a VBA UDF? The expression is long. I have
read that UDFs can be slow compared to formulas.

But this formula will appear 100s of times in the worksheet. In the end, I
think a noticeable chunk of the total file size will be taken up with
replications of this formula. It will occupy about 20 columns by 100 rows of
one worksheet.

I've already gotten the formulas written and they work, so it isn't an issue
of getting something that works. It's an issue of getting the most efficient
solution.

John Wirt
 
Back
Top