Macro parse failed

[#1]

Hi

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

next

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

next

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

next

set v = ActiveDocument.Variables("vCsum")

v.SetContent sumC,true

set v = ActiveDocument.Variables("vCcount")

v.SetContent CountC,true

End Sub

jja

[#2]

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

[#3]

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

[#4]

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

[marcus_sommer]

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

[#6]

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

[marcus_sommer]

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

[#8]

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?

[marcus_sommer]

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

[#10]

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