Public Function - Coding / Color /Worksheet Change Event

[brian sullivan]

Greetings Chandoo community,

I'm reaching out in regards to completing a public function I created in VBA. The public Function Performance_Message I created is designed to return a output that is a mix of string & numeric text.

I would appreciate some advice here so much. I feel like I'm very close to the finish line, but I'm doing a small thing incorrectly in my coding. I've attached a 2010 Excel file with Code that I would appreciate some feedback here.

My issues that exist right now are below:

• Public Function in the worksheet (Performance_Message) returns a a value error based on VBA code using Select Case Statement
• I'm requesting assistance in how to generate colored cells in my function. I have coding in place, but it's not generating correctly. I believe my coding structure is wrong for this matter.
• Worksheet Change Event (I have in place is suppose to fire everytime numbers in the support data exits).

Data Notes (Performance Worksheet) MODULE

=Performance_Message(D43,$D$42,E43,$E$42)
VBA variables names in Function Worksheet /Arguments

• D43 = Non Weighted Worksheet Report average (Worksheet) / Non PreferredAverage (VBA) single data type
• $D$42 = Non Weighted Report Name column Header / NonPreferredAverageName (VBA string variable type)
• E43 = Weighted Worksheet Report Average (Worksheet) /PreferredAverage (Single)
• $E$E42 = Weighted Worksheet Report averageName (Worksheet)/PreferredAverageName VBA String
• Outputp As String Variable (VBA Code) represents returned public function text string output type

=Performance_Message(E43,$E$42,G43,$G$42) same setup here too. Formula public function carries over

Declarations (Dim statements)
Dim Performance message As String
Dim Averageddifference As Single
Dim Stravgdif As String
Dim Cell Color AS String

Expressions

M
averagedifference = Abs(NonPreferredAvg - PreferredAvg)
stravgdif = FormatPercent(averagedifference, 2)

 

[Hui]

Your function Performance_Message has 5 inputs, but you only supply 4 in the Formulas in column F

None of the parameters are marked optional and so there is an error

Public Function Performance_Message(NonPreferredAvg As Single _
, NonPreferredAvgname As String _
, PreferredAvg As Single _
, PreferredAvgname As String _
, Outputtype As String _
)

F43: =Performance_Message(D43,$D$42,E43,$E$42)

So a simple change:

Public Function Performance_Message(NonPreferredAvg As Single _
, NonPreferredAvgname As String _
, PreferredAvg As Single _
, PreferredAvgname As String _
, Optional Outputtype As String _
) as Variant

Fixes it

It is also good practice to declare the return data type of the function, in this case a String or Variant.

 

[brian sullivan]

Good afternoon,

Thank you for the quick tip to provide the Optional /As Variant syntax. The function processes the figures well now.

I realized the OutputType parameter can't be known until run time; therefore you need to use Option/Variant. Is that the correct logic being implied here, or should I be diagnosing this item a different way.

Thanks,

Brian