0

I am working with named ranges (mostly in Sheet 2) and am in the process of creating a summary in a sheet named "Signups". I am mostly cutting and pasting code that has worked in other parts of the program. For some reason VBA will not Select a range in Sheet 2.

In the code below, "SignupPairs" is a range in sheet "Signups" and "NewMem" is a range in "Sheet2".

''''''''''
MsgBox "SignupPairs 2,1 " & Range("SignupPairs")(2, 1)
Range("SignupPairs")(2, 1).Select
MsgBox "NewMem 1,1 " & Range("NewMem")(1, 1)
'Sheets("Sheet2").Range("NewMem")(1, 1).Select  'Doesn't work.
Range("NewMem")(1, 1).Select
''''''''''

The first four lines have been added for debugging purposes. The last line is the one that brought me to a screeching halt. Line four was an attempt to be specific about the sheet I wanted to use; it didn't work. MsgBox correctly reports the contents of cells in "SignupPairs" and "NewMem" but I can't select NewMem(1,1) in order to do a "Range(Selection, Selection.End(xlDown)).Rows.Count.

For bonus points: Does anyone have a link, or a reference, for a good primer or manual on VBA so I can educate myself and not have to ask these simple questions? VBA for Dummies only got me into this mess.

P.S. Clicking "F1" on "Select" in VBA Editor sends me to a page on Select Case which seems like a dead end.

Don
  • 1
  • 1
  • 2
    Read this https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba You can't select a cell on a non-active sheet so you have to either activate the sheet first or use `application.goto`. – SJR Apr 24 '19 at 17:47
  • 1
    Since these are named ranges, you can also get their data directly to an array in vba, like 'ThisWorkbook.Names("SignupPairs)..RefersToRange.Value` – jessi Apr 24 '19 at 17:51
  • @jessi that would make a great, upvote-worthy answer! – Mathieu Guindon Apr 24 '19 at 19:19
  • I created an answer from my comment. I hope that it helps the op move forward with this project. I appreciate the encouragement @MathieuGuindon – jessi Apr 24 '19 at 20:14

1 Answers1

1

Since you are new to VBA, I highly recommend always writing down what you intend to do as comments in your code. This will help helpers and future you.

I am not sure what you want to do with the data in your summary sheet. I recommend that you create a reference to your summary sheet and then you can use arrays from your named ranges.

Here's a way that you can get both Sign Up Pairs and New Mem into arrays and then msg box out what you were looking at (but I assume that you want to match them or do something else)

Sub doSomethingWithSignups()
    Dim ws As Worksheet, signUps As Variant, newMems As Variant
    ' these arrays will have whatever data you have identified in the Named Ranges
    signUps = ThisWorkbook.Names("SignupPairs").RefersToRange.Value2
    newMems = ThisWorkbook.Names("NewMems").RefersToRange.Value2

    ' you can do anything with these arrays now
    MsgBox("SignupPairs 2,1 " & signUps(2, 1))
    MsgBox("NewMem 1,1 " & newMems(1, 1))
End Sub
jessi
  • 1,438
  • 1
  • 23
  • 36
  • Thank you jessi. I need to go back through my code and figure out how to use signUps instead of Range("SignupPairs"). As I have been working through this code I have been using meaningful range names to help keep track of what I am doing. Using signUps is a lot simpler than using Range("SignupPairs") but it would help to know why signUps is functionally better. My gut reaction is that multiple levels of abstraction risks more unintended consequences. Can I hand off signUps to a Sub? If I redimension signUps in the Sub, will that apply in the calling routine and in other Subs? – Don Apr 24 '19 at 21:33
  • You can change the dimensions of a named range in your Sub. Whether or not it impacts other subs depends on when Subs are called and what scope you give it. I'd be hesitant to change the shape of a named range a bunch unless you know what impacts you are having. You could make the range large to begin with and have code that checks for empty. Or, you could dynamically name the range at the start of your sub. You can definitely pass arrays around Subs and Functions for more work. – jessi Apr 24 '19 at 21:37
  • OK Thanks for the help. I think I am starting to see part of the problem: A named rang is a property of the worksheet while signUps is a VBA variable that works better in the VBA environment. – Don Apr 24 '19 at 21:54
  • Yes! A named range is an easy way to refer to a group of cells. signUps (in my example) is an array that is not on a worksheet. You can do a lot with arrays quickly. – jessi Apr 24 '19 at 21:56
  • What does .Value2 mean? Is it the same as .Value? Does anyone have a suggestion for a reference where I can look this up so I don't have to take people's time with simple questions? – Don Apr 24 '19 at 22:03
  • I always go to the Docs https://learn.microsoft.com/en-us/office/vba/api/excel.range.value2 – jessi Apr 24 '19 at 22:06
  • Never mind. I just "F1" Value2 and found the explanation. Sometimes that works well. – Don Apr 24 '19 at 22:08
  • It is still broken. At the top of my program I Dim newMem as Variant and set it equal to the name "NewMem" per your example. MsgBox newMem(1,1) works, the next statement; newMem(1,1).Select does not. Is there something I need to learn about addressing this array? – Don Apr 24 '19 at 22:34
  • Fixed it! I needed to Activate Sheet2. – Don Apr 25 '19 at 22:04
  • I'm glad you solved this @Don - be very cautious with Activate. This is switching between the sheets you are using. You can accidentally overwrite things if you now output to that sheet. – jessi Apr 26 '19 at 01:28
  • also, regarding the comment before it worked for you. I apologize for pasting in `newMem(1,1).Select` - that would not work. If you want to do something with `newMem(1,1)` it is just there. My mistake as I was trying to translate your code to an array. – jessi Apr 26 '19 at 01:32
  • OK Thanks for your help. – Don Apr 29 '19 at 22:02