Skip to content

MAccessors.bas

Jump to bottom
Sergey Frolov edited this page Aug 29, 2020 · 8 revisions

Module MAccessors.bas

Contains functions and procedures to simplify access to Excel objects like workbooks, sheets, rows, columns, etc.

created 2020-08-20
by Sergey Frolov ([email protected])
version 0.1 (2020-08-20)
Provided under GNU General Public License

References:

MSugar.bas

Contains:

Function getCellValueWithoutOpeningFile()

Get cell value without opening external Excel-file

Parameters:

fileName - Name of external Excel file

sheetName - target Sheet name

rangeAddress - Range name, where the first cell is a point of our interest

folderOfExcelFile - Optional. The name of folder where the target file is located. By default current folder will be applied

Returns:

Value of cell

Function findRow()

Find the first row in the target range with the same values as in the source row for specified columns

Parameters:

rowToFind - source/example Row

columnsIDsInRow - list of the column names of the source row separated by coma which will be used for search

inRange - target Range, where to search

columnsIDsInRange - list of the column names of target range separated by coma which will be used for search Column names can be different for the source and target ranges and will be compared by the order in columnsIDsInRow and columnsIDsInRange parameters.

Returns:

Row object

Sub getArrayFromRow()

Put values from target row to target dynamic array. Values can be taken from the first cell of target range to the last cell, of the first row in the range, or from the first N columns only.

For example:


    getArrayFromRow [C3], myArray, 3

Parameters:

firstCell - Range. Starting cell(s) for taking values

dataArray - Target DYNAMIC array, where the values will be placed

length - Optional. Amount of the values to be taken from the starting cell. By default values will be taken to the last non-empty cell in the target row.

Sub putValuesToRow()

Put any values to row starting from target cell

Parameters:

firstCell - starting cell

dataArray - ParamArray of values to be placed in row

Sub putArrayToRow()

Put array values to row starting from index 1

Parameters:

firstCell - starting cell

dataArray - Array of values to be placed in row

Function lastColumnInTheRow()

Find the column index of the last non-empty cell in the row

Parameters:

forRow - Row number

inSheet - Optional. Can be Sheet object, Sheet name or Sheet index. By default ActiveSheet is used

Returns:

Column index of the last non-empty cell in the row

Function lastColumnNameInTheRow()

Find the column name of the last non-empty cell in the row

Parameters:

forRow - Row number

inSheet - Optional. Can be Sheet object, Sheet name or Sheet index. By default ActiveSheet is used

Returns:

Column name of the last non-empty cell in the row

Function columnNameByIndex()

Get column name by column index

Parameters:

columnIndex - Index of the target column

Returns:

Column name

Function lastRowInColumn()

Find row number for the last non-empty cell in the target column

Parameters:

inColumn - Column name

inSheet - Optional. Can be Sheet object, Sheet name or Sheet index. By default ActiveSheet is used

Returns:

Row number for the last non-empty cell in the target column

Function lastRowInSheet()

Find row number for the last non-empty cell in the entire sheet

Parameters:

inSheet - Optional. Can be Sheet object, Sheet name or Sheet index. By default ActiveSheet is used

Returns:

The last row number for the last non-empty cell out of all columns in the sheet

Sub enableFastCode()

Toggle Excel autoupdate mode - SIGNIFICANTLY speed-ups calculations!

Parameters:

set_fast - True for switch-off Excel autoupdate, False - for switch-on

Sub deleteVisibleRows()

Delete all non-hidden rows in the ActiveSheet Can be used in combination with Filetrs

Sub cloneRow()

Copy source row and paste it before target row

Parameters:

copyRow - Source row (values will be copied from this row)

pasteBeforeRow - Target row (values will be pasted to the row BEFORE this row)

inSheet - Optional, ActiveSheet as default value

toSheet - Optional, inSheet as default value

insertRow - If True will add new row before paste, if False - will replce the row before target row with copied values

Function match()

Wrapper for Application.WorksheetFunction.Match() function

Parameters:

toFind - Value to find

inRange - Range where to find

Returns:

Returns the relative position of an item in the Range that matches a specified value.

Function isOpenWorkbook()

Check if workbook open or not

Parameters:

wbName - Workbook name (without path to file)

Returns:

True if open, or False if not

Sub deleteAllRowsBelowCell()

Delete All non-empty row BELOW the target cell

Parameters:

fromRange - Target cell Range (the row of the first cell in the Range will be used)

Sub findDublicates()

Find rows in selected Range with similar values in selected Columns. Row numbers for rows with similar values in mentioned columns will be copied to Clipboard

Parameters:

inRange - Source range

idInColumns - Column names separated by coma which together can be treated as unique ID for the row

Function getPreviousNonEmptyValue()

Get previous non-empty value in the column above the target cell (including the cell value), will be empty if there is no non-empty values above the target cell

Parameters:

forCell - Target cell to start with

inSheet - Optional. Can be Sheet object, Sheet name or Sheet index. By default ActiveSheet is used

Returns:

First non-empty value from the target cell or above

Wiki Home See Code Download release Post new idea/Report bug

Using Excel helpers

Clone this wiki locally