Array addition/subtraction vba

Question

Okay, I feel like I'm missing something major here; however I am trying to subtract arrays on VBA. I need to subtract the arrays and assign them to another variable x so I cant do a normalizing function. Below is my version of the code:

Code:

Dim xnew As Variant, xTable As Variant, x As Variant

xnew = [{1,2}]
xTable = [{4,3}]

x = xTable - xnew

MsgBox (x)

I swear this is a stupid question and I have looked around the internet for this simple operation, but to my demise, nothing has come up... I see this question as different to MINUS operation on Array elements because the person asking here is trying to change the size of the array, I am not.

Check the answers in this thread [MINUS operation on Array elements](https://stackoverflow.com/questions/13931786/minus-operation-on-array-elements). Looks like you will need named ranges or a loop. Does not seem like "array subtraction" is supported in vba. — Jacob H, Jun 14 '18 at 19:59

score 3 · Answer 1 · answered Jun 14 '18 at 20:19

You can also use strings and Application.Evaluate to avoid the loop:

Dim xnew As String, xTable As String, x As Variant

xnew = "{1,2}"
xTable = "{4,3}"

x = Application.Evaluate("INDEX(" & xTable & " - " & xnew & ",)")

For i = LBound(x) To UBound(x)
    Debug.Print "x(" & i & ") = " & x(i)
Next

Drew Chapin · Accepted Answer · 2018-06-14T20:09:29.587

1

I think you're trying to subtract them as a matrix. VBA isn't that smart. You need to loop through the array and do the subtraction yourself.

Dim xnew As Variant, xTable As Variant, x(1 To 2) As Variant, i As Integer

xnew = [{1,2}]
xTable = [{4,3}]

For i = LBound(xTable) To UBound(xTable)
    x(i) = xTable(i) - xnew(i)
Next

For i = LBound(x) To UBound(x)
    Debug.Print "x(" & i & ") = " & x(i)
Next

Output

x(1) = 3
x(2) = 1

Note: If you don't know the size of the xTable then you can do ReDim x(LBound(xTable) To UBound(xTable) to set the correct dimensions on x

edited Jun 14 '18 at 20:09

answered Jun 14 '18 at 20:06

Drew Chapin

7,779
5
58
84

Thanks! How do you know the output. It doesn't print it for me – JakeK Jun 14 '18 at 20:09
@JakeK, open the VBA editor, and make sure you have the Immediate window visible (View → Immediate Window, CTRL+G). This is where `Debug.Print` output will show up. – Drew Chapin Jun 14 '18 at 20:10
Ah! Cool! I didn't know that was a feature! Thanks! – JakeK Jun 14 '18 at 20:11
1

@JakeK, we honestly taught each other something new. I've been doing this for 15 years and I didn't know you could define an array like `[{element,element}]` – Drew Chapin Jun 14 '18 at 20:12
2

@DrewChapin `[]` is short hand for `Application.Evaluate` which can return an array. – Scott Craner Jun 14 '18 at 20:13
Haha neato! That's awesome! I really hope it helps you out in the future! – JakeK Jun 14 '18 at 20:14

Array addition/subtraction vba

Code:

2 Answers2