Free Microsoft Excel 2013 Quick Reference

Passing named range into vba function

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.


Post your answer or comment

comments powered by Disqus
Hi all, I'm trying to pass a named range into an array.

Normally with ranges you pass them into arrays as such:

	VB:
	
 
myRange = Sheets("Sheet1").Range("A1:A10") 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
But it doesn't seem to work like that with named ranges. What is the correct syntax?

I have tried:

	VB:
	
 
myRange = Sheets("Sheet1").Range("rangeName") 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
And even tried:


	VB:
	
 
myRange = Thisworkbook.Names("rangeName").RefersToRange 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Each giving an "Application defined or object defined error" -- any ideas?

This is one of several question I need answered for a larger project.

Once I have called in a named range into Vba and made changes to It, how doe I update the changes back into the spread sheet?

This is one of several questions I need answered to perform a larger task.

I have named ranges locally in the active file and a named range in a different file.

How do I call these ranges into the VBA code to evaluate them.

I am writing a VBA function which will take X and Y curve points and determine their polynomial trendline coefficients. I don't want to create an Excel chart to obtain the coefficients.

Eventually I'd like to let the user select a set of X and Y points on a spreadsheet, pass them to the VBA function and return different values back to the spreadsheet.

J-Walk & Associates webpage provides equations for calculating the trendline coefficients. Unforturnately, I can't get the function to work.

Here is the primary code which is causing the problem.
Sub TestHead()
    Dim Answer As Variant
    Answer = PumpHead(Worksheets("Sheet1").Range("A2:A8"),
Worksheets("Sheet1").Range("B2:B8"), 800, 6)
End Sub

Public Function PumpHead(xFlow As Range, yTDH As Range, Flow As Double, Order As Integer) As Variant
    Dim xFlowUB As Integer, yTDHUB As Integer
    Dim i As Integer
    Dim Head1 As Double, Test As Double
    Dim coeff()
    
'Check Inputs
'Here I have several error checking routines to ensure the values passed to the function 
'are acceptable.
    
'Begin coeff and PumpHead calculation
    Select Case Order
        Case 2
            coeff = Evaluate("LINEST(yTDH,xFlow^{1,2})")
            PumpHead = coeff(1) * Flow ^ 2 + coeff(2) * Flow + coeff(3)
        Case 3
            coeff = Evaluate("LINEST(yTDH,xFlow^{1,2,3})")
            PumpHead = coeff(1) * Flow ^ 3 + coeff(2) * Flow ^ 2 + coeff(3) * Flow + coeff(4)
        Case 4
            coeff = Evaluate("LINEST(yTDH,xFlow^{1,2,3,4})")
            PumpHead = coeff(1) * Flow ^ 4 + coeff(2) * Flow ^ 3 + coeff(3) * Flow ^ 2 + coeff(4) * Flow + coeff(5)
         Case Else
            PumpHead = "ERROR!#"
    End Select

ExitFunction:
End Function
When I run the code above I get the error "Type mismatch". I tried converting the xFlow and yTDH values to an array but with no success.

Thanks in advance.

I have a modfunction I found online but am not sure how to pass the range.("I:K" & Range("K65536").End(xlUp).Row) to the function.

can anyone help?

code is from http://www.cpearson.com/excel/DeleteDupsWithFilter.aspx

Option Explicit
Option Compare Text

Function DeleteDuplicatesViaFilter(ColumnRangeOfDuplicates As Range) As Long
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' DeleteDuplicatesViaFilter
' This function uses Advanced Filter to remove duplicate records from
' the rows spanned by ColumnRangeOfDuplicates. A row is considered to
' be a duplicate of another row if the columns spanned by ColumnRangeOfDuplictes
' are equal. Columns outside of those spanned by ColumnRangeOfDuplicates
' are not tested.  The function returns the number of rows deleted, including
' 0 if there were no duplicates, or -1 if an error occurred, such as a
' protected sheet or a ColumnRangeOfDuplicates range with multiple areas.
' Note that Advanced Filter considers the first row to be the header row
' of the data, so it will never be deleted.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Dim DeleteRange As Range
Dim Rng As Range
Dim SaveCalc As Long
Dim SaveEvents As Long
Dim SaveUpdating As Long
Dim BeginRowCount As Long
Dim EndRowCount As Long

''''''''''''''''''''''''''''
' Save application settings.
''''''''''''''''''''''''''''
SaveCalc = Application.Calculation
SaveEvents = Application.EnableEvents
SaveUpdating = Application.ScreenUpdating

On Error GoTo ErrH:

'''''''''''''''''''''''''''''''''
' Allow only one area.
'''''''''''''''''''''''''''''''''
If ColumnRangeOfDuplicates.Areas.Count > 1 Then
    DeleteDuplicatesViaFilter = -1
    Exit Function
End If

If ColumnRangeOfDuplicates.Worksheet.ProtectContents = True Then
    DeleteDuplicatesViaFilter = -1
    Exit Function
End If

''''''''''''''''''''''''''''''''''''''''
' Change application settings for speed.
''''''''''''''''''''''''''''''''''''''''
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Application.ScreenUpdating = False
BeginRowCount = ColumnRangeOfDuplicates.Rows.Count

'''''''''''''''''''''''
' AutoFilter the range.
'''''''''''''''''''''''
ColumnRangeOfDuplicates.AdvancedFilter Action:=xlFilterInPlace, Unique:=True
'''''''''''''''''''''''''''''''''''''''
' Loop through and build a range of
' hidden rows.
'''''''''''''''''''''''''''''''''''''''
For Each Rng In ColumnRangeOfDuplicates
    If Rng.EntireRow.Hidden = True Then
        If DeleteRange Is Nothing Then
            Set DeleteRange = Rng.EntireRow
        Else
            Set DeleteRange = Application.Union(DeleteRange, Rng.EntireRow)
        End If
    End If
Next Rng
'''''''''''''''''''''''''
' Delete the hidden rows.
'''''''''''''''''''''''''
DeleteRange.Delete shift:=xlUp
'''''''''''''''''''''''''
' Turn off the filter.
'''''''''''''''''''''''''
ActiveSheet.ShowAllData
EndRowCount = ColumnRangeOfDuplicates.Rows.Count
'''''''''''''''''''''''''
' Set the return value.
'''''''''''''''''''''''''
DeleteDuplicatesViaFilter = BeginRowCount - EndRowCount

ErrH:
If Err.Number <> 0 Then
    DeleteDuplicatesViaFilter = -1
End If
''''''''''''''''''''''''''''''''''''''
' Restore application settings.
''''''''''''''''''''''''''''''''''''''
Application.Calculation = SaveCalc
Application.EnableEvents = SaveEvents
Application.ScreenUpdating = SaveUpdating

End Function


Hi,

I have created a code to copy a named range (basically an array of a few rows and columns) into VBA and it does not seem to work:

    Dim ABC As Variant
    ABC = Range("whydoesthisnotwork")
There must be something very simple that I'm missing?

Thanks
MVScarlet

Hi All,

These forums have been great for me however I can't seem to find a solution to my problem.

I have 3 salesman, each with their own proposal log.

Upon clicking a button (in excel) to create a proposal (using a merge from named ranges in excel to bookmarks in a word template), I want the macro to first open up the salesman's relevant proposal log and copy certain named ranges into a new row at the bottom of the log table.

I get no errors when running the code and the correct proposal log is opened (as well as the merge to the word document), however the range I want to copy and paste into the proposal log does not occur and I can't work out why.

Here is the code:


	VB:
	
 
    Dim wk As String 
    On Error Goto Notopen 
    wk = Workbooks(Book).Name 
    Workbookopen = True 
    Exit Function 
Notopen: 
    Workbookopen = False 
End Function 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

	VB:
	
 BCMerge() 
    Dim pappWord As Object 
    Dim docWord As Object 
    Dim wb As Excel.Workbook 
    Dim Propwb As Excel.Workbook 
    Dim xlName As Excel.Name 
    Dim TodayDate As String 
    Dim Path As String 
    Dim YesOrNoAnswerToMessageBox As String 
    Dim QuestionToMessageBox As String 
    Dim Ph As String 
    Dim Sm As String 
     
     
    QuestionToMessageBox = "Are you sure you wish to create proposal?" 
     
    YesOrNoAnswerToMessageBox = MsgBox(QuestionToMessageBox, vbYesNo, "Create Proposal or Not") 
     
    If YesOrNoAnswerToMessageBox = vbNo Then 
         
    Else 
         
        Ph = Workbooks("Estimate Sheet7").Path 
        Sm = [salesman] 
         
        On Error Resume Next 
        If [salesman] = "AP" Then 
            If Not Workbookopen("AP Proposal Log.xlsm") Then 
                Workbooks.Open Filename:=Ph & "AP Proposal Log.xlsm", UpdateLinks:=0 
            End If 
             
        ElseIf [salesman] = "MB" Then 
            If Not Workbookopen("MB Proposal Log.xlsm") Then 
                Workbooks.Open Filename:=Ph & "MB Proposal Log.xlsm", UpdateLinks:=0 
            End If 
             
        ElseIf [salesman] = "JH" Then 
            If Not Workbookopen("JH Proposal Log.xlsm") Then 
                Workbooks.Open Filename:=Ph & "JH Proposal Log.xlsm", UpdateLinks:=0 
            End If 
        End If 
         
        Set Propwb = Workbooks(Sm & " Proposal Log.xlsm") 
         
        Windows("Estimate Sheet7").Activate 
        Sheets("Calc Sheet").Select 
        Range("AD1:AL1").Copy 
        Propwb.Activate 
        Sheets("Proposal Log").Select 
        Range("A2").End(xlDown).Select 
        ActiveCell.Offset(1, 0).Activate 
        Sheets("Proposal Log").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ 
        :=False, Transpose:=False 
        Application.CutCopyMode = False 
    End If 
     
    Set wb = ActiveWorkbook 
    TodayDate = Format(Date, "mmmm d, yyyy") 
    Path = wb.Path & "DROSS PRESS PROPOSAL - bookmark attempts 7" 
     
    On Error Goto ErrorHandler 
     
     'Create a new Word Session
    Set pappWord = CreateObject("Word.Application") 
     
    On Error Goto ErrorHandler 
     
     'Open document in word
    Set docWord = pappWord.Documents.Add(Path) 
     
     'Loop through names in the activeworkbook
    For Each xlName In wb.Names 
         'if xlName's name is existing in document then put the value in place of the bookmark
        If docWord.Bookmarks.Exists(xlName.Name) Then 
            docWord.Bookmarks(xlName.Name).Range.Text = Range(xlName.Value) 
        End If 
    Next xlName 
     
     'Activate word and display document
    With pappWord 
        .Visible = True 
        .ActiveWindow.WindowState = 0 
        .Activate 
    End With 
     
     'Release the Word object to save memory and exit macro
ErrorExit: 
    Set pappWord = Nothing 
    Exit Sub 
     
     'Error Handling routine
ErrorHandler: 
    If Err Then 
        MsgBox "Error No: " & Err.Number & "; There is a problem" 
        If Not pappWord Is Nothing Then 
            pappWord.Quit False 
        End If 
        Resume ErrorExit 
    End If 
     
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Any help would be greatly appreciated.

Thanks.

I totally agree with the Ozgrid author who wrote "I don't think it's any secret that I'm a pretty big fan of Dynamic Named Ranges ".

However I believe am getting a heavy performance hit from using the standard dynamic named range of "=OFFSET($A$1,0,0,COUNTA($A:$A),COUNTA($1:$1))"
Apparently OFFSET is a volatile function and gets executed every time you change a cell.

Has anyone found a good way around this?

My usage usually revolves around copy/pasting a big block of raw data (10k or more rows) and then updating some pivots and other calculations.

Possibilities I have considered:Defined named range calling VBA function where you pass the 1st cell of the sheet and it returns the range of all used cells. Excel would not treat the VBA as dynamic which is good. But not sure when it would get recalculatedAs 1 but pass a very wider range of columns (eg myVBA(A:ZZ)) - this would then get recalculated if anything in that range changedAdd a function which picks up the change event for each of the sheets I am interested in
Any thoughts?

Mike

Hi-

I'm looking to create a named range based on certain values from a column. For example, with this array:

1 Run Tom Judy James
1 Run Tom Judy James
1 Run Tom Judy James
2 Walk Tom James
2 Walk Tom James
2 Walk Tom James
3 Sit James
3 Sit James
3 Sit James
3 Sit James
4 Stand Judy James
4 Stand Judy James
4 Stand Judy James

From this data, I would like to create three named ranges in VBA:

A list including one instance of each row which contains TomA list including one instance of each row which contains JudyA list including one instance of each row which contains JamesTom's list:

1 Run Tom Judy James
2 Walk Tom James

Judy's list:

1 Run Tom Judy James
4 Stand Judy James

James' list:

1 Run Tom Judy James
2 Walk Tom James
3 Sit James
4 Stand Judy James

Is this possible? These lists could be very long (10-15k lines).
It doesn't necessarily have to be a named range, It would be acceptable if these lists were copied into their own sheets.

Thanks!
-Aaron

I am trying to pass a variable into a function. Every time I attempt to do so, the variable within the function is blank. Let me show you my code:

In this part of the code, I am pulling 6 digits that are dates, but just not formatted as dates (example 060105 for June 1, 2005, 123104 for Dec 31, 2004, etc...) and putting them into the variable strFunctionDate.

	VB:
	
 TransformDate() 
    strFunctionDate = Right(ActiveCell.Worksheet.Name, 6) 
    Call FormatDate 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
This is the function. strFunctionDate is always Empty when it gets to this point, whereas I would like it to retain the value as noted above.

	VB:
	
 FormatDate() 
    If Len(strFunctionDate) = 6 Then 
        strFunctionDate = Left(strFunctionDate, 2) & "/" & Mid(strFunctionDate, 3, 2) & "/" & Right(strFunctionDate, 2) 
    ElseIf Len(strFunctionDate) = 7 Then 
        strFunctionDate = Left(strFunctionDate, 1) & "/" & Mid(strFunctionDate, 2, 2) & "/" & Right(strFunctionDate, 4) 
    ElseIf Len(strPaymentDate) = 8 Then 
        strFunctionDate = Left(strFunctionDate, 2) & "/" & Mid(strFunctionDate, 3, 2) & "/" & Right(strFunctionDate, 4) 
    End If 
End Function 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Please help.

Thank you.

I have 344 identical looking ranges. I know I can use a named range in the VLOOKUP formula for the "table_array" but I need an automated way of getting the correct named range into the formula because the required range changes very frequently. I have a text formula that creates the correct name for the range but the VLOOKUP function does not read that. Any Help???

How do I refer to a named range in VBA.

I have a Range named "Parts" I use for a ComboBox list.

How can I refer to the selected item by the range name or is it easier to refer to the ComboBox selection in VBA.

I want to past the item selected in the ComboBox into another sheet for a parts list.

Thanks!!

I have a range of numbers. I would like to pass this range to a function in visual basic (sort of like the sum function, eg: SUM(A1:A10)).

How do I pass a range to a function. I tried paramarray, but in that instance you have to specify each cell in the array. I need it to be in the format A1:A10.

Thank you.

Hi All, first post here although I have honestly used this place countless times, so first of all an introductory thanks is in order I guess..
I am trying to pass a holiday range to a workday function. I have a two letter handle (GY/ES as examples) and about 10 holiday calendars to choose from. each calendar is named according to a two letter handle and "Hols" as in "GYHols"/"ESHols", etc..
When I use =workday(A1,A2,GYHols) function works as it should. When I use =workday(a1,a2,a3&"Hols") it returns a #value error. this is the same no matter what I pass to it.
Is there a way I can pass a string value as a named range within this function or am I going to have to rely to a lot of nested ifs?

just for info,
A1 = start day
A2 = days count
a3 = two letter ID

Thanks for your help!
Filippo

I need to cycle through some named ranges (zones) and perform actions on the cells withing those ranges. I have worked out the actions that take place in the individual ranges and now want to cycle through the ranges. Im getting an OBJECT REQUIRED error when I try to pass the "name" of the names ranges into the range.

there are 4 zones named:
zoneAPrng
zoneASrng
zoneFPrng
zoneFSrng

Dim zonNm As Name
Dim Rng1 As Range
...
For Each zonNm In ThisWorkbook.Names

    If zonNm.Name Like "zone*" Then
          Worksheets("Supervisor").Activate
         Set Rng1 = zonNm.RefersTo<----------------Error
*code*
Next zonNm
Ive tried several variations after reading the help on the error but am at the point that Im just easter egging. Each thing produces a new error.

Set me strait please
Robert

Hi All

Is there a way of defining dynamic named ranges in VBA (Excel 2003)? I have an application which heavily uses named ranges, which was seriously bloated. When I exported it to a new workbook to clean it, I found that the dynamic named ranges have all been lost!! Any solutions or ideas?

Many thanks

iFixem

I'm trying to concatenate text with different cell references within my workbook that when combined create the name of a range I have within the same workbook. I then will sum a sumrange, that is also a concatenation of text and cell references. I'm getting nowhere fast. When I type the name ranges into the sumif formula everything is great. When I create those same name ranges using concatenation it all falls apart. I'm guessing SUMIF and concatenation can't play nice together. I've also tried vlookup with sumif and didn't get anywhere.

Example:
=SUMIF(CONCATENATE("CS",YMKEY,"KEY"),CONCATENATE($A6,"TX",IF(YMKEY="Y","Y",MonthNum)),CONCATENATE("Data!MITSUBISHI1IND",YEARNUM)

References:
YMKEY = "Y" or "M"
$A6 = the name of a county
MonthNum = the number of the month (1 - 12)
YearNum = the year using four digits (i.e. 2008)

The ranges attempting to be referenced by the concatenation:
CSMKEY, CSYKEY, Data!Mitsubishi1IND2007, Data!Mitsubishi1IND2008, etc.

Any suggestions?

Can I insert the actual Name of a Named Range into a cell on my worksheet?
example: I name the range B1355:P1397 'Current Forecast'. Can I have cell
B1398 automatically populate the Actual Name of the Range, 'Current Forecast'?
Thanks. Tondos.

I'm trying to define a named range in VBA using the following syntax.
However, it gives me an error regarding the equal sign and if I remove the
equal sign, then the named range is defined but it treats the formula as a
text value so it doesn't evaluate it. I'm sure there's something easy that
I'm overlooking but what is it?

Any help is appreciated.

ActiveWorkbook.Names.Add Name:="PivotRange", RefersToR1C1:= _
"=OFFSET(Sheet1!$A$1,0,0,COUNTA(Shee1!A:A),10)'"

--
Richard

I need to create a Named Range via VBA

For each cell in Column A, if the value of the cell = "ABCD" then include the entire row in the Named Range

So if A8, A13 & A18 contain "ABCD" then
MyNewNamedRange ='Sheet1'!$8:$8,'Sheet1'!$13:$13,'Sheet1'!$18:$18

Thank you

All:

I am attempting to pass a cell such as $C10 into a function. Inside
the function, I have the following code:

Dim Rng1 as Range
Set Rng1 = Range("$C10")

This code segment works. Now, if I attempt to pass a named cell into
the code, it compiles but gives the following error message when
"Watched". <Expression not defined in context> Type = Empty

Dim Rng1 as Range
Set Rng1 = Range(NamedCell)

Any suggestions?

Thanks in advance.

Cheers.

Some help required please.

I have created a VBA function using select case where a constant value is asigned to a variable. This works perfectly well. The last element of the select case is directed to a vlookup worksheet function where the value of the variable is found from a table in a worksheet. This also works perfectly well when I use a set named range for the range property.

However the data I am querying is quite extensive and spreads over 24 months, each month has two columns of data which form the lookup table for that month. My function is intended to be pasted as a formula in each one of the 24 months. In order for this to work I have created a dynamic named range (using offset and indirect) that selects a range specific to the month. This works in the worksheet and selects the correct range for each month. When I use the dynamic named range for the range property in the vba vlookup function I get a #value! error.

Is there an obvious fix for this please, its getting a bit infuriating!

Many thanks

Howdy,

I'm trying to make an Excel VBA function that generalizes the SUMPRODUCT worksheet function. This new function would take two named ranges and two conditions on them and then return the count of rows that meet those conditions

For example, imagine that I have the following two-column worksheet:

Team || Win/Loss
------------------------
1 W
2 L
1 W
3 W
1 L

I then name the first column's data as "TEAMNAME" and the second column's data as "OUTCOME".

Using SUMPRODUCT directly, I could write

=SUMPRODUCT((TEAMNAME="1")*(OUTCOME="W"))

to see that team 1 won twice.

However, I want to write a VBA function that will take these ranges and conditions and return this sumproduct value,e.g.

=MYFUNC(TEAMNAME,"1",OUTCOME,"W")

I want to generalize in this way because I have many different two-column pieces of data where I want to use the same centralized code (and in truth my real problem has much more data than this example). The problem is that I'm having trouble passing the named ranges "TEAMNAME" and "OUTCOME" to my VBA SUMPRODUCT function and getting it to do the counting. Any suggestions or examples that work?

Many thanks, JP

Your code (not counting the last bits that I don't have) worked ok for me.

Are you sure you're passing the range from the correct worksheet?

Kurt M. Sanger wrote:
>
> For years I've been passing ranges into Excel functions, then converting them
> into an array so that I can use them. But today I'm getting an empty array
> instead of a full one. If I pass in a 4x4 array my function seems to work.
> But when I pass in a 27x10 array I get nada. After the assignment "arrX =
> arrayXIn.Value" the debugger shows arrX is the correct size but its empty.
> Here's my code. Any suggestions?
>
> Public Function funcExcelGenXXInv(arrayXIn As Range)
> Dim arrX As Variant '27 x 10
> Dim arrayXPrime As Variant '10 x 27
> Dim arrayXpX As Variant '10 x 10
> Dim arrayXpXInv As Variant '10 x 10
> Dim intNRows, intNCols As Integer
>
> arrX = arrayXIn.Value
> 'Find size of X array.
> intNRows = UBound(arrX, 1)
> intNCols = UBound(arrX, 2)
>
> arrayXPrime = funcTranspose(arrX)
> arrayXpX = funcMultM(arrayXPrime, arrX)
> arrayXpXInv = funcInvMatrix(arrayXpX)
> funcExcelGenXXInv = arrayXpXInv
> End Function

--

Dave Peterson


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