Debugging QlikView VBScript

[#1]

As it can be seen in the other posts concerning this topic,

since QlikView Version 10.0 there is no longer any debugging functionality for VBScript in Qlikview.

And it seems that QlikTech is not willing to integrate debugging tools in the near future

Without a debugger writing VBScript Code can be very hard.

Therefore I tried another approach: Why not use the integrated debugger in Microsoft Excel?

To be able to debug via Excel I took the following steps:

• I installed the QlikView Plugin (which is also needed for the enhanced WebView of QlikView in the IE)
• I created a new empty Excel document and copied all the VBScript Code that I want to debug from QV into an Excel VBA module
• In order to communicate with QlikView I added the following references to the VBA project (they should be simply selectable when the plugin is installed):
  • QlikOCX ActiveX Control module
  • QlikView 11.0 Type Library
• Due to small incompatibilities between QV and Excel VBA some minor code adaption have to be made
  • Global variables may not be initialised outside a Sub or Function in Excel. Therefore I have to change my script to only declarare global variables, and to move all Initialisation to a newly created Sub init(), which I have to call before I start the actual debugging
  • If I want to call a Sub or Function in QV I only need to write "function_abc(var1)". In Excel VBA I need to change that to "CALL function_abc(var1)"
• I created a new Sub Debug() that I use to debug the QV code:
  • Call Init()     'Initialise global variables
  • Set qvApp = New QlikView.Application     'gives you access to QV. If you have more than 1 QV instances opened, the last opened is returned
  • Set qvDoc = qvApp.ActiveDocument     'gives you access to the QV ActiveDocument. In your QV script you have to replace every usage of "ActiveDocument" by the variable qvDoc, which references the active document!
  • Call qv_function_you_want_to_debug
• At last I created a button in Excel and linked its click event to the Debug() procedure

After all this steps the Excel debugger can be used and everything works quite well. The need to adapt the code is a little drawback, but If you try to design your initial QV code according to the Excel incompatibilities, then this is no big deal.

Has anybody else experiences with a debugging solution?  Do you think the solution described is useful or do you have any suggestions or enhancements?

I'm looking forward to any comments 🙂

Kind regards

Chris

[#2]

Chris,

Thanks for the post, this is useful.... 

Have you tried using Visual Studio, C# for this? 

Don

[#3]

Hi Don,

you are welcome 🙂

I don't think you can use .NET and Visual Studio here.

QlikView is only capable of VBA...so you have to work with the VBA Editor/Debugger that comes with Excel or any other Microsoft Office program

Kind regards

Chris

[m_woolf]

QlikTech suggest to me:

Keep the QV vers 9 exe on your desktop. Use it to load your qvw and debug macro script.

[#5]

Hi Miguel,

Thanks for your comments.

I previously chose the Excel option, works nicely. 

Don

Donald Schafer

dj . schafer @comcast.net

978-582-1523

[#6]

Hi Chris,

When I try to use your sample code, it point me that "New QlikView.Application" is undefined, may I know what should I need to do/config after installing the QV 11 IE Plugin, so that I can use this debug functionality in EXCEL VBA?

Thanks,

Benny

[#7]

Hi Benny,

sorry for the delay...

Did you add the QlikView modules as references in your VBA project?
With the IE plugin installed they should be simply selectable in Excel...

You need the following modules:

QlikOCX ActiveX Control module

QlikView 11.0 Type Library

Kind regards

Chris

[sgrice]

Also if you want intellisense to Work in Excel while you are coding then every Variable you want intellisense on you need to define first

i.e.

    Dim qvApp As QlikView.Application

    Dim qvDoc As QlikView.Document

    Dim ContainerObj As QlikView.Container

    Dim ContProp As QlikView.IContainerProperties

This way it props you with all the available properties and methods when coding.

Remember to comment out these DIM's because qlikview does not like these lines.

[#9]

Hi Steven,

as mentioned in one of my earlier replies, I have written an Init() procedure where I do some initialisation first.

In this procedure I also have to figure out if the code is executed via Excel or directly in QlikView, to initialize ActiveDocument correctly.

If you also add you <DIM> commands in the first part of the of the <IF>, then they are only executed, if the code is executed in Excel and you don't have to cope commenting them out, if the code is executed directly in Qlikview

Below you will find my code that I'm refering to...

Hope this makes your life a bit easier...

Kind regards

Chris

'*****If code is executed in Excel Debugger, ActiveDocument is empty

If ActiveDocument = Empty Then

     '*****A new QV App needs to be created and stored as reference in the variable <doc>,

     '*****in order to access ActiveDocument

     Set qvApp = New QlikView.Application

     Set doc = qvApp.ActiveDocument

'*****If the code is executed in QlikView

Else

     '*****ActiveDocument can be accessed directly

     Set doc = ActiveDocument

End If

[deec]

Hi, I'm not understanding why the value for Doc falls out of reference as soon as Init ends...

Sub Init()
    '***Init Global Variables (e.g. newline or tab character)
  vNl = Chr(13) & Chr(10)
    vTab = Chr(9)

    '***If Code will be executed in Excel debugger, ActiveDocument is empty
    If ActiveDocument = Empty Then
    '***First a new QlikView App needs to be created, to be able to access the ActiveDocument and to be able to store a reference in variable doc
          Set qvApp = New QlikView.Application
    Set Doc = qvApp.ActiveDocument
    '***If the code is run directly inside the QlikView client (and not inside Excel)
    Else
          '***ActiveDocument can be accessed directly
    Set Doc = ActiveDocument
    End If

End Sub

'Sub qv_function_you_want_to_debug()
Sub export_VehPen()

    '***Call Init Sub to initialize ActiveDocument and all global variables.
  Init

  MsgBox Doc.Name
  '*** Put your code that you want to debug here 🙂

RUNTIME ERROR 424 object required.

I have the put "Doc" under watch in the Excel editor and noticed that the value for Doc is set to the QV app directory until the Init "End Sub" line. So when it is called by the "MsgBox Doc.Name", it is empty or rather doesn't exist.

[marcus_sommer]

You defined doc inside from a sub-routine and therefore if the routine has finished doc didn't exists anymore. You could either define doc within the second sub-routine, too or you creates a global variable outside from a routine.

- Marcus