locked
Filling an Array with a for loop RRS feed

  • Question

  • I am trying to fill an array using a for loop. I previously constructed a much more simple code to do this, but now as I have tried to expand it I am not getting the value that I need. What am I doing wrong?


    Function OpExt(Cut_length As Single, Usage As Single, Cost As Single)

        Dim Min As Single ' Minumum Extrusion length
        Dim Max As Single ' Maximum Extrusion length
        Dim Grip As Single ' Required Scrap in inches for either cutting or stamping an extrusion
        Dim Cut_width As Single ' The amount of material removed by the saw
        Dim Rungs_per_ext As Single
        Dim Ext_per_day As Single
        Dim Scrap_per_ext As Single
        Dim Salvage_scrap_per_ext As Single
        Dim Salvage_scrap_per_day As Single
        Dim Salvage_lengths_per_year As Single
        Dim Savings_per_year(5) As Variant
        Dim i As Integer
        
        Min = 175 ' Minimum extrusion length in inches
        Max = 176 ' Maximum extrusion length in inches
        
         
            For i = Min To Max Step 0.25
                Grip = 4 ' The amount of scrap to cut an extrusion
                Cut_width = 0.1875
         
                Rungs_per_ext = i / (Cut_width + Cut_length)
                Ext_per_day = Usage / Rungs_per_ext
            
                If (Rungs_per_ext - WorksheetFunction.RoundDown(Rungs_per_ext, 0)) * Cut_length > (Grip + Cut_width) Then
                        Scrap_per_ext = (Rungs_per_ext - WorksheetFunction.RoundDown(Rungs_per_ext, 0)) * Cut_length
                    Else
                        Scrap_per_ext = ((Rungs_per_ext - WorksheetFunction.RoundDown(Rungs_per_ext, 0)) * Cut_length) + Cut_length
                End If
                
                Salvage_scrap_per_ext = Scrap_per_ext - Grip - Cut_width
                Salvage_scrap_per_day = Salvage_scrap_per_ext * Ext_per_day
                Salvage_lengths_per_year = (Salvage_scrap_per_day / i) * 365
                
                
                Savings_per_year(i) = (Cost * Salvage_lengths_per_year)                         
                                      
            Next
               
            OpExt = WorksheetFunction.Min(Savings_per_year) ' I want to return the lowest amount of savings
    End Function



    Friday, May 13, 2016 9:08 PM

Answers

  • Re: code stops

    This runs, but I don't have any idea if the answer is correct.
    Note the name change.
    '---
    Jim Cone


    '---
    Function OpExt_R1(Cut_length As Single, Usage As Single, Cost As Single)

    Dim Min As Single ' Minumum Extrusion length
    Dim Max As Single ' Maximum Extrusion length
    Dim Grip As Single ' Required Scrap in inches for either cutting or stamping an extrusion
    Dim Cut_width As Single ' The amount of material removed by the saw
    Dim Rungs_per_ext As Single
    Dim Ext_per_day As Single
    Dim Scrap_per_ext As Single
    Dim Salvage_scrap_per_ext As Single
    Dim Salvage_scrap_per_day As Single
    Dim Salvage_lengths_per_year As Single
    Dim Savings_per_year(1 To 5) As Double '<<< revised
    Dim i As Double '<<< revised
    Dim j As Long    '<<< new

    Min = 175 ' Minimum extrusion length in inches
    Max = 176 ' Maximum extrusion length in inches
    j = 1     '<<< new

    For i = Min To Max Step 0.25
    Grip = 4 ' The amount of scrap to cut an extrusion
    Cut_width = 0.1875

    Rungs_per_ext = i / (Cut_width + Cut_length)
    Ext_per_day = Usage / Rungs_per_ext

    If (Rungs_per_ext - WorksheetFunction.RoundDown(Rungs_per_ext, 0)) * Cut_length > (Grip + Cut_width) Then
    Scrap_per_ext = (Rungs_per_ext - WorksheetFunction.RoundDown(Rungs_per_ext, 0)) * Cut_length
    Else
    Scrap_per_ext = ((Rungs_per_ext - WorksheetFunction.RoundDown(Rungs_per_ext, 0)) * Cut_length) + Cut_length
    End If

    Salvage_scrap_per_ext = Scrap_per_ext - Grip - Cut_width
    Salvage_scrap_per_day = Salvage_scrap_per_ext * Ext_per_day
    Salvage_lengths_per_year = (Salvage_scrap_per_day / i) * 365

    'i replaced with j
    Savings_per_year(j) = (Cost * Salvage_lengths_per_year)
    j = j + 1 '<<< new
    Next

    OpExt_R1 = WorksheetFunction.Min(Savings_per_year)  '<<< revised
    End Function




    • Edited by James Cone Monday, May 16, 2016 4:22 PM more notes
    • Marked as answer by Tricky Steve Monday, May 16, 2016 7:42 PM
    Monday, May 16, 2016 4:04 PM
  • Re: more info in one cell over

    A function called from a worksheet cell can only return a value to the calling cell.
    This is a security precaution built into Excel.  You can return multiple values to the calling cell...
    '---
    Function OpExt_R2(Cut_length As Single, Usage As Single, Cost As Single) As Variant  'version R2
     Dim Min As Single
     Dim Max As Single
     Dim Grip As Single
     Dim Cut_width As Single
     Dim Rungs_per_ext As Single
     Dim Ext_per_day As Single
     Dim Scrap_per_ext As Single
     Dim Salvage_scrap_per_ext As Single
     Dim Salvage_scrap_per_day As Single
     Dim Salvage_lengths_per_year As Single
     Dim Savings_per_year(1 To 5) As Double
     Dim i As Double
     Dim J As Long
     
     Dim x As Variant    'version R2
     Dim y As Variant    'version R2
     
     Min = 175
     Max = 176
     J = 1

    For i = Min To Max Step 0.25
     Grip = 4
     Cut_width = 0.1875
     Rungs_per_ext = i / (Cut_width + Cut_length)
     Ext_per_day = Usage / Rungs_per_ext
     
     If (Rungs_per_ext - WorksheetFunction.RoundDown(Rungs_per_ext, 0)) * Cut_length > (Grip + Cut_width) Then
       Scrap_per_ext = (Rungs_per_ext - WorksheetFunction.RoundDown(Rungs_per_ext, 0)) * Cut_length
     Else
       Scrap_per_ext = _
       ((Rungs_per_ext - WorksheetFunction.RoundDown(Rungs_per_ext, 0)) * Cut_length) + Cut_length
     End If
     
     Salvage_scrap_per_ext = Scrap_per_ext - Grip - Cut_width
     Salvage_scrap_per_day = Salvage_scrap_per_ext * Ext_per_day
     Salvage_lengths_per_year = (Salvage_scrap_per_day / i) * 365
     
     Savings_per_year(J) = (Cost * Salvage_lengths_per_year)
     J = J + 1
    Next

    x = Application.WorksheetFunction.Min(Savings_per_year)             'version R2
    y = Application.WorksheetFunction.Match(x, Savings_per_year, 0)  'version R2
    OpExt_R2 = x & "_index " & y                                                           'version R2
    End Function
    '---

    Jim Cone
    • Edited by James Cone Monday, May 16, 2016 10:58 PM
    • Marked as answer by Tricky Steve Tuesday, May 17, 2016 1:41 PM
    Monday, May 16, 2016 10:55 PM

All replies

  • Re: finding min value in an array

    You don't say what is wrong with the answer you get,  but...

    I would make two changes:
    Dim Savings_per_year(5) As Variant  to  Dim Savings_per_year(1 to 5) As Double
      (5 by itself, gives you a 6 element array - the array is zero based)

    OptExt = WorksheetFunction.Min(Savings_per_year(i))  to  OptExt = WorksheetFunction.Min(Savings_per_year)
      (no i  - you want the min from the entire array)

    '---
    Jim Cone
    Portland, Oregon USA
    https://www.dropbox.com/sh/ttybwg5e9r31twa/AAAnyBTHPX5XsTDp10ItTcw4a?dl=0

    • Edited by James Cone Wednesday, September 21, 2016 10:29 PM
    Saturday, May 14, 2016 12:37 AM
  • You cannot use i as array index. I'd avoid using an array.

    Your function is named OpExt but the line that sets the return value has OptExt. That is not consistent.

    Function OpExt(Cut_length As Single, Usage As Single, Cost As Single)
        Dim Min As Single ' Minumum Extrusion length
        Dim Max As Single ' Maximum Extrusion length
        Const Grip As Single = 4 ' Required Scrap in inches for either cutting or stamping an extrusion
        Const Cut_width As Single = 0.1875 ' The amount of material removed by the saw
        Dim Rungs_per_ext As Single
        Dim Ext_per_day As Single
        Dim Scrap_per_ext As Single
        Dim Salvage_scrap_per_ext As Single
        Dim Salvage_scrap_per_day As Single
        Dim Salvage_lengths_per_year As Single
        Dim Savings_per_year As Single
        Dim Min_Savings_per_year As Single
        Dim i As Integer
        
        Min = 175 ' Minimum extrusion length in inches
        Max = 176 ' Maximum extrusion length in inches
        Min_Savings_per_year = 1E+100 ' initialize return value to a very large number
        
        For i = Min To Max Step 0.25
            Rungs_per_ext = i / (Cut_width + Cut_length)
            Ext_per_day = Usage / Rungs_per_ext
        
            If (Rungs_per_ext - WorksheetFunction.RoundDown(Rungs_per_ext, 0)) * Cut_length > (Grip + Cut_width) Then
                Scrap_per_ext = (Rungs_per_ext - WorksheetFunction.RoundDown(Rungs_per_ext, 0)) * Cut_length
            Else
                Scrap_per_ext = (Rungs_per_ext - WorksheetFunction.RoundDown(Rungs_per_ext, 0)) * Cut_length + Cut_length
            End If
        
            Salvage_scrap_per_ext = Scrap_per_ext - Grip - Cut_width
            Salvage_scrap_per_day = Salvage_scrap_per_ext * Ext_per_day
            Salvage_lengths_per_year = (Salvage_scrap_per_day / i) * 365
        
            Savings_per_year = Cost * Salvage_lengths_per_year
            If Savings_per_year < Min_Savings_per_year Then
                Min_Savings_per_year = Savings_per_year
            End If
        Next i
        
        OpExt = Min_Savings_per_year ' I want to return the lowest amount of savings
    End Function


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Saturday, May 14, 2016 12:41 AM
  • Sorry, I am clearly a novice with VBA and programming in general. Thank you for your help with this issue! The problem is that when I go into debugging the code it runs up until the line Savings_per_year(i) = (Cost*Salavage_lengths_per_year) then exits before iterating to the next index. The answer that it leaves me is simply #VALUE!  

    The similar code that I based this more complex code on is below. This code iterates through all of the values. The concept of the codes seem very similar to me, but I don't understand the issue with the original code not executing.

    Function xval(x As Single, y As Single, z As Single)


        Dim myarray(10) As Variant
        Dim i As Integer
        Dim value As Single
        
        
        For i = 0 To 9
            
            value = i * z
            
        
            myarray(i) = i * x + (y / (i + 1)) - value
            
        Next
        
        xval = WorksheetFunction.Max(myarray())
            

    End Function

    Monday, May 16, 2016 3:24 PM
  • Thank you for your help! I still am having the same issue that I replied to above in James Cone's response, but that was certainly and issue I needed to fix! 
    Monday, May 16, 2016 3:29 PM
  • Re: code stops

    This runs, but I don't have any idea if the answer is correct.
    Note the name change.
    '---
    Jim Cone


    '---
    Function OpExt_R1(Cut_length As Single, Usage As Single, Cost As Single)

    Dim Min As Single ' Minumum Extrusion length
    Dim Max As Single ' Maximum Extrusion length
    Dim Grip As Single ' Required Scrap in inches for either cutting or stamping an extrusion
    Dim Cut_width As Single ' The amount of material removed by the saw
    Dim Rungs_per_ext As Single
    Dim Ext_per_day As Single
    Dim Scrap_per_ext As Single
    Dim Salvage_scrap_per_ext As Single
    Dim Salvage_scrap_per_day As Single
    Dim Salvage_lengths_per_year As Single
    Dim Savings_per_year(1 To 5) As Double '<<< revised
    Dim i As Double '<<< revised
    Dim j As Long    '<<< new

    Min = 175 ' Minimum extrusion length in inches
    Max = 176 ' Maximum extrusion length in inches
    j = 1     '<<< new

    For i = Min To Max Step 0.25
    Grip = 4 ' The amount of scrap to cut an extrusion
    Cut_width = 0.1875

    Rungs_per_ext = i / (Cut_width + Cut_length)
    Ext_per_day = Usage / Rungs_per_ext

    If (Rungs_per_ext - WorksheetFunction.RoundDown(Rungs_per_ext, 0)) * Cut_length > (Grip + Cut_width) Then
    Scrap_per_ext = (Rungs_per_ext - WorksheetFunction.RoundDown(Rungs_per_ext, 0)) * Cut_length
    Else
    Scrap_per_ext = ((Rungs_per_ext - WorksheetFunction.RoundDown(Rungs_per_ext, 0)) * Cut_length) + Cut_length
    End If

    Salvage_scrap_per_ext = Scrap_per_ext - Grip - Cut_width
    Salvage_scrap_per_day = Salvage_scrap_per_ext * Ext_per_day
    Salvage_lengths_per_year = (Salvage_scrap_per_day / i) * 365

    'i replaced with j
    Savings_per_year(j) = (Cost * Salvage_lengths_per_year)
    j = j + 1 '<<< new
    Next

    OpExt_R1 = WorksheetFunction.Min(Savings_per_year)  '<<< revised
    End Function




    • Edited by James Cone Monday, May 16, 2016 4:22 PM more notes
    • Marked as answer by Tricky Steve Monday, May 16, 2016 7:42 PM
    Monday, May 16, 2016 4:04 PM
  • I don't fully understand what you are trying to do but it looks like something for Excel's Solver.
    Monday, May 16, 2016 5:53 PM
  • Thank you so much! That seems to have done the trick!
    Monday, May 16, 2016 7:43 PM
  • Is it possible to print what index, i, it is that yielded the right answer? Ideally this would take place in the next cell over.
    Monday, May 16, 2016 8:06 PM
  • Re: more info in one cell over

    A function called from a worksheet cell can only return a value to the calling cell.
    This is a security precaution built into Excel.  You can return multiple values to the calling cell...
    '---
    Function OpExt_R2(Cut_length As Single, Usage As Single, Cost As Single) As Variant  'version R2
     Dim Min As Single
     Dim Max As Single
     Dim Grip As Single
     Dim Cut_width As Single
     Dim Rungs_per_ext As Single
     Dim Ext_per_day As Single
     Dim Scrap_per_ext As Single
     Dim Salvage_scrap_per_ext As Single
     Dim Salvage_scrap_per_day As Single
     Dim Salvage_lengths_per_year As Single
     Dim Savings_per_year(1 To 5) As Double
     Dim i As Double
     Dim J As Long
     
     Dim x As Variant    'version R2
     Dim y As Variant    'version R2
     
     Min = 175
     Max = 176
     J = 1

    For i = Min To Max Step 0.25
     Grip = 4
     Cut_width = 0.1875
     Rungs_per_ext = i / (Cut_width + Cut_length)
     Ext_per_day = Usage / Rungs_per_ext
     
     If (Rungs_per_ext - WorksheetFunction.RoundDown(Rungs_per_ext, 0)) * Cut_length > (Grip + Cut_width) Then
       Scrap_per_ext = (Rungs_per_ext - WorksheetFunction.RoundDown(Rungs_per_ext, 0)) * Cut_length
     Else
       Scrap_per_ext = _
       ((Rungs_per_ext - WorksheetFunction.RoundDown(Rungs_per_ext, 0)) * Cut_length) + Cut_length
     End If
     
     Salvage_scrap_per_ext = Scrap_per_ext - Grip - Cut_width
     Salvage_scrap_per_day = Salvage_scrap_per_ext * Ext_per_day
     Salvage_lengths_per_year = (Salvage_scrap_per_day / i) * 365
     
     Savings_per_year(J) = (Cost * Salvage_lengths_per_year)
     J = J + 1
    Next

    x = Application.WorksheetFunction.Min(Savings_per_year)             'version R2
    y = Application.WorksheetFunction.Match(x, Savings_per_year, 0)  'version R2
    OpExt_R2 = x & "_index " & y                                                           'version R2
    End Function
    '---

    Jim Cone
    • Edited by James Cone Monday, May 16, 2016 10:58 PM
    • Marked as answer by Tricky Steve Tuesday, May 17, 2016 1:41 PM
    Monday, May 16, 2016 10:55 PM
  • That works just great! Thank you very much!
    Tuesday, May 17, 2016 1:41 PM