Using VBA with ControlWORQ

Last updated: 27 May 2025

ControlWORQ Excel Functions¶

ControlWORQ provides several Excel functions that can be used to return ControlWORQ information to Excel. To access these functions, you can use the Excel function wizard. As long as the ControlWORQ add-ins are loaded you will see a group of ControlWORQ Excel functions. Help information is provided for each function.

Functions may display this value when opening a new workbook:

Refresh needed...

Click the Refresh button on the toolbar to recalculate/refresh these functions from the ControlWORQ server.

ℹ️ Note about Cube Arguments in Functions: You may use either the Planning Analytics Cube Name alone or the Server Prefix:Planning Analytics Cube Name notation in ControlWORQ functions.

Example Scenario¶

In the examples below, a travel accrual has been made against the GL cube by the Admin. The journal entry has been saved and approved, but not posted. The journal name is 201401-LC-ACT – travel accrual. It was made on July 23, 2014.

Below is a list of the ControlWORQ Excel functions that will return certain results based on the above example:

CWGetEntryExists¶

This function returns a 1 if the journal entry exists in ControlWORQ; a 0 if it does not.

Syntax¶

CWGetEntryExists(Cube,Entry #)

Arguments¶

Argument	Description
Cube	The name of the cube that contains the entry.
Entry #	The entry number.

Example¶

CWGetEntryExists("GL", "201401-LC-ACT – travel accrual")

In this example, GL is the cube against which the journal entry 201401-LC-ACT – travel accrual has been saved. This example returns a 1 because this journal entry exists in the GL cube.

CWGetEntryInfo¶

This function returns a status of the journal entry, the user who last modified the entry and when it was last modified. If the entry is not found it returns "Entry not found."

Syntax¶

CWGetEntryInfo(Cube,Entry #,Date Format)

Arguments¶

Argument	Description
Cube	The name of the cube that contains the entry.
Entry #	The entry number.
Date Format	A date format that adheres to one of the following conventions: https://msdn.microsoft.com/en-us/library/az4se3k1(v=vs.100).aspx The default is the general format (e.g., 1/14/2015 8:30 AM)

Example¶

CWGetEntryInfo("GL", "201401-LC-ACT – travel accrual", "yyyy-MM-dd hh:mm:ss")

In this example, GL is the cube against which the journal entry 201401-LC-ACT – travel accrual has been saved. This example returns "APPROVED by Admin on 2014-07-23 05:21:08".

CWGetEntryIsApproved¶

This function returns a 1 if the journal entry has been approved in ControlWORQ; a 0 if it has not been approved.

Syntax¶

CWGetEntryIsApproved(Cube,Entry #)

Arguments¶

Argument	Description
Cube	The name of the cube that contains the entry.
Entry #	The entry number.

Example¶

CWGetEntryIsApproved("GL", "201401-LC-ACT – travel accrual")

In this example, GL is the cube against which the journal entry 201401-LC-ACT – travel accrual has been saved. This example returns a 1 because the entry has been approved.

CWGetEntryIsPosted¶

This function returns a 1 if the journal entry has been posted in ControlWORQ; a 0 if it has not been posted.

Syntax¶

CWGetEntryIsPosted(Cube,Entry #)

Arguments¶

Argument	Description
Cube	The name of the cube that contains the entry.
Entry #	The entry number.

Example¶

CWGetEntryIsPosted("GL", "201401-LC-ACT – travel accrual")

In this example, GL is the cube against which the journal entry 201401-LC-ACT – travel accrual has been saved. This example returns a 0 because the journal entry has not been posted.

CWGetEntryIsSaved¶

This function returns a 1 if the journal entry has been saved in ControlWORQ; a 0 if it has not been saved.

Syntax¶

CWGetEntryIsSaved(Cube,Entry #)

Arguments¶

Argument	Description
Cube	The name of the cube that contains the entry.
Entry #	The entry number.

Example¶

CWGetEntryIsSaved("GL", "201401-LC-ACT – travel accrual")

In this example, GL is the cube against which the journal entry 201401-LC-ACT – travel accrual has been saved. This example returns a 1 because the journal entry has been saved.

CWGetEntryLastAuthor¶

This function returns the Planning Analytics client (user) name who last changed the entry.

Syntax¶

CWGetEntryLastAuthor(Cube,Entry #)

Arguments¶

Argument	Description
Cube	The name of the cube that contains the entry.
Entry #	The entry number.

Example¶

CWGetEntryLastAuthor("GL", "201401-LC-ACT – travel accrual")

In this example, GL is the cube against which the journal entry 201401-LC-ACT – travel accrual has been saved. This example returns "Admin".

CWGetEntryLastUpdated¶

This function returns the date/time of the last change to the entry.

Syntax¶

CWGetEntryLastAuthor("GL", "201401-LC-ACT – travel accrual")

Arguments¶

Argument	Description
Cube	The name of the cube that contains the entry.
Entry #	The entry number.
Date Format	A date format that adheres to one of the following conventions: https://msdn.microsoft.com/en-us/library/az4se3k1(v=vs.100).aspx The default is the general format (e.g., 1/14/2015 8:30 AM)

Example¶

CWGetEntryLastUpdated("GL", "201401-LC-ACT – travel accrual", "yyyy-MM-dd hh:mm:ss")

In this example, GL is the cube against which the journal entry 201401-LC-ACT – travel accrual has been saved. This example returns "2014-07-23 05:21:08".

CWGetEntryStatus¶

This function returns the text status of the entry. Possible values are SAVED, APPROVED, and POSTED.

Syntax¶

CWGetEntryStatus(Cube,Entry #)

Arguments¶

Argument	Description
Cube	The name of the cube that contains the entry.
Entry #	The entry number.

Example¶

CWGetEntryStatus("GL", "201401-LC-ACT – travel accrual")

In this example, GL is the cube against which the journal entry 201401-LC-ACT – travel accrual has been saved. This example will return APPROVED because that is the current status of the entry.

CWGetEntryStatusID¶

This function returns the status ID of the entry. Possible values are 0, 1, 2, or 3.

Syntax¶

CWGetEntryStatusID(Cube,Entry #)

Arguments¶

Argument	Description
Cube	The name of the cube that contains the entry.
Entry #	The entry number.

Example¶

CWGetEntryStatusID("GL", "201401-LC-ACT – travel accrual")

In this example, GL is the cube against which the journal entry 201401-LC-ACT – travel accrual has been saved. This example will return a 2 because this is the ID that corresponds to an APPROVED status.

0 = UNSAVED/NOT FOUND
1 = SAVED
2 = APPROVED
3 = POSTED