0

I'm new to VBA and I'm trying to understand how to correctly deal with current selection range referrences in VBA. I already spent an hour searching for a solution to it without success.

I used the code below (I found it in another stackoverflow post, which works fine) and changed it to use the current selection range instead of a range specified within the code as it was originally. But it´s returning 424 object required error.

mySelection variable is a Range type and so is the rng makeUpper parameter. When I inspect this variable, it has the correct content (my manually selected cell range) and correct type (range). Why isn´t it being accepted as a valid object? I read in a VBA documentation that when we skip to specify the upper levels of an object hierarchy, Excel simply assumes it should look in the currently active Workbook or Worksheet. So what am I missing?

Sub test()
'    With Worksheets("Sheet1")
'        makeUpper .Range("A2:A1000000")
'    End With

' Changed to this...
    Dim mySelection As Range
    Set mySelection = Selection
    makeUpper (mySelection)
End Sub

Sub makeUpper(rng As Range)
    Dim v As Long, vUPRs As Variant
    With rng
        vUPRs = .Value2
        For v = LBound(vUPRs, 1) To UBound(vUPRs, 1)
            vUPRs(v, 1) = UCase(vUPRs(v, 1))
        Next v
        .Cells = vUPRs
    End With
End Sub
felima
  • 91
  • 6
  • 2
    You are calling a Sub, not a Function, so you do not use parenthesis. `makeUpper mySelection` - That will get you to your next error. – braX Jun 10 '21 at 06:25
  • 1
    Also would you be interested in a different approach? Just one line to achieve what you want. `mySelection = Evaluate("index(upper(" & mySelection.Address & "),)")` [Convert an entire range to uppercase without looping through all the cells](https://stackoverflow.com/questions/19985895/convert-an-entire-range-to-uppercase-without-looping-through-all-the-cells) – Siddharth Rout Jun 10 '21 at 06:28
  • 2
    When surrounding a parameter by parenthesis (whether in a `Sub` or `Function`), VBA evaluates the parameter. In this case the value of `mySelection` is a 2-d array with all values of the range. In other words `makeUpper (mySelection)` is equivalent to `makeUpper mySelection.Value`. As @braX said, removing the parenthesis should get rid of that error – Super Symmetry Jun 10 '21 at 06:44
  • @SiddharthRout shouldn't there be an `=` just before `index`? or am i missing something? – Super Symmetry Jun 10 '21 at 06:46
  • 2
    @SuperSymmetry. You can put it but it is not required in `Evaluate()`. See this example. `MsgBox Evaluate("=1+1")` and `MsgBox Evaluate("1+1")` – Siddharth Rout Jun 10 '21 at 06:48
  • @SiddharthRout Interesting, thanks :) – Super Symmetry Jun 10 '21 at 06:50
  • @SuperSymmetry have a look [at this answer](https://stackoverflow.com/a/56579194/3219613) where I explained the thing going on with the parenthesis. – Pᴇʜ Jun 10 '21 at 06:53
  • @Pᴇʜ Nicely explained. Thanks for sharing – Super Symmetry Jun 10 '21 at 13:21
  • 1
    @SiddharthRout it worked like a charm. Thank you. Tried to upvote your comment but clicked twice by mistake, now can´t vote it anymore :-/ – felima Jun 10 '21 at 17:06
  • lol no worries. glad you like it! – Siddharth Rout Jun 10 '21 at 18:14

0 Answers0