VBA For Loop Variables

Question

I'm relatively new to VBA and I'm trying to simulate a complex equation in Excel VBA because my data logger is outputting a CSV, and since the equation is so long and complex with 13 variables and 17 intermediary calculations, I'm trying to make variables to help make the code a lot more readable for both debugging (for now and when I do stuff like this in the future) and for making the modifications I'm making to the simulation more visual.

The basic structure of my code is as follows:

'Temperature = .Cells(i,1)
'WindSpeed = .Cells(i,2)
'SolarRadiation = .Cells(i,3)
'Humidity = .Cells(i,4)
'SimulatedValue = .Cells(i,5)

Sub Simulation()
With Worksheets("DataLoggerValues")
Dim i As Integer

For i = 2 To 184

.Cells(i,5) = (.Cells(i,1)+.Cells(i,3)/.Cells(i,2))^2 + .Cells(i,4)/37

Next i

End With
End Sub

I'd like to clean it up so the code line reads more like:

Simulated value = (Temperature+SolarRadiation/WindSpeed)^2 + Humidity/37

But every time I try declaring anything with .Cells(i,j) as a variable (I've tried string and array), I'll get a number of different errors, most often "Runtime Error 1004", but sometimes "Syntax Error" or "Expected New or type name" as compile errors.

Is it even possible to turn .Cells(i,j) into a variable, or do I need to take a different approach?

Thanks in advance!

`CELLS()` is a range. Sometimes it works without it, but I always use `CELLS(i,j).VALUE` to be clear that I am setting or getting the value of the cell. And I'm not sure why you have `.Cells`. Try it without the `.` — Rey Juna, Sep 05 '18 at 22:07
Welcome to Excel VBA programming. We all fall into the trap of Cell(row,column) and think "Oh this will be easy". What you want to do is to work with Ranges, a cell is a range. More importantly, learning how to use Named ranges and ListObjects will be critical to your success. Click here to see [Microsoft's Named Range Documentation](https://learn.microsoft.com/en-us/office/vba/excel/concepts/cells-and-ranges/refer-to-named-ranges) and [Chip Pearson](http://www.cpearson.com/Excel/DefinedNames.aspx) is my go to for most things VBA. — Jamie Riis, Sep 05 '18 at 22:21

Shelty · Accepted Answer · 2018-09-06T21:54:39.513

Your code should look something like this.

Sub Simulation()

    Dim i As Long
    Dim Temperature As Long
    Dim WindSpeed As Long
    Dim SolarRadiation As Long
    Dim Humidity As Long
    Dim SimulatedValue As Long

    With Worksheets("DataLoggerValues")

        For i = 1 To 100

            Temperature = .Cells(i, 1).Value
            WindSpeed = .Cells(i, 2).Value
            SolarRadiation = .Cells(i, 3).Value
            Humidity = .Cells(i, 4).Value


            .Cells(i, 5).Value = (Temperature + SolarRadiation / WindSpeed) ^ 2 + Humidity / 37

        Next i

    End With

End Sub

You can also use something like this. Just use that function as a normal excel function. You can then drag this with autofill. You can use it after you add the code to a module.

Function Simulation(Temperature As Long, WindSpeed As Long, SolarRadiation As Long, Humidity As Long)

    Simulation = (Temperature + SolarRadiation / WindSpeed) ^ 2 + Humidity / 37

End Function

Edit:

An answer to the comment below.

Sub Simulation()

    Dim i As Long
    Dim Temperature As Long
    Dim WindSpeed As Long
    Dim SolarRadiation As Long
    Dim Humidity As Long
    Dim SimulatedValue As Double

    With Worksheets("DataLoggerValues")

        For i = 1 To 100

            Temperature = .Cells(i, 1).Value
            WindSpeed = .Cells(i, 2).Value
            SolarRadiation = .Cells(i, 3).Value
            Humidity = .Cells(i, 4).Value

            '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

            SimulatedValue = (Temperature + SolarRadiation / WindSpeed) ^ 2 + Humidity / 37

            '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''


            .Cells(i, 5).Value = SimulatedValue

        Next i

    End With

End Sub

I guess you should read something about what objects are in a programming language. You can start with this:

https://analysistabs.com/excel-vba/objects-properties-methods/

What a problem was with your code is:

Worksheets("DataLoggerValues") is an object. It has some propeties. For example it has a name: "DataLoggerValues".

You can check it with

Sub Test()

    MsgBox Worksheets(1).Name

End Sub

If its index is one. It may be different. You can check it using another property of that object.

Sub Test()

    MsgBox Worksheets("DataLoggerValues").Index

End Sub

And now we are getting to the problem. Worksheets("DataLoggerValues").Cells(i, 5) is another object. It is a child object (a child class object).

It is not a single value!!! It is an object. It has different properties and methods. This is why: .Cells(i,5) =... doesn't make sense. Are you assigning that value to the .Cells(i,5).Address or .Cells(i,5).Value or maybe some different property? All of them 'store' values. You can read them or change them.

Maybe I should clear out why you have to use SimulatedValue differently. Well, with those first four cells it is simple. You create a variable, e.g. Dim Temperature As Long. It can store later assigned value somewhere in the computer's memory!!! Just assign some cell's value to it Temperature = .Cells(i, 1).Value.

You do the same with different variables and perform some calculations. As I did it in the new code, you can store that number (It has to be Double because it has to 'store some numbers after the comma') in another variable (If your Temperature, WindSpeed etc. are not integers then change Long to Double).

Now you can assign that value to the cells value .Cells(i, 5).Value = SimulatedValue. If you would have added the line SimulatedValue = .Cells(i, 5).Value somewhere before the calculations, you would just assign to the variable (SimulatedValue) a cell's value (which is 0 if the cell is empty or doesn't exist, I am not an expert. If you try to print the value, you get nothing but you can multiply anything by its value and you get 0). If you then write something like SimulatedValue = 5 you just assign new value to the variable. You don't do anything to that cell object (the cell, the cell's value).

I hope it is clear. I tried to be as concise as I could

I am just a beginner when it comes to vba and I don't know too much about programming so I may have used some terms incorrectly, bear that in mind. Maybe somebody will correct me. :)

Important stuff, when it comes to using floating-point variables (e.g. that Double). Read this:

Whats wrong with this simple 'double' calculation?

Compare double in VBA precision problem

I guess that if you assign that value straight to some cell's value the problem persists, although I haven't read that or try that.

.Cells(i, 5).Value = (Temperature + SolarRadiation / WindSpeed) ^ 2 + Humidity / 37

It may save you some time in the future if you do some calculations with floating-point numbers.

Thanks, this was very helpful and worked well. I am curious though why SimulatedValue can't be set as a variable also? I tried it and it just didn't work. It doesn't make any practical distance but would be a little cleaner. — Omar, Sep 06 '18 at 19:36
Well, I have been thinking about it and an easy solution came to my mind. I edited the answer and also explained why your code was wrong and I hope I cleared a few thing out. — Shelty, Sep 06 '18 at 21:56

score 0 · Answer 2 · answered Sep 05 '18 at 23:08

You don't actually need to loop this as Excel is smart enough to increment it then you can just copy and paste as value like so:

Sub Simulation()
With Worksheets("DataLoggerValues").Range("E2:E184")
    .Formula = "=(A2+C2/B2)^2+D2/37" 'Excel will increment the row relative to the row the formula is in
    .Copy
    .PasteSpecial xlPasteValues
End With
End Sub

You should mark Shelty as correct as his answer answers your specific question by moving the variable assignment inside the loop, I am just offering and alternative idea for you which will speed up your code.

score 0 · Answer 3 · answered Sep 05 '18 at 23:16

There are N number of ways to standardize your code. for the sake of simplicity, you can do like this

     Sub Simulation()

    Dim i As Long
    Dim Temperature As Range
    Dim WindSpeed As Range
    Dim SolarRadiation As Range
    Dim Humidity As Range
    Dim SimulatedValue As Range

    With Worksheets("DataLoggerValues")

    'just initialize the header part
    Set Temperature = .Cells(1, 1)
    Set WindSpeed = .Cells(1, 2)
    Set SolarRadiation = .Cells(1, 3)
    Set Humidity = .Cells(1, 4)
    Set SimulatedValue = .Cells(1, 5)


    End With
    Dim i As Integer

    For i = 1 To 183

     SimulatedValue.Offset(i, 0) = (Temperature.Offset(i, 0) + SolarRadiation.Offset(i, 0) / WindSpeed.Offset(i, 0)) ^ 2 + Humidity.Offset(i, 0) / 37

     Next i

    ' and finally
      Set Temperature = Nothing
      Set WindSpeed = Nothing
      Set SolarRadiation = Nothing
      Set Humidity = Nothing
     Set SimulatedValue = Nothing

    End Sub

Few tips: Avoid using With..End with, you can use named range or Tables or constant address for better performance and usability.

Seems over engineered assigning variables then releasing them, also I disagree with your comments regarding not using With..End with, What makes you think it slows the code down as opposed to any other method? if anything using a loop is what would slow this code down (see my answer for how to avoid this). — Dan Donoghue, Sep 05 '18 at 23:26
Generally "With..End with" is good for small objects but harmful if the referencing object contains more complex objects in it.. — Sajy Bhaskaran, Sep 06 '18 at 08:53

VBA For Loop Variables

3 Answers3