0

I'm new to VBA and I do not know what is happening here. I run a simple function like this:

    Function OnlyNumsRange(myRange As Range) As Range
       Dim cell As Range, newRange As Range

       Set newRange = myRange
    
       Dim rng As Range
       For Each rng In newRange
           rng.Value = rng.Value + 10
       Next
        
       Set OnlyNumsRange = newRange
    
    End Function

Given a range, that code is supposed to return a new range with +10 on each cell. This function works perfectly for VBA debugger, but when I try to run it on my excel sheet, it raises a #VALUE! error. Thank you in advance.

Cnoob
  • 3
  • 1
  • What are you actually trying to do? An example of the data you're working with, as well as your desired output, would make your issue easier to understand. Also, how are you running this in the debugger that "works perfectly"? Perhaps it would also be helpful to check out "[ask]" as well as how to create a [mcve]. – ashleedawg Feb 07 '22 at 09:17
  • 2
    Functions called from a worksheet should not return values to other cells, only to the cell(s) the function is/are in. There are ways round it, but it's not good practice. – Rory Feb 07 '22 at 09:17
  • I have a column with values 20, 30... but I should not make any change on these cells. I just want to create another column with the values modified. But if its not good practice, I will try to make it differently. Thank both of you! – Cnoob Feb 07 '22 at 09:35
  • If you want a function that returns an array and thus spills to several cells, just [return an array](https://stackoverflow.com/q/67879054/11683). – GSerg Feb 07 '22 at 11:21
  • Thank you, its the first time I work with ranges (and vna) and I did not know I was not using the proper variable type – Cnoob Feb 07 '22 at 12:25

1 Answers1

-1

To return a value for the function, you assign the value to the function name, in this case, OnlyNumsRange. You don't use Set in this case.

jm19x62
  • 1
  • 1
  • `You don't use Set in this case` - if the function returns an object, which `Range` is, you absolutely do. – GSerg Feb 07 '22 at 11:17