You are here:

- Home
- Computing/Technology
- Business Software
- Excel
- Refining the Subtract Function in VBA

Advertisement

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.

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

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

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

Answers by Expert:

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.

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.