The PDQDATA Visual Basic Macro functions

PDQDATA Visual Basic Functions

All of the PDQDATA functions are designed to be used in a VBA (Visual Basic) macro.

In order to reference the PDQDATA visual basic macro functions, from the visual basic editor, click Tools, References, then check the box PDQDATA2. You can enter each function's name without regard to case, as VB will correct the case after you referenece PDQData2.

Many functions in PDQData.com have a parameter for a spreadsheet range. This value can be passed as a R1C1 or A1 style of reference. For example to enter just a single cell in the third row and second column as a reference to a function, the parameter would be "R3C2" or "B3".
A range of cells is just two cell references connected by a colon. For example, to enter the range of cells from the third row to the sixth row in the second column, the parameter would be:"R3C2:R6C2" or "B3:B6".

Most macros accessing PDQData will follow the same steps as an ad hoc request using the spreadsheet menu. That is before you import data you need to

Open a table (PDQ_VB_OPEN or PDQ_VB_ROPEN)
Select the variables (PDQ_VB_GET_VARIABLES
Set a date range (PDQ_VB_GET_DATES or PDQ_VB_MATCH_DATES)

A complete macro is displayed at the end of this document

Function Reference

PDQ_VB_OPEN opens a PDQDATA table by specifying a PDQDATA tablename. If a blank string is passed the the function, the PDQDATA Open menu will be displayed.

PDQ_VB_OPEN(Tablename as String)

    Sub Example_PDQ_vb_open()   ' Opens a Table by specifying the tablename as a string
       tt = "lcz6"              ' define a variable with the table name
       PDQ_VB_OPEN (tt)         ' open the table passing the variable defined
       PDQ_VB_OPEN ("lcz6")     ' same effect, just pass the string directly
    End Sub

PDQ_VB_ROPEN
opens a PDQDATA file by specifying a spreadsheet range that holds a PDQDATA tablename.

PDQ_VB_ROPEN(spreadsheet_range as String)

Example:

    Sub Example_PDQ_vb_ropen()    ' Opens a tabke by passing a reference to a 
                                  ' spreadsheet cell that contains the tablename
       PDQ_VB_ROPEN ("sheet1!b4") ' Cell referenced contains the name of PDQData table 
    End Sub

PDQ_VB_GET_VARIABLE Takes a list of variables from the spreadsheet and selects those variable from the table for later importing. The typical way to build this list is to open a table, select the variables, then import the variables into the spreadsheet from the PDQData2 menus.

PDQ_VB_GET_VARIABLES(spreadsheet_range as string)

Example:

    Sub Example_PDQ_VB_VARIABLE()           ' Sets the variables selected from a table for later imports
       PDQ_VB_get_VARIABLE ("Sheet1!B1:D1") ' Cell reference (or range) that contains the variables
                                        ' to import (variable names, not descriptions)
    End Sub

PDQ_VB_GET_DATES gets a beginning and optionally an ending date range to import the data. If only one cell is specified, PDQDATA assumes this is a beginning date, and will import data from that date to the end of the dataset. If a range is entered, PDQDATA assumes that the upper left corner of the cell contains the beginning date, and the lower right corner of the range contains the ending date. PDQDATA will only import data between the two dates inclusive.

PDQ_VB_GET_DATES(spreadsheet_range as string)

Example:

    Sub Example_PDQ_VB_DATES()           ' Sets the time range by passing a reference to one
                                        ' or more cells in the sheet that contain the beginning dates
       PDQ_VB_get_dates ("Sheet1!B1:b2") ' Cell reference (or range) that contains the dates
                                        ' to import.
    End Sub

PDQ_VB_MATCH_DATES tells PDQDATA the spreadsheet range that contains the dates you wish to match for importing data.

PDQ_VB_MATCH_DATES(spreadsheet_range as string)

    Sub Example_PDQ_VB_MATCH_DATES()       ' Sets the date range by passing a reference to one or more
                                           ' cells in the sheet that contain the dates to import data
       PDQ_VB_MATCH_DATES ("Sheet1!B1:b2") ' Cell range holding the date list
    End Sub

PDQ_VB_DIRECTION sets the import direction for subsequent imports. If Vertical is selected data will be imported with dates running vertically down the spreadsheet and any data descriptions imported across the spreadsheet. If Horizontal is selected, data will be imported with dates going horizontal across the spreadsheet and any descriptions of the data running down the spreadsheet.

PDQ_VB_DIRECTION("{H|V}")

    Sub Example_PDQ_direction()  ' Sets the import direction
       PDQ_VB_DIRECTION("h")       ' Sets the import direction with dates going across the spreadsheet 
       PDQ_VB_DIRECTION("V")       ' Sets the import direction to Vertical
    End Sub

PDQ_VB_IMPORT_DATA imports the data values for the selected variables and date(s)

PDQ_VB_Import_Data(spreadsheet_range as string)

    Sub Example_PDQ_VB_import_data()       ' Imports the numbers for the selected variables
                                           ' and date range into the spreadsheet
       PDQ_VB_import_data ("Sheet1!B11")   ' imports the data
    End Sub

PDQ_VB_IMPORT_DESC Imports the descriptions of the selected variables

PDQ_VB_Import_Desc(spreadsheet_range as string)

    Sub Example_PDQ_VB_import_desc()       ' Imports the variable descriptions (Textual descriptions)
                                           ' of the variables selected into the spreadsheet
       PDQ_VB_import_desc ("Sheet1!B11")   ' imports the descriptions
    End Sub

PDQ_VB_IMPORT_UNITS Import the units of measure of the selected variables

PDQ_VB_Import_Units(spreadsheet_range as string)

    Sub Example_PDQ_VB_Import_Units()       ' Imports the units of measure of the variables selected into the spreadsheet
       PDQ_VB_Import_Units ("Sheet1!B11")   ' imports the units for the selected variables
    End Sub

PDQ_VB_IMPORT_SOURCE Imports the data source for the selected variables

PDQ_VB_Import_Source(spreadsheet_range as string)

    Sub Example_PDQ_VB_Import_Source()       ' Imports the source of the data into the spreadsheet
       PDQ_VB_Import_Source ("Sheet1!B11")   ' imports the source
    End Sub

PDQ_VB_IMPORT_VARIABLE Imports the PDQDATA variable names

PDQ_VB_Import_Variable(spreadsheet_range as string)

    Sub Example_PDQ_VB_import_Variable()       ' Imports the PDQData2 variable names into the spreadsheet
       PDQ_VB_import_Variable ("Sheet1!B11")   ' imports the variables
    End Sub

PDQ_VB_IMPORT_FILENAME Imports the PDQDATA table name

PDQ_VB_Import_Filename(spreadsheet_range as string)

    Sub Example_PDQ_VB_import_Filename()       ' Imports the name of the table into the spreadsheet
       PDQ_VB_import_Filename ("Sheet1!B11")   ' imports the file/Table name
    End Sub

Working macro example

In the following example, we will import December 2006 closing Live Cattle Futures from April 1, 2006 to May 1, 2006. The dates associated with the data will also be imported. After inserting a new module, remember to click Tools, References and check the PDQData2 box. The PDQData2 table name is LCZ6 and is in cell A1. The variables (date and LCZ6SET) are in cells A2 and B2. Finally our date range is in cells A3 and A4. We will be importing the descriptions into cell C1, and the data begins with cell C2. Thus our spreadsheet looks something like:

	a	b	c
1	lcz6
2	date	lcz6set
3	04/01/2006
4	05/01/2006

    Sub Example_PDQ()
       PDQ_VB_import_ROpen ("A1")   ' Opens the table
       PDQ_VB_get_Variable("A2:B2") ' Selects the variables
       PDQ_VB_Get_Dates("A3:A4")    ' Sets the range of dates to import
       PDQ_VB_Import_Desc("C1")     ' Imports the descriptions beginning in cell C1
       PDQ_VB_Import_Data("C2")     ' Imports the data beginning in cell C2
    End Sub

	PDQDATA
	The Ultimate Data Service