Range of inputs and graphing of result

To the VBA Express community,

Any help or tips would be appreciated.

My aim is input a range of values for one of the inputs in the following equation (V), and to then graph the output (XoverL) against that variable.

Code:

---

Function XoverL(u As Double, V As Double, e As Double, dc As Double, N1 As Integer, N2 As Integer) As Double
    XoverL = N1 * u * V * ((1 - e) ^ (3 / 2) * (1 + 56 * (1 - e) ^ 3) / dc ^ 2)
End Function
 
Sub test()
    MsgBox XoverL(1, 0.05, 0.752, 0.000023, 64, 56)
End Sub

---

Thanks,
ITY

I would set out the values that you want to use as the input values to the function on a worksheet using the same order as they are in the input to fnction, eg.
U in column A , V in column B, etc
Then keep adding the new values in the rows below. Then run this code which will iterate through all the values and write the result for each row in Column G, So you can easily plot any of the input paramters agains the function values

Code:

---

Sub test()
Dim u As Double
Dim V As Double
Dim e As Double
Dim dc As Double
Dim N1 As Integer
Dim N2 As Integer
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
inarr = Range(Cells(1, 1), Cells(lastrow, 6))
For i = 2 To lastrow
    u = inarr(i, 1)
    V = inarr(i, 2)
    e = inarr(i, 3)
    dc = inarr(i, 4)
    N1 = inarr(i, 5)
    N2 = inarr(i, 6)
    Cells(i, 7) = XoverL(u, V, e, dc, N1, N2)
Next i
End Sub


Function XoverL(u As Double, V As Double, e As Double, dc As Double, N1 As Integer, N2 As Integer) As Double
XoverL = N1 * u * V * ((1 - e) ^ (3 / 2) * (1 + 56 * (1 - e) ^ 3) / dc ^ 2)
End Function

---

Thank you for your reply and sorry for such a delay in getting back to you.

This code works as desired for that purpose.

Code:

---

Sub test()   
Dim V As Double
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
inarr = Range(Cells(1, 1), Cells(lastrow, 3))
For i = 2 To lastrow
    V = inarr(i, 3)
    Cells(i, 7) = X(V)
Next i
Worksheets(1).Range("G1").Value = "dP / L"
End Sub
 
 
Function X(V As Double) As Double
 X = V * Worksheets(1).Range("E2") / Worksheets(1).Range("D2")
End Function      'Here the constant values are brought from the worksheet, whilst the variable input is still accounted for.

---

For a similar situation, but for one where there is only one variable, is the above code as efficient/correct as it could be?

Here column C has the rising values for V, but column A, B, D, E etc contain constants.

This code works with this set up.

However, I have applied it to another scenario on another worksheet and it does not run.

Code:

---

Sub test2()
Dim Nr As Double
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
inarr = Range(Cells(1, 1), Cells(lastrow, 10))
For i = 4 To lastrow
    Nr = inarr(i, 10)
    Cells(i, 16) = DIP(Nr)
Next i
End Sub
 
Function DIP(Nr As Double) As Double
DIP = 1 + ((1.996 * 5) / Nr)
End Function

---

Here, the varying input is in column J starting at row 4. The constants have been inputted manually.

Thanks,
ITY

I am not quite sure why you are using a function for your calculations at all. in the first routine your function just seems to multiply V by a constant, (unless E2 or D2 has some equation that reads from column G).
You can make your sub more efficient by only calculating E2/D2 once at the start of the sub
this code will do exactly the same as yor code ( with the assumption that E2 and D2 don't change with each iterration)

Code:

---

Dim V As Double
  K = Worksheets(1).Range("E2") / Worksheets(1).Range("D2")
    lastrow = Cells(Rows.Count, "A").End(xlUp).Row
    inarr = Range(Cells(1, 1), Cells(lastrow, 3))
    For i = 2 To lastrow
        V = inarr(i, 3)
        Cells(i, 7) = K * V
    Next i
   
    Worksheets(1).Range("G1").Value = "dP / L"

---

This is also true for your second function all you are doing is dividing colum C into a constant again, why bother about the function?
This code does the same as your second function:

Code:

---

    Dim Nr As Double    k2 = 1 + (1.996 * 5)
    lastrow = Cells(Rows.Count, "A").End(xlUp).Row
    inarr = Range(Cells(1, 1), Cells(lastrow, 10))
    For i = 4 To lastrow
        Nr = inarr(i, 10)
        Cells(i, 16) = k2 / (Nr)
    Next i

---

you say it doesn't run: try putting a breakpoint in the first instrunctio nand then stepping through using F8

Both of these are so simple I do have to ask why you are using VBA at all foir this since you can do both of these very easily using formula in the worksheet

You are correct, individually these can be solved just using the worksheet.

However, they are part of a much bigger project which will have several dozen columns (individual equations). These will relate back to a set list of inputs (some of which will have a minimum and maximum value with a specified increment value) and also forwards and backwards along these columns. Changing the maximum and minimum values and the increments of these inputs will change the number of outputs for some columns which will have a snowball effect on the entire worksheet (if set up correctly).

The final solution will depend on several of these equations, which will in turn bring all of the columns & inputs into play.

I have been able to set up equations that use the incremental values of inputs, but have been struggling to then use this list of outputs in the following equations.

The equation I originally sent is a generic equation, I was to then modify it by adding all of the other parts.

The code above works across the required columns, but the solution is rounded. As it sets everything to 'double' I was expecting a more accurate result, the exact solution ideally. Could you advise me on how to generate that in this case?

I have a code that brings inputs into an equation incrementally from specified minimum to maximum. My next aim is to incorporate that code with something similar to what you have provided, allowing for the previously calculated range of solutions to be inputted alongside an input that rises incrementally.

Quote:

---

As it sets everything to 'double' I was expecting a more accurate result, the exact solution ideally. Could you advise me on how to generate that in this case?

---

You can try using the Decimal variant sub-type. Can't approach it directly, you have to sneak up in using variant variables and CDec

XoverL2 is an example

Code:

---

Option Explicit
Function XoverL(u As Double, V As Double, e As Double, dc As Double, N1 As Integer, N2 As Integer) As Double
    XoverL = N1 * u * V * ((1 - e) ^ (3 / 2) * (1 + 56 * (1 - e) ^ 3) / dc ^ 2)
End Function
 

'CDec can be used in Excel VBA to convert to a Decimal data type.
'+/-79,228,162,514,264,337,593,543,950,335 for zero-scaled numbers, that is, numbers with no decimal places.
'For numbers with 28 decimal places, the range is +/-7.9228162514264337593543950335.
'The smallest possible non-zero number is 0.0000000000000000000000000001

Function XoverL2(u As Variant, V As Variant, e As Variant, dc As Variant, N1 As Long, N2 As Long) As Variant
    XoverL2 = CDec(N1 * u * V * ((1 - e) ^ (3 / 2) * (1 + 56 * (1 - e) ^ 3) / dc ^ 2))
End Function
 
 
 
Sub test2()
    MsgBox XoverL(1, 0.05, 0.752, 0.000023, 64, 56)
    MsgBox XoverL2(CDec(1#), CDec(0.05), CDec(0.752), CDec(0.000023), 64, 56)
End Sub

---

If you are incrementing variable between a minimum and a maximum value did you know that you can do this as a loop in VBA e.g

Code:

---

Sub test()

cnt = 0
For Weight = 45 To 100 Step 0.01
cnt = cnt + 1
MsgBox (Weight)
If cnt = 6 Then Exit For
Next Weight


End Sub

---

Paul_Hossler , This solved that particular issue - thank you.

offthelip Yes - I have been experimenting with this function of VBA. It is working for me as I wish it to, thank you