Assignment of value to variable not working in VBA

Question

I am currently writing a program that contains a two dimensional newton raphson sub. it starts like this:

Sub newton11()

Dim x As Double, z As Double, tolerance As Double
Dim error_x As Double, error_z As Double
Dim iteration As Integer

iteration = 0
tolerance = 0.05
x = Range("h19").value
z = Range("h20").value

however when I run the sub, it doesn't work. When I was debugging I noticed when I hovered over x it was assigned a value of -344 when Range("h19") is 53 and z was assigned -5.12 when Range("20") is 0.

does anybody know how to fix this?

Does your workbook have multiple sheets or do you have multiple workbooks open? What are the values of ActiveSheet.Name and ActiveWorkbook.Name at that point in the code? Do those values return the name of the workbook and spreadsheet tab you are trying to pull values from? — Mark Balhoff
– Mark Balhoff, Commented Apr 16, 2015 at 21:01

Matteo NNZ · Accepted Answer · 2015-04-16 21:02:38Z

6

Always define the sheet where you pull the data from. If you write:

x = Range("H19").Value

by default you're saying:

x = ActiveSheet.Range("H19").Value

which is probably containing the value -344 while you were waiting for 53. With this:

x = Sheets("myGoodSheet").Range("H13").Value

you're sure you're referencing to the proper one. And as Mark says in his comment, even better if you reference the correct workbook with Workbooks(j) just in front of the Sheets collection.

edited Apr 16, 2015 at 21:02

answered Apr 16, 2015 at 21:00

Matteo NNZ

12.8k13 gold badges62 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mark Balhoff Over a year ago

+1 for reinforcing in a full answer the importance of always explicitly naming the workbook and worksheet. Heck you can do it by creating Worksheet objects at the top of your code. Just make sure you do it. Seems to be a common issue. Creates nagging reliability issues in small code like this but exponentially more difficult issues (and hard to diagnose) when you expand to, for example, multiple code modules running from buttons across a multi-tab spreadsheet.

Community · Accepted Answer · 2017-05-23 11:57:04Z

2

What you're describing isn't possible.

Instead of this:

x = Range("h19").value
z = Range("h20").value

Extract variables:

Dim xRange As Range, yRange As Range
Set xRange = Range("h19")
Set yRange = Range("h20")

x = xRange.Value
y = yRange.Value

Now place a breakpoint on the x = xRange.Value line, and use the locals window (from the View menu) to inspect the runtime value of xRange and yRange - then F8 and inspect the runtime value of x: the two are the same.

See @Matteo's answer for the why.

edited May 23, 2017 at 11:57

CommunityBot

11 silver badge

answered Apr 16, 2015 at 21:02

Mathieu Guindon

71.5k8 gold badges113 silver badges250 bronze badges

3 Comments

j_needs_help Over a year ago

I implemented this but it is still coming up with the same error! when I hover over xRange.Value it says it is 53 but when I hover over x it is -345. There is only one sheet on this, but two modules.

RubberDuck Over a year ago

@j_needs_help are you checking the value while the yellow line is on it, or after you've stepped to the next line?

j_needs_help Over a year ago

@RubberDuck it is not the yellow line. It's pale yellow boxes when I hover over the variables. I've checked the following formula are working correctly

Collectives™ on Stack Overflow

Assignment of value to variable not working in VBA

2 Answers 2

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related