Thread: Formatting a number in a VBA Function

I am running Excel 2007 and am just starting to learn and get into using VBA or macros. I am currently working on creating my own function and want the formatting of the result to be formatted in a certain way. While I can get the formatting to work, I can't get excel to recognize the solution as a number it will add with other 'normal' numbers. I figure that I am missing something in my code since I'm new at VBA. I tried "numberformat" instead of "format" but the vba editor tells me the sub or function is not defined.

Can anyone point out what I've missed? My code is below.

Function DNF(Fleet, RaceCompetitors, Penalty)
Dim Fleet_Count
Dim Race_Count

' Count Number In Fleet
Fleet_Count = WorksheetFunction.CountA(Fleet)

' Count Number DNS
Race_Count = WorksheetFunction.CountIf(RaceCompetitor… "DNS")

' Determine DNF Score and Label

DNF = Format(((Fleet_Count - Race_Count) + Penalty), "percent")

End Function

Hi Mick

Welcome to the Forum

Please wrap your code in code tags, before the moderators get you...

Forum rules

3. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # button at the top of the post window. If you are editing an existing post, press Go Advanced to see the # button.

Cheers

Looks like the moderators already did lol.

So I've learned that 'format' is purely for strings. So it is by default turning my calculated number into text. The only semi intelligent answer I received on Yahoo Answers is below, but the end result is obviously the same cell my function is in, so I don't know how to convert this guys suggestion into what I'm trying to accomplish.


What you need to do is determine what cell the end result is displayed in, and format that using the Cell Format function, which is tied to an object:

Say the result is displayed in cell D100 in Sheet 1:

ThisWorkbook. Sheets( "Sheet1" ). Range( "D100" ). Value = ((Fleet_Count - Race_Count) + Penalty)
ThisWorkbook. Sheets( "Sheet1" ). Range( "D100" ). NumberFormat = "0.00%