VBA

Calling DataNitro from VBA

Calling Scripts

Use this to call a DataNitro script from VBA:

Sub call_DN()
    Application.COMAddIns("DataNitro.DataNitro").Object.RunScript ("test.py")
End Sub

Replace “test.py” with the name of your imported script. Use the name of the script as you see it in the DataNitro toolbar - you don’t need the pathname.

If you have multiple scripts with the same filename, you should still use the name as it appears in the toolbar, e.g. “test.py (1)”.

Calling User-Defined Functions

There are two ways to call DataNitro UDF’s from VBA. If you want to call a UDF inside a cell, the best way is to write the formula directly to the cell.

For example, if f is a DataNitro UDF:

ActiveCell.value = "=f(3)"
    '' computes f(3)
ActiveCell.value = "=f(A1)"
    '' computes f(A1)

If you want to use a DataNitro UDF without calling into Excel, you need to run it, like so:

x = Application.Run("f", 3)
    '' x is now f(3)
x = Application.Run("f", Cells(1, 1).value)
    '' x is now f(A1)

Application.Run takes the function name, as a string, followed by up to 30 arguments.

Calling VBA from DataNitro

VBA(macro_name[, args = None, sheet = ''])

VBA lets you call VBA macros (including both subroutines and functions).

macro_name: the macro’s name as a string, including the module or sheet its defined in. This uses a sheet’s codename.

args: a list of arguments for the macro.

sheet: the sheet the macro executes on. Uses the active sheet by default.

Subroutines

Subroutines can be defined in a sheet or in a module. If a subroutine is in a module, you don’t have to specify the module unless there’s more than one macro with that name.

>>> VBA("test")
    ## runs test on the active sheet
>>> VBA("Module1.test", sheet = "Sheet2")
    ## runs test on Sheet2

If the subroutine’s in a sheet, you need to specify which sheet. The subroutine will always run on the sheet it’s defined in.

>>> VBA("Sheet1.test")
## runs test on Sheet1
>>> VBA("Sheet1.test", sheet = "Sheet2")
## also runs test on Sheet1
>>> VBA("test")
    ## won't work; must specify Sheet1

VBA doesn’t return a value after running a subroutine.

Functions

VBA functions can also be called. If this function is defined in our workbook:

Function TimesThree(x)
  TimesThree = x * 3
End Function

We can call it:

>>> VBA("TimesThree", [2])
6
>>> VBA("TimesThree", [4])
12

Sheets: name and codename

Each Excel sheet has a name and a codename. Only the name is used in the spreadsheet (and in DataNitro). The codename is used in the VBA editor, and you’ll only need it if you’re calling a VBA macro defined in that sheet.

You can get a sheet’s codename from the properties tab in the VBA editor, where it’s listed as (name).

For example, if you have a sheet with the name “James Bond” and the codename “Sheet007”, it’ll be listed in Excel as “James Bond”.

>>> Cell("James Bond", "A1").value = 3
    ## writes to the sheet
    >>> Cell("Sheet007", "A1").value = 3
    ## returns an error; DataNitro doesn't use codenames

Here’s how names and codenames look in VBA:

Sheets("James Bond").Cells(1, 1).Value = "Do you expect"
Double07.Cells(2, 1).Value = "me to talk?"
    '' Both of these work
Sheets("Double07").Cells(1, 1).Value = "No, Mr. Bond, I"
James Bond.Cells(2, 1).Value = "expect you to..."
    '' Both of these won't

If “DrNo” is defined in the sheet:

>>> VBA("Double07.DrNo")
    ## runs the subroutine
>>> VBA("James Bond.DrNo")
    ## fails - VBA macros use codenames

This is the only time you’ll need to use codenames in DataNitro.