Convert string to long in excel macro

How can I convert string to long in excel macro. CLng is giving me type mismatch error

Dim wStr As String
Dim w As Long

w开发者_运维知识库Str = "=RAND() * 0.3 + 0.35"
w = CLng(wStr)

The root cause of your error is that CDbl expects a numeric value or a string that looks like a number. the string "=RAND() * 0.3 + 0.35" itself does not look like a number, even though it will evaluate to a number.

What are you actually trying to achieve here?

If its to get a long integer result from the formula =RAND() * 0.3 + 0.35, use

Dim w as Long
w = Rnd() * 0.3 + 0.35

If its to emulate a cell formula use

Dim w as Long
w = Application.Evaluate("=RAND() * 0.3 + 0.35")

As to the formula itself, why this construct? It will return Single in the range [0.35, 0.65) which when rounded to a Long will return 0 or 1 at 50% probability of each.
Why not use

w = Rnd()

or

w = Application.Evaluate("=RAND()")

or

w = Application.WorksheetFunction.RandBetween(0, 1)

or is there some other reason I've missed?

Try the formula for w below.

w = CLng(Evaluate(wStr))

Or forget trying to use an "Excel formula", and go straight to VBA with with its random function counterpart

w = CLng(Rnd() * 0.3 + 0.35)