[RESOLVED] VB Function: #Name? Error

[chendry]

Hi

I'm new to VB and think I'm missing something basic here. I've written a function called Test2, but whenever I type "=Test2(670)" into my worksheet for example, I get the #Name? error.

Do I need to set up/declare the variable somewhere else as well?

Any advice much appreciated. Thanks.

Option Explicit
 
Function Test2(Tinit As Double)
                                          
  Dim cp1() As Double
    
  ' calculate specific heat
    If (Tinit < 600) Then
        cp1 = (425 + (0.773 * Tinit) - (1.69 * 10 ^ -3 * (Tinit ^ 2)) + (2.22 * 10 ^ -6 * (Tinit ^ 3)))
    ElseIf ((600 <= Tinit) And (Tinit < 735)) Then
        cp1 = 666 + 13002 / (738 - Tinit)
    ElseIf ((735 <= Tinit) And (Tinit < 900)) Then
        cp1 = (545 + 17820 / (Tinit - 731))
    ElseIf ((900 <= Tinit) And (Tinit <= 1200)) Then
        cp1 = 650
    End If
        
Test2 = cp1
     
End Function

[Hack]

Excel VBA question moved to Office Development

Welcome to the forums.

If you write a function in code then you must call it from code. Using the Excel command line isn't going to excecut a function you wrote. It will only execute built in Excel commands.

Probably the easiest way to use your function would be from a command button which you could put on your spreadsheet.

[westconn1]

using a function as a custom worksheet function works well, note that it may not auto update as often as builtin worksheet functions

this function appears to work fine, except that you declare cp1 as an array of values, change to single value only
Dim cp1 As Double

[opus]

IMHO the code needs to be in a module!

[westconn1]

IMHO the code needs to be in a module!

i assumed it was, but that may have been incorrect, it is not specified either way, but the error indicates that is the problem

[opus]

If I wouldn't have made this mistake, I wouldn't have noticed!

[chendry]

Thanks for all the help!


Its working now. I think it was because I declared the variable as an array. It was already in a Module. Thanks