usage of "empty" in Array()

Question

aProducts(lRow) = Array("Id Product", "Value A", "Value B")

'To copy value A
aProducts(lRow) = Array(.Cells(aPos(1)).Value2, .Cells(aPos(2)).Value2, Empty)

'To copy value B
aProducts(lRow) = Array(.Cells(aPos(1)).Value2, Empty, .Cells(aPos(3)).Value2)

Hi guys,

I use this piece of code in a loop to copy some values and their ID, but anyway, now I'd like to understand the usage of "Empty" in this code in order to add more value.

I tried:

aProducts(lRow) = Array("Id Product", "Value A", "Value B", "Value C")

'To copy value A
aProducts(lRow) = Array(.Cells(aPos(1)).Value2, .Cells(aPos(2)).Value2, Empty)

'To copy value B
aProducts(lRow) = Array(.Cells(aPos(1)).Value2, Empty, .Cells(aPos(3)).Value2)

'To copy value C
aProducts(lRow) = Array(.Cells(aPos(1)).Value2, Empty, Empty, .Cells(aPos(4)).Value2)

"Execusiion error 9"

Thanks in advance and sorry for my approximate english :/

EDIT 1

you can get the file here : https://drive.google.com/file/d/0B5DpGwPWsIfbWWlJRDAzZldYek0/view?usp=sharing

EDIT 2

Thanks to all of you, I just found that i've forgot to remove

ReDim Preserve aProducts(lRow)

Can you provide a download link for the workbook? If not I'm going to have to see more code to determine the problem. — user6432984
– user6432984, Commented Nov 16, 2016 at 13:49
It's a 1 hour meeting, can't post it before, sorry had no choice — Verd'O
– Verd'O, Commented Nov 16, 2016 at 14:02

Vityata · Accepted Answer · 2016-11-16 13:54:14Z

3

Write the following:

Option Explicit

Sub TestMe()

    Dim arr_var     As Variant

    arr_var = Array(Empty, Empty, 5)
    Stop
End Sub

Then run it. Click with mouse over arr_var, press Shift + F2 and you will see this: "Leer" means "Empty" in German.

answered Nov 16, 2016 at 13:54

Vityata

43.8k10 gold badges64 silver badges110 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Ralph Over a year ago

Essentially, Empty is the initial / default value for any new array. You could also just issue the Stop after Dim arr_var(2) and you'd see that you've got an array of three elements all of which are Empty. So, by using Empty you are "resetting" the array to it's initial emptiness.

Verd'O Over a year ago

Oh right, didn't see the screenshot on mobile version thank you again, I'll be back in an hour :/

Verd'O Over a year ago

Thanks to your help, i found my mistake: i forgot to delete ReDim Preserve aProducts(lRow) that I forgot between my B and C values. Thanks for your tips, and have a great day @Vityata ! ;)

Vityata Over a year ago

Welcome :) Its almost night at my place :)

Verd'O Over a year ago

Also have a great "almost night" :)

|

tjb1 · Accepted Answer · 2016-11-16 13:50:34Z

1

Does this work for you? It looks like you forgot to update the last one when you copied it.

aProducts(lRow) = Array("Id Product", "Value A", "Value B", "Value C")

'To copy value A
aProducts(lRow) = Array(.Cells(aPos(1)).Value2, .Cells(aPos(2)).Value2, Empty, Empty)

'To copy value B
aProducts(lRow) = Array(.Cells(aPos(1)).Value2, Empty, .Cells(aPos(3)).Value2, Empty)

'To copy value C
aProducts(lRow) = Array(.Cells(aPos(1)).Value2, Empty, Empty, .Cells(aPos(4)).Value2)

I also think you wanted to update .Cells(aPos(4)).Value2) to have a 4 instead of a 3.

answered Nov 16, 2016 at 13:50

tjb1

7579 silver badges30 bronze badges

2 Comments

Verd'O Over a year ago

I tried it but forgot to change it and it did not work (first thing it tried xD), i'll edit

Verd'O Over a year ago

I'll post it after my meeting

Collectives™ on Stack Overflow

usage of "empty" in Array()

2 Answers 2

9 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related