0

I basically want to write logic such that if certain element is already present in array i dont want to again put it into it. My array is one dimensional. I am not able to understand how filter function works. Please help. Thanks.

Pradnya
  • 11
  • 3
  • 1
    It would help to add the code you're having a problem with. You can figure out the size of your array using both UBound and lbound. – Tim Williams Mar 06 '14 at 06:16
  • I am declaring array as array1(410) as string. But while assigning values to array i am writing like For i =1 to Count array1(i) = worksheets("Sheet1").Cells(i,6).value End For. Now my actual length of array is count but when i write Ubound(array1) it gives 410 – Pradnya Mar 06 '14 at 06:24
  • as Tim mentioned you can get the size of the array using Ubound and LBound functions. When you define the array array1(410) then it will be an array of size 410. I'm not sure what you are trying to do here. – Math4123 Mar 06 '14 at 06:47
  • `I basically want to write logic such that if certain element is already present in array i dont want to again put it into it.` - look into [Collection/Dictionary](http://stackoverflow.com/questions/915317/does-vba-have-dictionary-structure) object – Dmitry Pavliv Mar 06 '14 at 08:38
  • What I am trying to do is.. I will take count as variant which contains value for number of rows that are present in Sheet1 which have value in column1. Now Suppose for 4 rows value is present then count is 4. So now i am inputting value in to array1 from 1 to Count. Now suppose user appends three more lines into excel then count value will be 7. Now again I have to fill array1 for values but those three new values only i have to add and not all 7 again. Can you suggest how can I achieve this? – Pradnya Mar 06 '14 at 08:58
  • Hint: `Redim Preserve` – Kapol Mar 06 '14 at 10:07
  • @TimWilliams My apologize array1(410) has a size of 411 and an upper bound of 410 – Math4123 Mar 06 '14 at 16:34
  • To answer your title text's first question, "How to find out length of an array in VBA?", here's a robust answer to another almost identical question: https://stackoverflow.com/a/68614881/501113 – chaotic3quilibrium Aug 02 '21 at 14:21
  • Does this answer your question? [Get length of array?](https://stackoverflow.com/questions/30574814/get-length-of-array) – chaotic3quilibrium Aug 02 '21 at 14:23

1 Answers1

0

I'm not sure I exactly get what you are trying to do.

But what I understand makes me recommend you to use Dictionary (it is basically a Hashmap)

It works with keys and values. Each element of this Dictionary contains a unique key (a String that is used to access specifically the data you want) and a value attached to it (can be a String, a Number...)

Here is how to use it :

in your VBA page, go to Tools -> References -> add "Microsoft Scripting Runtime" from the list of available references.

Then in your code :

Dim myDico as Dictionary
set myDico = new Dictionary

myDico.add "myKey", 5

msgbox myDico.Item("myKey") '5
vinkun
  • 83
  • 1
  • 6