0

Background: I have a function which takes a couple of ranges as inputs and does some calculations. It is widely used, so I am keen to avoid changing it if possible.

However I now need to use the same function but with one of the ranges I need to have it operate on the negative equivalent of the range.

My question is: Is there an easy way to operate on a copy of a range? (without changing the underlying source data in the spreadsheet - I can't go turning all the values negative there, it is just needed in the calculation).

I tried writing a simply function (below). I don't know if I'm thinking of VBA ranges in the wrong way, but whenever I assign it the affects get reflected on the source (i.e. it's doing it in a By Reference manner rather than By Value, if that makes sense in this context)

Function NegateRangeContents(rRange As Range) As Range
  Dim r As Range
  Dim rOutput As Range

  Set rOutput = rRange

  For Each r In rOutput
    r.Value = -r.Value
  Next r

  Set NegateRangeContents = rOutput
  Set r = Nothing
  Set rOutput = Nothing

End Function

I did try Googling for an answer and looked for similar questions on here.

Maybe I'm missing something obvious and just need more coffee?! Any advice gratefully accepted!

Neil
  • 686
  • 1
  • 9
  • 27
  • ***I need to have it operate on the negative equivalent of the range.*** ? What is negative equivalent please? – bonCodigo Feb 01 '13 at 15:00
  • The range points a list of numbers. The function currently works through those numbers by being passed the range and comes out with a result (used elsewhere). I need the function to accept a range that has the contents values multiplied by -1 (i.e. if the figures are 5, 6, 22; then the function should use -5, -6, -22) – Neil Feb 01 '13 at 15:12
  • `Set` sets a reference to the object, so this will always work ByRef rather than ByVal. A question on [cloning objects](http://stackoverflow.com/questions/218696/cloning-objects-in-vba) may be useful, although it could be overkill. – Andrew Leach Feb 01 '13 at 15:13
  • @Neil You function doesn't seem to say that it doesn't accept negative values... Simply use `ByRef`. Even better you can `multiply by -1`... compared to the clumsy I have with `x - (x*2)` – bonCodigo Feb 01 '13 at 15:18
  • If you want to use the same function for both how does it know if its range is the "negative" version, or the regular type? What kind of operations is the function performing? When you say you want to avoid changing it presumably you mean the algorithm rather than the actual code, since you're going to need to make **some** changes... – Tim Williams Feb 01 '13 at 16:01
  • Thanks for the suggestions but in retrospect I think this isn't really the best place for me to have posted as it's causing more confusion than it's worth! – Neil Feb 01 '13 at 18:20
  • @Tim - I wanted to pass the range to some new function to make the values negative which would return a range, then pass that to the original function, so myOriginalFunction(someRange) became myOriginalFunction(negatingFunction(someRange)) – Neil Feb 01 '13 at 18:22

1 Answers1

0

If you current range is in Sheet 1,

  • First copy that into Sheet2 (or any new empty sheet to have a copy of that range data).
  • Feed that new sheet range into the Function
  • Next do the calculations that you need. Output accordingly.

As for getting the negative value of a cell value, try this:

For Each r In rOutput
    r.Value = r.Value - (r.Value * 2)
Next r

'-- eg. cell value = 2, the new value would be 2 - (2*2) = - 2

PS: Really not sure what you meant by I need to have it operate on the negative equivalent of the range. If you could clarify it, we may be able to provide a specific solution...

bonCodigo
  • 14,268
  • 1
  • 48
  • 91
  • Yes I suppose that would do it. I was hoping to not have to create anything in a sheet itself if it could all be done in VBA (in variables etc), but it may be the best option. Thanks! – Neil Feb 01 '13 at 15:19
  • Please also look into `Cloning` as @Andrew pointed out, if that helps you or if you are able to accommodate to the code. Post a proper question if you are stuck. – bonCodigo Feb 01 '13 at 15:32