The RefEdit control is similar to a text box, but it allows you to enter a reference to a range by selecting it directly on the worksheet.
The Value property returns this reference.
TIP
To add the RefEdit control to the Toolbox panel, right-click on the panel and select Additional Controls. In the Additional Controls window that appears, select RefEditCtrl from the list of available controls and click OK.
Determining statistical parameters of a range
As an example of using the RefEdit control, let us construct a simple project that determines some statistical parameters of a range—namely, the maximum, minimum, and the sum of all cell values in that range.
So, create a form with a button and a RefEdit control.
In the form module, type the necessary code.
The project is ready.
To calculate the maximum, minimum, and sum of all values in the range, we use the Max, Min, and Sum properties of the WorksheetFunction object, which correspond to the worksheet functions of the same name.
Solving a system of linear equations
As another example of using the RefEdit control, let us create a project for solving systems of linear equations AX = B.
Here:
- A is an n × n square matrix (the coefficients),
- B is the column of constants,
- X is the column of unknowns.
To solve the system of equations, we use the worksheet functions MInverse() (МОБР in Russian Excel) and MMult() (МУМНОЖ in Russian Excel), which return the inverse of a given matrix and the result of multiplying two matrices, respectively.
Additionally, the MDeterminant() (МОПР in Russian Excel) function is used to check whether the system has a solution by calculating the determinant of the coefficient matrix.
The form window contains a RefEdit control and a button.
The user enters into the RefEdit control a reference to a range of size n × (n+1), where the first n columns contain the coefficient matrix and the last column contains the constants.
Clicking the button triggers the calculation of the solution, which is then output into the range immediately to the right of the selected one.
For example the following system of linear equations is solved:
- The coefficient matrix is in range B3:C4.
- The column of constants is in range D3:D4.
- The solution of the system is displayed in range E3:E4.
