You are here:

Excel/Refining the Subtract Function in VBA

Advertisement


Question
Hi Damon:

I want to follow-up to a question that you answered back in 2004 about subtracting all values from one another between two arrays of different lengths.  The link is below:

http://en.allexperts.com/q/Excel-1059/Array-substraction.htm

CONTEXT OF MY QUESTION:
I have a similar need for the function (although I eventually am trying to calculate =stdev(subtract(Array1,Array2) in the workbook).  I have a work book that populates once a week.  For a number of reasons, the values in the two arrays may end up being different depending on the number of experiments completed in the previous week.  By the end of the year, both arrays will be fully populated, but in any given week, neither is fully populated.  It would be inefficient for me manually adjust the array coordinates after each update.  I would rather just highlight the entire list of to-be-populated cells and have the formula return the "latest" answer each week when the data comes in.  

Column L and M
Column L and M  
QUESTION:
Using your original vba code, the formula doesn't work that way:

Option Base 1

Function Subtract(A As Range, B As Range) As Variant
 'Subtracts every cell in range B from entire range A and yields a result
 'that is M x N, where M is the number of cells in A and M is the number
 'of cells in B.
 Dim Result()      As Double
 Dim i          As Long
 Dim j          As Integer
 ReDim Result(A.Cells.Count, B.Cells.Count)
 For i = 1 To A.Cells.Count
    For j = 1 To B.Cells.Count
       Result(i, j) = A(i) - B(j)
    Next j
 Next i
 Subtract = Result
End Function

I think this is because the formula is counting the number of cells rather than the number of integers in the array.  I don't know how to fix this.  I need it to ignore the blank cells and only consider the integers for the formula.  In an ideal world, I would like the vba formula to return the same result for the subtract function in both examples in the attached picture.  In other words, how would I adjust the function so that the example on the right ignores the blank cells and gives me the same answer as the example on the left in the attached picture.

Thanks for your help!

ANSWER: Hi Rich,

I believe this version does what you want:

Option Base 1

Function Subtract(A As Range, B As Range) As Variant
 'Subtracts every cell in range B from entire range A and yields a result
 'that is M x N, where M is the number of cells in A and M is the number
 'of cells in B.
 Dim Result()      As Variant
 Dim i          As Long
 Dim j          As Integer
 ReDim Result(A.Cells.Count, B.Cells.Count)
 For i = 1 To A.Cells.Count
    For j = 1 To B.Cells.Count
       If IsEmpty(A(i)) Or IsEmpty(B(j)) Then
         Result(i, j) = ""
       Else
         Result(i, j) = A(i) - B(j)
       End If
    Next j
 Next i
 Subtract = Result
End Function

_____________________________

Note that this does not put #N/A in the cells whose values are not computed, but instead just sets them to empty.  In addition, you can also have values missing in the middle of either array, not just at the ends.  Let me know if I misinterpreted your question or if I missed the mark in any way.

Damon

---------- FOLLOW-UP ----------

QUESTION: Hi Damon:

One follow-up so far when running the formula.  It seems to "break" after a certain point.  For example if the formula range is =stdev(subtract(A:A,B:B)) I get a #Value error.  However, if I run the formula =stdev(subtract(A1:A2000,B1:B2000)) (Ctrl+Shift+Enter) it returns the answer.  Is there some intentional limit to the number of cells the formula can handle?  Can it be set to accomodate an array with 300,000 cells or greater?  I recognize that will take a VERY long time to compute :-).  Thanks for any help you can provide!

Rich

Answer
Hi again Rich,

This version should eliminate the problem with using entire columns (e.g., subtract(A:A,B:B)).

Option Base 1

Function Subtract(A As Range, B As Range) As Variant
  'Subtracts every cell in range B from entire range A and yields a result
  'that is M x N, where M is the number of cells in A and M is the number
  'of cells in B.
  Dim Result()      As Variant
  Dim i          As Long
  Dim j          As Integer
  Dim Anum       As Long
  Dim Bnum       As Long
  Anum = A(A.Columns(1).Cells.Count).End(xlUp).Row - A(1).Row + 1
  Bnum = B(B.Columns(1).Cells.Count).End(xlUp).Row - B(1).Row + 1
  ReDim Result(Anum, Bnum)
  For i = 1 To Anum
     For j = 1 To Bnum
        If IsEmpty(A(i)) Or IsEmpty(B(j)) Then
         Result(i, j) = ""
        Else
         Result(i, j) = A(i) - B(j)
        End If
     Next j
  Next i
  Subtract = Result
End Function

You mention arrays with 300,000 cells or greater.  Because Subtract yields an array with as many rows as in the A range, and as many columns as there are rows in the B range, you should be limited to 1,048,576 elements in the A array and 16,384 elements in the B array.  But if you were to actually use this number of rows in the two arrays, the Subtract range would contain over 1.7 x 10^10 cells.  With this number of cells, just the memory required for this number of numerical values in the Excel file would be 1.3 x 10^11 Bytes, or 130 GBytes.  I'm not sure now big an Excel file can get, but this is probably over that limit. Then in addition to this would be the memory to store the formulas, and I'm not sure how Excel stores array formulas so don't know how to estimate the memory required for these.

And of course the compute time would be corresponding humongous (as you already acknowledged).

Excel claims that its array size limitation is simply available memory.  Somehow I doubt that this is the only limit or that it applies to array formulas as well, but don't have time to research this further.

I hope you find this helpful.

Damon
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Damon Ostrander

Expertise

I have extensive experience with VBA programming in Excel 5 through Excel 2013. As a former aerospace engineer with a large aerospace corporation and consultant in a small defense technology services company, I have developed a wide range of applications in VBA, including simulations involving mixed-language programming, satellite orbit mechanics, graphics and animation, and real-time applications. I am interested in moderate to hard VBA-related questions only.

Experience

I have developed and taught several courses in Excel VBA programming and also VBA programming in Office 97, 2000, and 2007. I have developed a number of large technical applications in Excel VBA for use within the aerospace industry.

Education/Credentials
B.S. in Electrical Engineering and Computer Science, University of California, Berkeley.

©2016 About.com. All rights reserved.