Free Microsoft Excel 2013 Quick Reference

VBA function returns #NAME?

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


Post your answer or comment

comments powered by Disqus
Analysis ToolPak functions unexpectedly return #NAME error value in Excel 2003 and in Excel 2002

Does anyone know where I can find a complete list of VBA functions which includes
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.

Hello,
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

First, let me start by saying that the analysis toolpak is installed and looking at Tools > add-ins, it is checked so it should be active.

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.

Hi all,

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 been writing functions in vba for a template for designing heat exchangers. It returns #name errors when i try.
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:
	
 
 '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 Function 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Any body with any idea.
Thanks

Hello all,

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:
	
 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
This causes the DLL function to give an error. Any suggestions on how to make this work?

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

Can anyone help me write a VBA function in excel (as I think it does not exist in excel’s built in formulas) to measure compound returns for a specified period within a data range (I can send the spreadsheet as an example) and related function for the beginning and end dates of the data for the particular range.

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.

Hi,

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

Hey All,

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.

Why does VBA function sometimes return "Value", even though no cells in K6:k31 are "#Value!"?

(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

I'm writing a VBA function to replace a huge 5 line formula. However, I need the function to return to it where it was called from so that it can do all the checks. How do I return to the function the cell reference of the cell that called it?

I have a line is a VBA script that reads:

"=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

Does anyone know of a way to colour code the text string returned from a VBA function? I have written a function that returns a string of the days of the week worked e.g. M*W*FS* (where *=day off) and I want to turn Sat or Sun red if they are present (i.e. not *), but the range.characters method doesn't work. Is it possible to create a string with embedded colour? Does anyone know how to colour parts of a string such as this? I'd appreciate any thoughts.

Cheers

Most functions I enter return a #NAME? error in the field

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

Greetings! Am trying first experience of creating a VBA function that is
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

Hi, Using Excel 2003 with no VBA.

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

Hi,
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

Greetings! Am trying first experience of creating a VBA function that is
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

My task is to create a custom VBA function to calculate the geometric mean(i know there is already a worksheet function for this but we need to make out own) with the following assumptions:

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

Hey, Denmark calling

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 Function
My 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

I seem to being having trouble copying the results I obtain from the Kickbutt VBA function to a new sheet. The code I have used should work but it gives me a runtime error '1004' The information cannot be pasted because the COpy area and the paste area are not the same size and shape. try one of the following: Click a single cell, and then paste. Or select a rectangle that's the same size an shape, and then paste.

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


	VB:
	
 
     
    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 If 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
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.

Does anyone know where I can get information about how to make VBA run faster, and how to most effectively use embedded links so that the change of data can be read instantly?

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.

XL2002: FRN: Solver.xla Is Missing the VBA Functions

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


No luck finding an answer? You could always try Google.