topic Re: Macro parse failed in QlikView

Macro parse failed

Fri, 26 Sep 2014 08:17:55 GMT

I have a macro that runs on open document. When i open an original qvw on the server - the macro works fine it calculates everything what i need but if i put the file in production and open it as a user i get the error Macro parse failed functionality was lost. Type mismatch :String(see atached example). Any ideas why it is not working on a user side?

Here is my code:

Sub GetValues

Set TableBox = ActiveDocument.GetSheetObject("CH28")

dim sumA

dim CountA

idA=0

sumA=0

CountA=0

for RowIter = 1 to TableBox.GetRowCount-1

set cell = TableBox.GetCell(RowIter,2)

set cell7=TableBox.GetCell(RowIter,3)

if cell7.text="A" THEN

sumA=sumA+cell.text

CountA=CountA+1

idA="A"

end if

set v = ActiveDocument.Variables("vAsum")

v.SetContent sumA,true

set v = ActiveDocument.Variables("vAcount")

v.SetContent CountA,true

set v = ActiveDocument.Variables("vAid")

v.SetContent idA,true

dim sumB

dim CountB

sumB=0

CountB=0

for RowIter = 1 to TableBox.GetRowCount-1

set cell = TableBox.GetCell(RowIter,2)

set cell7=TableBox.GetCell(RowIter,3)

if cell7.text="B" THEN

sumB=sumB+cell.text

CountB=CountB+1

end if

set v = ActiveDocument.Variables("vBsum")

v.SetContent sumB,true

set v = ActiveDocument.Variables("vBcount")

v.SetContent CountB,true

dim sumC

dim CountC

sumC=0

CountC=0

for RowIter = 1 to TableBox.GetRowCount-1

set cell = TableBox.GetCell(RowIter,2)

set cell7=TableBox.GetCell(RowIter,3)

if cell7.text="C" THEN

sumC=sumC+cell.text

CountC=CountC+1

end if

set v = ActiveDocument.Variables("vCsum")

v.SetContent sumC,true

set v = ActiveDocument.Variables("vCcount")

v.SetContent CountC,true

End Sub

jja

Re: Macro parse failed

Fri, 26 Sep 2014 08:49:22 GMT

It be worth you downloading VBSedit

This is what I am using to sort out my macro errors and you can debug in this too

Re: Macro parse failed

Fri, 26 Sep 2014 09:05:15 GMT

IT might be where you are doing

sumC=0

then

sumC=sumC+cell.text

so you are trying to do maths with text, maybe try converting the text to integer or what ever the type of number you are expecting

Re: Macro parse failed

Fri, 26 Sep 2014 10:30:40 GMT

But it is very strange why it is working when opening in the server and why it is not working for user

Re: Macro parse failed

marcus_sommer — Fri, 26 Sep 2014 10:31:15 GMT

You want calculate the count and sum from a certain field which have a condition on another field? This could be done directly in gui and needed none macro, maybe like:

sum(if(field1 = 'A', field2))

Your parse-error could be caused through not numeric values in your tablebox which could occurred if you have different selctions or different datasets through section access. For this you need an additionally check with isnum().

Apart from that you could comment out parts from your routine and/or put a msgbox with a counter and your values to find the error-point.

- Marcus

Re: Macro parse failed

Fri, 26 Sep 2014 11:16:27 GMT

I can't calculate it via gui because i need to get values from chart(straight table) into text object.

Re: Macro parse failed

marcus_sommer — Fri, 26 Sep 2014 11:25:31 GMT

Most often you could calculate values in a textbox as well as in a table-chart. Maybe you need a little more complex expression with aggr().

http://community.qlik.com/blogs/qlikviewdesignblog/2013/03/07/aggr

- Marcus

Re: Macro parse failed

Fri, 26 Sep 2014 12:03:53 GMT

I think for now i will stick with thew macro just can find out how to convert cell.text to double.

i tried Cdbl(cell.text) but after that if anyway gives error type mismatch Cdbl. What is the method in qlikview macro with data conversions?

Re: Macro parse failed

marcus_sommer — Fri, 26 Sep 2014 12:19:46 GMT

I think you need something like this:

if isnum(value) then v1 + v2 'else nothing

instead of convert with cdbl or cint - then they will fail by strings and maybe null, too.

- Marcus

Re: Macro parse failed

Fri, 26 Sep 2014 12:20:43 GMT

aggr function anyway wont let me to manipulate sheet objects when writing a formula. I will not be able to pick a calculated value from table.

I managed to fix the code. it is simply not cell.text but cell.number

Re: Macro parse failed

Anonymous — Fri, 07 Nov 2014 17:11:27 GMT

Vaishali

When you say downloading VBSedit, what is that?