newb - extremely simple VB question for Excel...?

I am totally new to getting VB to work in Excel.

I want a forumla to live on my sheet one, in box G2.
It takes the values from E2 and F2 and does some calculations on them and outputs the value in G2.

I click in the box and go to Developer VB Editor (Alt-F11) and the VB editor starts.

But how do I get my subroutine to actively produce something in G2?
I tired clicking in G2 and typing PAL_NTSC_Difference() but it just put the text in.

Help!



ps: here's my simple sub so far

Sub PAL_NTSC_Difference()

Dim NTSC_value As String
Dim PAL_value As String

Range("E2").Select
Selection.NumberFormat = "@"
PAL_value = ActiveCell.Value

Range("F2").Select
Selection.NumberFormat = "@"
NTSC_value = ActiveCell.Value

Range("G2").Select
Selection.NumberFormat = "@"
ActiveCell.Value = "diffvalue" ' just a test string output so far...

End Sub

Hi, and welcome to the forum.
What you are after is called a User Defined Function – UDF. You can find lots of help on it by searching.
A UDF must be a Function and not a Sub, since you are after a return value (the result of the calculation).
It must be Public and it must be located in a standard Module and not a Sheet Module.
In order to make the UDF more generic it really should take all its input as parameters or arguments. It should not get values from the sheet itself (it can, and it might be useful sometimes, but try to avoid it in general).

Public Function PAL_NTSC_Difference(byval rParam1 as Range, byval rParam2 as Range) as String
PAL_NTSC_Difference = "diffvalue" ' just a test string output so far...
End Function

This should be enough to get you going. Come back if you have problems.
-Terry

thanks,

I made a UDF.

When I go to my cell G2, I type in

=PAL_NTSC_Difference(E2,F2)

and the function name appears in the drop down list as I type, but when I finish typing and press return, it simply puts in the box:
=PAL_NTSC_Difference(E2,F2)

and not the value it returns.

The format of my cell is Text and the function returns text using
ReturnString

I hate when that happens. It’s a formatting problem with the cell, not the UDF. You’ll same the same result if you type in a regular excel function.

Try setting the cell format to General. In my experience it can be difficult to make a cell ‘snap out of it’. You can try to copy a cell that behaves normally into G2 and then retype the formula.

-Terry

Thanks so much! The General format caused my function to invoke.

My last two problems are these:

a) How do I get it to return a String?
I have typed in
Return MyString
But when I type that I get a compiler error straight away "Expected End Of Statement" and the line turns red.

b) Some of my cells values I pass to my function say "#VALUE!"
How do I say in VB,
If ActiveCell = "#VALUE!" then skip it?
Whats the value for "#VALUE!" ??

thanks :)


ps:I actually just tried this:
MyFunctionName = AnswerToReturn
Setting it as s String gave me the object error. This fixed it.

pps: problem b) still exists!!!

You should not refer to the active cell in the UDF. All parameters should be passed into the UDF unless you have a good reason not to do it this way.

In my example above the UDF takes Ranges as parameters. Since they are ranges you may use properties and functions that apply to a regular range. Try using the IsError function to check the incoming parameters for errors.

-Terry

Thanks,

But if I pass the active cell as a parameter, I find I get a Circular Reference error every time I try and access the parameter, even if I try and display it.
eg:
MsgBox rParam2

How do I get the location of where my UDF is runnin from without getting a circular ref?

(ByVal rParam2 As Range)

You can’t.
A UDF takes some parameters and return a value. The value is displayed in the cell where you type the UDF name. You cannot base a value on it self.
It like putting “=SUM(A1:B1)” into cell A1.

Why do you want to base the value in G2 on the value in G2?

Try this

Public Function PAL_NTSC_Difference(byval rParam1 as Range, byval rParam2 as Range) as String
PAL_NTSC_Difference = “Value 1 is “ & rParam1.Value & “ and Value 2 is “ & rParam2.Value
End Function

And in G2 just have =PAL_NTSC_Difference(E2, F2)

-Terry