Excel Passing Named Range as Argument 1

[jjefferson]

I've been banging my head against this simple thing, tried suggested approaches found in searches without success, and need to ask for assistance.

I have three named ranges on a sheet, each referring to a single cell. Three buttons each call their own unique sub in the module.

I would like each of those button-subs to be able to call a generic sub in the module, passing the range or range name (whatever works) as an argument. That generic sub would update the value in the range that was passed to it.

I have tried passing the range name as a string, and also as a range object, but end up getting various errors, mostly "Method 'Range' of Object '_Global' failed".

I have tried (all abbreviated but is illustrative):

Sub Sub1()
  Dim strNR as String
  strNR = "NamedRange1"
  Call GenSub(strNR)
End Sub

Sub GenSub(strRangeName as String)
  ...some stuff goes on...
  Range(strRangeName).Value = "Something"
End Sub

I have also tried:

Sub Sub1()
  Dim rngRN as Range
  Set rngRN = Range("NamedRange1")  <-- usually errors here
  Call GenSub(rngRN)
End Sub

Sub GenSub(rngRangeObj as Range)
  ...more stuff...
  rngRangeObj.Value = "Something"
End Sub

Still very new to the VBA world and the Excel object model so I'm certain (hope) that I am missing something very basic and simple. I'd like Sub2 and Sub3 to also be able to call GenSub passing a different named range to be updated.

Thanks for a gentle shove in the right direction!

 

[SkipVought]

hi,

First are you certain that you have a Named Range?

If so, what is the Sheet Name?

How are you calling Sub1? This should be called in the Button_Click event!

Sub Sub1()
  Call GenSub(Sheets("YourSheetName").[NamedRange1])
End Sub

Sub GenSub(rng as Range)
'  ...some stuff goes on...
  rng.Value = "Something"
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[jjefferson]

Certain that I have the ranges named. Am able to address them successfully when I hard-code the range name.

The sheet is "Sheet1", and is the only sheet in the workbook.

Sub1 is the "Assigned Macro" for a particular button form control (as opposed to an ActiveX button control). This method seems to work fine for other buttons that perform different tasks, but if I need to do this differently to make this work, I can. The click is definitely hitting this sub.

Reworked the code per your example (explicitly referring to the sheet, etc.) Got a different error at the "Call" statement of "Application-defined or object-defined error". Used both your example and "Sheets("Sheet1").Range("NamedRange1") as the argument. I'll research that error as it is new and may turn on the light bulb, but additional advice is appreciated if any ideas pop up.

Thanks.

 

[SkipVought]

This is my exact code.

Sub Sub1()
  Call GenSub(Sheets("Sheet1").Range("NamedRange1"))
End Sub

Sub GenSub(rng As Range)
'  ...some stuff goes on...
  rng.Value = "Something"
End Sub

It runs without error!

You have some missnamed object reference!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[jjefferson]

Fat fingers when correcting my code to use your example; properly red-faced.

Works fine with your suggested changes. Initial attempts were spelled correctly but didn't use your syntax.

Thanks!

 

[SkipVought]

You might consider something like this using 2 button example...

Sub Buttion1_Click()
   GenSub 1
end sub

Sub Buttion2_Click()
   GenSub 2
end sub

Sub GenSub(i as integer)
  Dime sRng as string
  Select Case i
    Case 1: sRng = NamedRange1
    Case 2: sRng = NamedRange2
  end select
 'do stuff
  Sheets("Sheet1").Range(sRng).Value = "Something"
End Sub

This way, you have only one procedure to maintain

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[jjefferson]

Excellent suggestion; implementing that now and typing more carefully this time.

Thanks!