topic Re: vba to populate an array in QlikView

vba to populate an array

vvvvvvizard — Thu, 30 Apr 2015 12:26:13 GMT

am trying to populate an array , i can do it manually like this

sitearray=Array(varsplit(1),varsplit(2) ,varsplit(3),varsplit(4) ...........)

and it works

How do i populate the array using a loop statement

i tried

for z=1 to 10

sitearray=Array(varsplit(z))

Next z

and i get an expected end of statement error

Please assist me with the correct syntax

Re: vba to populate an array

marcus_sommer — Thu, 30 Apr 2015 12:51:56 GMT

Try something like this:

for z=1 to 10

sitearray(z) = varsplit(z)

Next z

Understanding VBScript: Arrays | Scripting content from Windows IT Pro

- Marcus

Re: vba to populate an array

vvvvvvizard — Thu, 30 Apr 2015 12:57:15 GMT

even if i try a

dim sitearray

sitearray(0)=Array('aaaa') or a sitearray(1)=Array('aaaa') i get a type mismatch error

vba sucks

Re: vba to populate an array

marcus_sommer — Thu, 30 Apr 2015 13:14:59 GMT

You need to declare sitearray as ARRAY and sometimes to dimension it:

dim sitearray(), z 'another ...

redim sitearray(9) 'started with 0

- Marcus

Re: vba to populate an array

vvvvvvizard — Thu, 30 Apr 2015 13:17:12 GMT

Hi Marcus , i marked your response as helpful as the url gave me insight into static vs dynamic arrays , i can now populate arrays but not with a loop , for now il copy and paste 324 time and fill in the numbers to populate the array .

Im certain theres an easier way to do it with a loop , i think my for statement might be the issue now .

Dim sitearray(324)

'Dim z

sitearray(0)=varsplit(1)

sitearray(1)=varsplit(2)

sitearray(2)=varsplit(3)

........................

until sitearray(323)=varsplit(323)

Re: vba to populate an array

marcus_sommer — Thu, 30 Apr 2015 13:34:03 GMT

How will be varsplit filled - is this an array, too? Maybe you posted a little more from your code.

- Marcus

Re: vba to populate an array

vvvvvvizard — Thu, 30 Apr 2015 13:45:58 GMT

Option Explicit

Dim vara

vara= ActiveDocument.Variables("vAlias").getcontent.string

'Split the variable int its parts

Dim varsplit

varsplit=Split(vara,",")

'Dim sitearray(3)

'Dim z

'sitearray(0)=varsplit(1)

'sitearray(1)=varsplit(2)

'sitearray(2)=varsplit(3)

'sitearray(3)=varsplit(4)

'Msgbox(sitearray(0))

Sub GenerateBarChart()

i can populate the array this way but not with a for loop statement as stated

Re: vba to populate an array

vvvvvvizard — Thu, 30 Apr 2015 13:50:27 GMT

first time am trying a loop statement in vba

even if i try

For i=1 to 10

msgbox("aaaaaa")

Next i

i get an expected end of statement error on the next i line

Re: vba to populate an array

vvvvvvizard — Thu, 30 Apr 2015 13:59:23 GMT

I figured it out , its

For z=1 to 10

statements

its just next , not next Z which gives an error , seems that vba used in those excel sites is different from the vba used in Qlikview ,not sure why .

Re: vba to populate an array

marcus_sommer — Thu, 30 Apr 2015 13:59:45 GMT

Try:

dim sitearray(), varsplit, vara, arrCount, i

vara= ActiveDocument.Variables("vAlias").getcontent.string

varsplit=Split(vara,",")

arrCount = ubound(varsplit)

redim sitearray(arrCount)

for i = 0 to arrCount - 1

sitearray(i) = varsplit(i)

next ' without i

But why sitearray if varsplit contained the same?

- Marcus

Re: vba to populate an array

robert_mika — Thu, 30 Apr 2015 14:30:16 GMT

Is not VBA is VBS