I have tried to add the below VBA-code from

http://www.ozgrid.com/VBA/autofilter-criteria.htm in a new module, but

it continues to return #NAME? when I try to use the function:

Function AutoFilter_Criteria(Header As Range) As String

Dim strCri1 As String, strCri2 As String

Application.Volatile

With Header.Parent.AutoFilter

With .Filters(Header.Column - .Range.Column + 1)

If Not .On Then Exit Function

strCri1 = .Criteria1

If .Operator = xlAnd Then

strCri2 = " AND " & .Criteria2

ElseIf .Operator = xlOr Then

strCri2 = " OR " & .Criteria2

End If

End With

End With

AutoFilter_Criteria = UCase(Header) & ": " & strCri1 & strCri2

End Function

Does this indicate an error in the VBA syntax or perhaps that the module

is not made available?

Mogens

http://www.ozgrid.com/VBA/autofilter-criteria.htm in a new module, but

it continues to return #NAME? when I try to use the function:

Function AutoFilter_Criteria(Header As Range) As String

Dim strCri1 As String, strCri2 As String

Application.Volatile

With Header.Parent.AutoFilter

With .Filters(Header.Column - .Range.Column + 1)

If Not .On Then Exit Function

strCri1 = .Criteria1

If .Operator = xlAnd Then

strCri2 = " AND " & .Criteria2

ElseIf .Operator = xlOr Then

strCri2 = " OR " & .Criteria2

End If

End With

End With

AutoFilter_Criteria = UCase(Header) & ": " & strCri1 & strCri2

End Function

Does this indicate an error in the VBA syntax or perhaps that the module

is not made available?

Mogens

- Analysis Toolpak Functions Return #name Error Value
- VBA function return code data types?
- VBA function got name error in excel 2003
- WORKDAY() Function Returning #NAME?
- WEEKNUM Function Returns #NAME?
- #name error in VBA function-
- Passing named range into vba function
- Excel VBA Function: Rolling Period Compound Return
- Networkdays function returns #Name error
- VBA Function to return row number based on matching conditions in three columns
- Why does VBA function sometimes return "#Value!"
- Return cell reference in vba function
- VLOOKUP in VBA returns #Name?
- Returning text from a VBA function
- Most functions do not work and return #Name?
- Worksheet VBA function - not recognized
- Find function returns the #VALUE! error value
- VBA function to look for a value inside a named range
- Worksheet VBA function - not recognized
- Need help creating custom VBA function in excel 2007
- Using a VBA function nested in a Excel function
- Problem Copying Results from Kickbutt VBA Function to Different Sheet
- VBA Function/ Embedded Links
- XL2002: FRN: Solver.xla Is Missing the VBA Functions

Analysis ToolPak functions unexpectedly return #NAME error value in Excel 2003 and in Excel 2002

definitions of the return code data types? I am particularly interested in knowing

what data type is returned by the Application.Search function.

At the moment I've got the return value var declared as a variant (no choice since I

don't know the data type). Now I'm sifting through my code getting rid of these

dreaded variants wherever possible (mainly for performance reasons) and my search

call is receiving special attention since it is in a large loop (executed 1000s of times).

I checked various MS sites and had no joy so far.

I would very grateful for any leads on this.

Many thanks all,

Richard.

I got several VBA functions defined and try to use it in excel 2003, but I

got name error, the functions work very well in others' computer with

different excel version. do you have any ideas of the reason? thank you very

much!

nancy

Here is my code:

=IF(BA5="Point1",WORKDAY('Main _ Menu'!$AC$4,BB5),IF(BA5="Point2",WORKDAY('Main _ Menu'!$AC$5,BB5),IF(BA5="Point3",WORKDAY('Main _ Menu'!$AC$6,BB5),WORKDAY('Main Menu'!$AC$7,BB5))))I've got a workbook with this formula in it deployed amongst multiple users who all have the analysis toolpak installed. Sporadically, when they're using this workbook the cell with the WORKDAY formula will return #NAME? (Even when the Anlysis ToolPak is Checked - I've seen it first hand). If they restart excel or their computer, the function seems to work fine.

Is there anything I can do to do some error handling to ensure that the analysis toolpak is actually running when the workbook is opened? Any other thoughts on how to solve this?

Any replacements for WORKDAY()? I need to be able to take a given date and add/subtract days from it to return a new date that is not on a weekend.

I am trying to get the week from a given data (from a cell that has been correctly formatted with date). =YEAR works, =MONTH works, =WEEKNUM does not work. I am using Excel 2003 and I have tried in both my workplace and at home...

I.e., I have a date in C2 (18-05-08), and =YEAR(C2) returns 2008, =MONTH(C2) returns 5, while =WEEKNUM(C2) returns #NAME, and =WEEKNUM(C2,2) does not work at all...

Any suggestions? thx in advance!

David

I have several functions I think i may have a problem with most of them. but they are written in such a way that

they depend on each other so that if the first returns an error the rest wont show anything.

The first is for calculating correction factor fc taken from Serth see code. I am stuck, see code.

VB:Any body with any idea.'Correction Factor calculations for the LMTD 'source Robert R Serth, heat Transfer principles and applications. 'For any number of shell side passes and any even number of tube side passes as follows 'LMTD is calculated as ' N is the nymber of shell side passes Function Calcfc(Tis As Double, Tos As Double, Tit As Double, Tot As Double, N1 As Double, N2 As Double) As Double 'Correction Factor calculations for the LMTD 'source Robert R Serth, heat Transfer principles and applications. 'For any number of shell side passes and any even number of tube side passes as follows ' N is the nymber of shell side passes 'R P and alpha are ratios used to calculate the correction factor Dim R As Double, P As Double, S As Double, alpha As Double, n As Double Dim num As Double, den As Double, F As Double 'the numerators and denominators of the functions to ease writing the functions 'Where Ta (Tis)= inlet temperature of the shell-side fluid 'Tb (Tos) = outlet temperature of the shell-fluid 'ta (Tit= inlet temperature of the tube side fluid 'tb(Tot) = oulet temperature of the tube side fluid R = (Tis - Tos) / (Tot - Tit) P = (Tot - Tit) / (Tis - Tit) n = N1 * N2 alpha = ((1 - R * P) / (1 - P)) ^ (1 / n) If R = 1 Then S = P / (n - (n - 1) * P) num = (S * 2 ^ 0.5) den = (1 - S) * WorksheetFunction.Ln((2 - S * (2 - 2 ^ 0.5)) / (2 - S * (2 + 2 ^ 0.5))) F = num / den Else alpha = ((1 - R * P) / (1 - P)) ^ (1 / n) S = (alpha - 1) / (alpha - R) num = ((R ^ 2 + 1) ^ 0.5) * WorksheetFunction.Ln((1 - S) / (1 - R * S)) den = (R - 1) * WorksheetFunction.Ln((2 - S * (R + 1 - (R ^ 2 + 1) ^ (1 / 2))) / (2 - S * (R + 1 + (R ^ 2 + 1) ^ (1 / 2)))) F = num / den End If Calcfc = F End FunctionIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Thanks

I've been trying for the past 3 hours to pass named ranges as parameters into my vba function, would really appreciate some help.

I have 2 dynamic named ranges, "myData" and "myPos". I also have a VBA function "myFunc" that takes in ranges, calls a function in a DLL and returns a result.

In my Excel spreadsheet, I could enter the formula "=myFunc(myData,myPos)" and the result would appear in the cell.

Now I'm trying to have a button, such that when i click on the button, the result gets put into a certain cell. Here's my code:

VB:This causes the DLL function to give an error. Any suggestions on how to make this work?Range Dim myPosRange As Range Set myDataRange = ThisWorkbook.Names("myData").RefersToRange Set myPosRange = ThisWorkbook.Names("myPos").RefersToRange Worksheets("Results").Cells(5, 3) = myFunc(myDataRange, myPosRange, 1000)If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

I've also tried referring to the ranges as Range("myData"), but I'm getting a "Method 'Range' of object '_Worksheet' failed" error.

I am trying to create a formula that can measure the compound return for a period ending in a certain date (dates are in column A). For example, (using a formula created in VBA) the compound return (ie (1+monthly return 1) * (1 + monthly return 2) …. * (1+ monthly return 12). So, the inputs would be range (C2:C98) and ending date (in this case 12/31/08, which is in column A86) and number of months (in this case 12). So it might look like: =rollingreturn(range,date,months,0) or =rollingreturn(C$2:C$98,39813,12,0) where the data range is c2:c98, the date is 39813, 12 refers to the previous 12 months (the cell in column C in the row corresponding to the date and the previous 11 rows) and 0 / 1 (binary reference to tell the formula whether to return blank or return a calculation if all of the numbers are not there (ie if some of the 12 cells in this case are blank cells). So in this case, the answer is -23.70% as the monthly performance that it refers to in column C is: -2.40%, 1.10%, 0.30%, 3.40%, 0.80%, -1.70%, -1.80%, -1.50%, -4.70%, -8.70%, -13.20%, 3.00%. Note that the math is that each of the months of performance must be added to 1 and then all multiplied (and then subtract one at the end) in order to get the compound return (ie in this case: = (1+C75) * (1+C76) *(1+C77) * (1+C78) * (1+C79) * (1+C80) * (1+C81) * (1+C82) * (1+C83) * (1+C84) * (1+C85) * (1+C86) -1.

As part of this request, I also need (this part should be REALLY simple) functions to tell the date where the data starts and ends. In column C, that would be =enddate(c$2:c$98) which would return 39964 (May 31, 2009, the date corresponding to the last non-blank cell in the range) and a similar function begindate(c$2:c$98) which would return 39325 (August 31, 2007, the date corresponding to the first non-blank cell in the range) .

My data looks like this: Column A: range of monthly dates (ie. A2: 12/31/2001, A3: 1/31/2002…A98: 12/31/2009). Column B: thru Column O: monthly percentage returns (performance for funds that I am analyzing). The fund names are in row 1 (ie B1, C1 etc.). However, the inception date for each fund differs. So, for example, the fund in column C started in August 2007 and I have data through May 2009. So, cells C2:C69 are blank as are rows C92:C98. Rows C70:C91 have monthly % returns in each row (ie: 1.50%, 1.10%, 0.70%, -0.80%, 1.00%, -2.40%, 1.10%, 0.30%, 3.40%, 0.80%, -1.70%, -1.80%, -1.50%, -4.70%, -8.70%, -13.20%, 3.00%, -1.20%, 0.85%, -0.10%, 2.90%, 2.90%). As mentioned previously, I can send the spreadsheet if it helps.

This function works in all of my workbooks except one. For some reason I get #name unless I create a VBA reference to Atvbaen inb that workbook. All other workbooks have no referneces and the function works fine.

I'm pulling my hair out - thx for any help

Howard

Been getting involved in using VBA with Excel to help out a colleague. My problem at the moment is; we have range of product/price data of which Col A, Col B, Col C contain product information. We have three cells on a worksheet using lists to allow the user to select values which are found in the three columns A,B,C. A user will select three values and we want to be able to use VBA linked to a button to find the row which matches with these three values, we will use the row number later with a column range to perform an intersect.

An example is: user chooses value for col a = 2, col b = a, col c =10 the VBA function or sub will return the matching row, in this case row 6.

a b c

row2 1 a 10

row3 1 a 20

row4 1 b 10

row5 1 b 20

row6 2 a 10

row7 2 a 20

row8 2 b 10

row9 2 b 20

All help greatly appreciated.

(Any better ways to concatenate text in a range of cells?)

If I press f9 then it often recalculates and properly shows "none".

xls: =ConcatenateCellsInRange(Systems!$K$6:$K$31)

K6: =IF(D6=2,"SUM "&E6&" > LIMIT "&J6&" "&U6,"")

VBA:

Function ConcatenateCellsInRange(SelectedRange As Range) As String

'2002-07-27d Created by Brian West

Dim c As Range

Dim FirstOne As Boolean

Dim TempStr As String

TempStr = "NONE"

FirstOne = True

For Each c In SelectedRange

If c.Value "" Then

If FirstOne = True Then

TempStr = " " & c.Value

FirstOne = False

Else

TempStr = TempStr & Chr(10) & " " & c.Value

End If

End If

Next c

ConcatenateCellsInRange = TempStr

End Function

Thanks,

Brian

"=IF(ISNA(VLOOKUP(RC[-24],M2M!A:L,9,FALSE)),"" "",VLOOKUP(RC[-24],M2M!A:L,9,FALSE))"

When I execute the code, this returns #Name?

If I then go to that cell, hit Edit (F2), and then hit Enter, it returns the actual value that I expect. Can anyone help me fix this in the VBA code? As it is now, the user runs the VBA, and then has to go to that cell, hit Edit, hit Save, and copy the formula to the bottom of the range. I'm stumped.

TIA,

Theresa

Cheers

Example Functions that do NOT work:

=QUOTIENT(5, 2)

=complex(3,4)

=IMAGINARY("3+4i")

(None of the engineering functions work)

All of the above functions return the error "#NAME?"

Example functions that work:

=sum

=odd

=even

=concatenate

Logical functions all work.

I have tried other computers at my company and it works fine.

Any ideas?

Cheers,

James

Microsoft Office Excel 2003 (11.6560.6568) SP2

Part of Microsoft Office Standard Edition 2003

called via a cell formula in a worksheet, unsuccessfully so far.

Steps have been to paste code into a workbook module such as:

Function CellType(c)

' Returns the cell type of the upper left

' cell in a range

Application.Volatile

Set c = c.Range("A1")

Select Case True

Case IsEmpty(c): CellType = "Blank"

Case Application.IsText(c): CellType = "Text"

Case Application.IsLogical(c): CellType = "Logical"

Case Application.IsErr(c): CellType = "Error"

Case IsDate(c): CellType = "Date"

Case InStr(1, c.Text, ":") 0: CellType = "Time"

Case IsNumeric(c): CellType = "Value"

End Select

End Function

I then enter a formula in a cell in a worksheet as =celltype(a1). The

function name does not get capitalized, suggesting that it wasn't located,

and the result is #NAME?, suggesting same thing. Something's not plugged in

somewhere, but I have no idea what. Help!

Thanks,

George

I am using the Find function in a cell to search for a comma. When it finds

the comma no problem but the function returns the #VALUE if it does not find

the comma. How do I trap for the error?

I am looking at a cell A1 for names and putting the answer in B1. If the

name is "last, first" I switch them to "first last" with a space. If A1

contains "first last" with no comma I am leaving them alone. Cell Function

is:

=IF(FIND(",",A1,1),A1,RIGHT(A1,LEN(A1)-SEARCH(",",A1,1))&" "&

LEFT(A1,SEARCH(",",A1,1)-1))

Works with comma but returns #VALUE! with no comma.

How to trap for error?

Ken

This is probably moronicly simple, but I've been staring at it for hours ....

The task appears simple, I have a named range "ListOfReps" in B2:B4 with the names Tom, Peter and David respectively listed. I am trying to create a Boolean function that I can use in the spreadsheet to check to see if a name is on the list, i.e., =IsRep("David") or =IsRep(C2). My code is simple:

Function IsRep(RepName As String)

Dim C As Range

Set C = Sheets("Sheet1").Range("ListOfReps").Find(RepName)

If C Is Nothing Then

IsRep = False

Else

IsRep = True

End If

End Function

If I call IsRep from a VBA function, it works correctly; however, if I enter a formula in a spreadsheet, "=IsRep("Tom") and singlestep through the code, C is never non-empty. Any idea what I am doing wrong?

Thanks,

David

called via a cell formula in a worksheet, unsuccessfully so far.

Steps have been to paste code into a workbook module such as:

Function CellType(c)

' Returns the cell type of the upper left

' cell in a range

Application.Volatile

Set c = c.Range("A1")

Select Case True

Case IsEmpty(c): CellType = "Blank"

Case Application.IsText(c): CellType = "Text"

Case Application.IsLogical(c): CellType = "Logical"

Case Application.IsErr(c): CellType = "Error"

Case IsDate(c): CellType = "Date"

Case InStr(1, c.Text, ":") <> 0: CellType = "Time"

Case IsNumeric(c): CellType = "Value"

End Select

End Function

I then enter a formula in a cell in a worksheet as =celltype(a1). The

function name does not get capitalized, suggesting that it wasn't located,

and the result is #NAME?, suggesting same thing. Something's not plugged in

somewhere, but I have no idea what. Help!

Thanks,

George

Write a custom function to calculate the geometric average of the returns listed in cells B6:B15.

Below is the formula for a geometric average:

[(1 + R1) * (1 + R2) * ... * (1 + Rt)]^(1/t) - 1

There are three requirements for your function:

i. Your function should have a single input – the range of cells that contain the return series.

ii. Use a For/Next loop to cycle through the returns in the range provided by the user.

iii. Use an If/Then statement to determine if the returns are entered in decimal or percentage point form. You may assume that values above 1 are in percentage

---------------------------------------------------------------------------------------------------------------

This is my function so far and I keep getting a #NAME error. ANY HELP WOULD BE GREATLY APPRECIATED

Function MyGEO(Returns As Range)

Dim Varcount As Integer

Dim t As Integer

Dim mean As Double

Dim geomean As Double

If Returns >= 1 Then Returns = Returns / 100

Varcount = Returns.Count

mean = 1

For t = 1 To Varcount

mean = mean * (1 + Returns(t))

Next t

geomean = mean ^ (1 / Varcount) - 1

MyGEO = geomean

End Function

I'm trying to define a dropdown/validation defined in another worksheet. In the sheet where I want the dropdown - say sheet1 - in column A I got a number corrensopding to the row number in the sheet - sheet2 - contaning the list data.

Anyway so far I got (in the 'Data'->'Validation'->'Settings'->'Source' dialog):

=OFFSET(Gen_list1;(ROW()-307);1;1;8)

Working smoothly but not very dynamic

So I've created a small VBA function - called FindGenIONo() - that simply extract the number from the text in column A, and returns it, like this:

Function FindGenIONo() As Integer Dim IONo As Integer Dim GenIONameNo As String GenIONameNo = Cells(ActiveCell.Row, "A") If InStr(GenIONameNo, "GenIO") Then 'This is a GenIO If Len(GenIONameNo) = 6 Then '1 digit. No's 0 - 9 IONo = Mid(GenIONameNo, 6, 1) ElseIf Len(GenIONameNo) = 7 Then '2 digit. No's 10 - 99 IONo = Mid(GenIONameNo, 6, 2) ElseIf Len(GenIONameNo) = 8 Then '3 digit. No's 100 - IONo = Mid(GenIONameNo, 6, 3) End If ElseIf InStr(GenIONameNo, "GenOut") Then 'Name = GenOut If Len(GenIONameNo) = 7 Then '1 digit. No's 0 - 9 IONo = Mid(GenIONameNo, 7, 1) ElseIf Len(GenIONameNo) = 8 Then '2 digit. No's 10 - 99 IONo = Mid(GenIONameNo, 7, 2) ElseIf Len(GenIONameNo) = 9 Then '3 digit. No's 100 - IONo = Mid(GenIONameNo, 7, 3) End If Else 'Name unknown IONo = 10000 'MsgBox ("Unable to determind GenIO Name") End If FindGenIONo = IONo End FunctionMy plan was to use this function in the Validation dialog, like this:

=OFFSET(Gen_list1;FindGenIONo();1;1;8)

But when trying to do so I get an error (ind excel - not VB):

"A name range you specified cannot be found"

Do any of you know if it possible to use a function like this? or what I'm doing wrong?

Thanks a lot in advance.

CB

Below is the code I have used. The results of the function call return an unknown amount of rows with 10 columns.

VB:As you can see the destination I use is only one cell so it should paste with no problem but it doesn't. The code runs fine when I paste to any row in column A but once I paste to a different column it bugs out. I don't understand why it is creating these errors.mon = month(DateValue("1-" & Target.Value & "-2000")) Call Find_Range(mon, Sheets(6).Columns("A")).EntireRow.Copy(Sheets(3).Range("F10")) Else: Exit Sub End IfIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

My plan specifically is to use a VBA Function procedure to input embedded links, using other data on the worksheet as arguments: the names of the source workbooks comprise first and last name, which will appear as separate fields on the destination workbook (put there by a query). So the function will use those as arguments to put the link there.

Would appreciate all the tips I can get on using embedded with macros, and what might slow down the time it takes to read the source data.

Solver.xla is missing Microsoft Visual Basic for Applications (VBA) functions in ... Date Time Version Size File name ...