R – VBA point variable to range

objectrangevba

I want to point to a cell as a range in VBA. I've tried using:

         Dim range
         range = Sheet("sheet").Range("A1")

But this just returns the value in the range. What I actually want is the range object so I can manipulate it, e.g. by setting range.Value = "Hello"

Any ideas?

Best Solution

First, I strongly recommend you to make explicit declaration of variables in your code mandatory. Go to Tools - Options, in the Editor tab check "Require variable Declaration", or put Option Explicit in the first line of all your scripts.

Second, I think there is a small typo in your code, it should be Sheets.("sheet").

To answer your question, with range = Sheets("sheet").Range("A1") you are assigning a value variable, not an object. Therefore the default variable of the range object is implicitly assigned, which is value. In order to assign an object, use the Set keyword. My full example code looks like this:

Option Explicit

Public Sub Test()
    Dim RangeObject As range
    Set RangeObject = Sheets("Sheet1").range("A1")    
    RangeObject.Value = "MyTestString"
End Sub

This should put the text "MyTestString" in cell A1.

Edit: If you are using named ranges, try RangeObject.Value2 instead of RangeObject.Value. Named ranges do not have a Value property.

Related Solutions

Python – Calling a function of a module by using its name (a string)

Assuming module foo with method bar:

import foo
method_to_call = getattr(foo, 'bar')
result = method_to_call()

You could shorten lines 2 and 3 to:

result = getattr(foo, 'bar')()

if that makes more sense for your use case.

You can use getattr in this fashion on class instance bound methods, module-level methods, class methods... the list goes on.

Python – How to use a decimal range() step value

Rather than using a decimal step directly, it's much safer to express this in terms of how many points you want. Otherwise, floating-point rounding error is likely to give you a wrong result.

You can use the linspace function from the NumPy library (which isn't part of the standard library but is relatively easy to obtain). linspace takes a number of points to return, and also lets you specify whether or not to include the right endpoint:

>>> np.linspace(0,1,11)
array([ 0. ,  0.1,  0.2,  0.3,  0.4,  0.5,  0.6,  0.7,  0.8,  0.9,  1. ])
>>> np.linspace(0,1,10,endpoint=False)
array([ 0. ,  0.1,  0.2,  0.3,  0.4,  0.5,  0.6,  0.7,  0.8,  0.9])

If you really want to use a floating-point step value, you can, with numpy.arange.

>>> import numpy as np
>>> np.arange(0.0, 1.0, 0.1)
array([ 0. ,  0.1,  0.2,  0.3,  0.4,  0.5,  0.6,  0.7,  0.8,  0.9])

Floating-point rounding error will cause problems, though. Here's a simple case where rounding error causes arange to produce a length-4 array when it should only produce 3 numbers:

>>> numpy.arange(1, 1.3, 0.1)
array([1. , 1.1, 1.2, 1.3])

Best Solution

Related Solutions

Python – Calling a function of a module by using its name (a string)

Python – How to use a decimal range() step value

Related Question