Excel/General

Enum vOutput

Enumeration type variable listing possible outputs when handling functions that work with worksheet cells.

vAddress
vValue
vWidth
vLength

xls_import_data

xls_import_data (ws_main, s_keyword)
Description:
Method to import data from chosen .xlsx file to main workbook. Data will be imported to sheet that is active while the method is being called. Cell G2 will have a full directory path of the imported file pasted.
Input:
STRING `ws_main` Name of the worksheet, in the Workbook the data is being imported from.
STRING `s_keyword` Keyword that should indicate top right corner of the data that is to be imported.
Output:
None
Return Code:
None
Remarks/Usage:
It is recommended to use format with preceding dot and a number at the end .data1 to avoid confusion with regularly used expressions. Method supports import of multiple tables, as long as they match the keyword .data, .data1, .data2, .dataN. First occurrence of the keyword does not need to have a number. Method import_data is calling functions `xls_look`, `a_make`, `a_paste`, `a_tidy`, and `WorksheetExists` from the same module.
Example:
None

a_base0

a_base0 (input_arr)
Description:
Function takes in array indexed from 1 and updates indexing to start from 0. Supports 1 dimensional arrays only.
Input:
VARIANT `input_arr` Option Base 1 array to be updated to Option Base 0.
Output:
VARIANT `a_base0` Array with indexing starting from 0. No additional output arguments required.
Return Code:
None
Remarks/Usage:
None
Example:
None

a_test

a_test (input_arr)
Description:
Subroutine pastes passed array into worksheet called test. If the workbook exists, it replaces current content of the worksheet. If not, routine creates one.
Input:
VARIANT `input_arr` Array that is pasted into cell B2, in test Worksheet.
Output:
None
Return Code:
None
Remarks/Usage:
None
Example:
None

a_paste

a_paste (input_arr, paste_cell, offset_row, offset_col, wbook)
Description:
Soubroutine that pastes passed array into passed cell.
Input:
VARIANT `input_arr` Array that subroutine will paste into defined cells.
STRING `paste_cell` Destination cell where the data is going to be inserted to. Can be provided in format of absolute address (for example A1), or a keyword (.data).
INTEGER `[offset_row, offset_col]` Values that define whether the data should be inserted in a found cell (default option), or offset. Default value is 0 (direct location).
WORKBOOK `[wbook]` Allows to look for data in different workbooks to the active one. It will look for in active sheet though. Default value is ThisWorkbook.
Output:
None
Return Code:
None
Remarks/Usage:
Subroutine is calling in `xls_look` function.
Example:
None

a_look

a_look (s_keyword, input_arr, num)
Description:
Function looks for `s_keyword` in first row and column of `input_arr` and returns value defined by `num`.
Input:
VARIANT `s_keyword` Keyword that is to be found in row/column header.
VARIANT `input_arr` Array that the `s_keyword` is being looked in.
INTEGER `num` Values [-1, 0, 1, 2, 3... n < Ubound(input_arr)]
Output:
VARIANT `a_paste` Function returns accordingly: -1; column/row index as INTEGER 0; column/row index with preceding name (col/row) to indicate s_keyword location. 1, 2, 3... n - value that is stored in cell 1, 2, 3... cells below/right to the s_keyword cell.
Return Code:
None
Remarks/Usage:
Function is calling in a `a_2Dto1D` function.
Example:
None

a_make

a_make (s_keyword, n_down_start, n_right_start, n_down_end, n_right_end, wbook)
Description
Function sweeps active worksheet for s_keyword value.
Input:
STRING `s_keyword` Keyword that is to be found in row/column header.
LONG `[n_down_start, n_right_start]` Parameters define whether function should start from the s_keyword cell of cell adjacent offset down/right by passed value. Default value equals 0.
LONG `[n_down_end, n_right_end]` Parameters define length/width of created matrix. If omitted, function will take entire table which represents combination of keys `Ctrl`+`Shift`+`Down`
Output:
VARIANT `a_make` 2 dimensional matrix of data that was stored in continuous chain of cells to the right and to the bottom of s_keyword cell, unless offsets were specified.
Return Code:
None
Remarks/Usage:
None
Example:
None

a_tidy

a_tidy ()
Description:

Input:

Output:

Return Code:
None
Remarks/Usage:
None
Example:
None

isVariantAllocated

isVariantAllocated ()
Description:

Input:

Output:

Return Code:
None
Remarks/Usage:
None
Example:
None

xls_WorksheetExists

xls_WorksheetExists (ws_name, wbook)
Description:
Function checks if worksheet with passed ws_name in declared workbook exists. If no wbook is declared, function will look in workbook that is calling the funcion.
Input:
STRING `ws_name` Name of the worksheet to be checked if exists.
WORKBOOK `[wbook]` Workbook where the function should look for ws_name. Default value is `ThisWorkbook`.
Output:
BOOLEAN `xls_WorksheetExists` Boolean statement. Returns `True` is Worksheet named ws_name exists, returns `False` otherwise.
Return Code:
None
Remarks/Usage:
None
Example:
None

xls_fast

xls_fast (status)
Description:
Subroutine to slightly improve Worksheets related macro efficiency. It turns off Screen Updating and calculation. It is recommended to set is back on at the end of the script.
Input:
BOOLEAN `[status]` Variable to set the function on or off. If omitted, default value is True.
Output:
None
Return Code:
None
Remarks/Usage:
None
Example:
None

array_1Dto2D

array_1Dto2D (input_arr)
Description:
Function converts 1 dimensional array and allocates second dimension for each item. Returns 2 dimensional array.
Input:
VARIANT `input_arr` 1 dimensional array that will have second dimension allocated.
Output:
VARIANT `array_1Dto2D` 2 dimensional array. Second dimension is 1 element vector.
Return Code:
None
Remarks/Usage:
None
Example:
None

xls_inputbox

xls_inputbox ()
Description:
Function displays an InputBox asking for user input. User can provide either sinlge values separated with a comma, or provide limits of a range separated with a dash. Inserted data is then converted into an array.
Input:
No input variables required. User input is passed through an InputBox textfield.
Output:
VARIANT `xls_inputbox` 2 dimensional vector of values read from input box.
Return Code:
None
Remarks/Usage:
Input: 1, 2, 3, 4, 5 -> Output: array = (1, 2, 3, 4, 5) Input: 1- 10 -> Output: array = (1, 2, 3, 4... , 10) Negative ranges are supported.
Example:
None

xls_look

xls_look (s_keyword, output, offset_down, offset_right, wbook)
Description:
Function sweeps active worksheet for s_keyword value. Returns data dependent on the input choice.
Input:
STRING `s_keyword` Keyword that the function is looking for in the active worksheet.
vOutput `output` Enumeration variable indicating what function should return. {`vValue`, `vAddress`, `vLength`, `vWidth`}
LONG `[offset_down, offset_right]` Indicates whether function should return values offset from cell containing s_keyword. If omitted, function will return information about cell containing `s_keyword` (exact match).
WORKBOOK `[wbook]` Allows to look for data in different workbooks to the active one. It will look for the value in active sheet though.
Output:
VARIANT `xls_look` Returned value depends on value chosen in output variable.
Return Code:
None
Remarks/Usage:
None
Example:
None

a_nDim

a_nDim (input_arr)
Description:
Function returns number of dimensions of input_arr that was passed on to the function. Returns a Long variable informing about number of dimensions.
Input:
VARIANT `input_arr` Array that number of dimension will be calculated from.
Output:
LONG `a_nDim` Value that represents number of dimensions array has.
Return Code:
None
Remarks/Usage
None
Example:
None

a_2Dto1D

a_2Dto1D (input_arr, a_vector)
Description:
Function extracts single dimensional vector from 2 dimensional matrix.
Input:
VARIANT `input_arr` 2 dimensional matrix that row or column is to be extracted from.
STRING `a_vector` Keyword that indicated which row/column should be extracted from input_arr matrix. Syntax should look as follows: row1, col3.
Output:
VARIANT `a_2Dto1D` Single dimensional vector.
Return Code:
None
Remarks/Usage:
`a_vector` should indicate whether user wants to extract row or column and point at it's index (base1). For example: row1, col3.
Example:
None

a_len

a_len (input_arr)
Description:
Function returns lenght of the array as Long
Input:
VARIANT `input_arr` Array that length will be returned
Output:
STRING `a_len` Length of the array (first dimension only).
Return Code:
None
Remarks/Usage:
Indexing the array (base0, base1) does not affect the result. To calculate length of array's second dimension rin `a_len(arr[1])`.
Example:
None

sRange_to_array

sRange_to_array (input_str)
Description:
User can provide either single numeric values separated with a comma, or provide limits of a range separated with a dash. Inserted data is then converted into an array that function returns.
Input:
STRING `input_str` String that will be converted into an array.
Output:
VARIANT `sRange_to_array` Array of data points that have been separated by comma, or range if dash has been used.
None
Return Code:
None
Remarks/Usage:
None
Example:
None

a_remove_duplicates

a_remove_duplicates (input_arr)
Description:
Function removes duplicate entities from a 1 dimensional array.
Input:
VARIANT `input_arr` Array that the duplicates will be removed from.
Output:
VARIANT `a_remove_duplicates` Array with removed duplicates.
None
Return Code:
None
Remarks/Usage:
Function does not overwrite passed array, but returns a new one. Function supports 1 dimensional arrays only.
Example:
None

a_count_occurrence

a_count_occurrence (input_arr, item)
Description:
Function removes duplicate entities from a 1 dimensional array.
Input:
VARIANT `input_arr` Input array that occurrences will be deleted from.
VARIANT `item` Variable that indicates what items should be removed.
Output:
LONG `a_count_occurrence` Number of `item` occurrence in `inpur_arr`.
None
Return Code:
None
Remarks/Usage:
Function supports 1 dimensional arrays only.
Example:
None

append

append (input_arr, appendee)
Description:
Function adds variable `appendee` at the end of `input_arr`.
Input:
VARIANT `input_arr` Input array that will be updated with next item.
VARIANT `appendee` Variable will be inserted at the end of the `input_arr`.
Output:
VARIANT `append` New array with updated items.
None
Return Code:
None
Remarks/Usage:
`appendee` parameter should have the same number of dimensions as `input_arr`, otherwise function may prompt an error or return unexpected result.
Example:
None

a_contains

a_contains (input_arr, item)
Description:
Function loops through an array to check if `item` is stored in the vector.
Input:
VARIANT `input_arr` Input array that will be looped through.
VARIANT `item` Variable that is being looked for in the `input_arr`.
Output:
BOOLEAN `a_contains` Function returns `TRUE` is the `item` has been found, `FALSE` if not.
None
Return Code:
None
Remarks/Usage:
Function will return `TRUE` and finish iteration after first value that fulfills the condition is met. It will not return any additional information. Function supports only one dimensional arrays. `item` variable is defined as `VARIANT` to simplify using the function, user has to parse the type he wants to evaluate by himself.
Example:
None