This example shows how Excel and VBA can interact with each other. VBA will be used to generate function values over a given range. The answers will then be stored within an Excel workbook and used to produce a graph.
This could be done entirely with Excel without the use of VBA. But if the formulae was particularly complicated then the Excel expression would be difficult to decipher. Instead, we just pass the function arguments to a VBA function. The function then evaluates the expression and returns the answer to Excel.
In this case our function will be a “saddle”:
X^2 – Y^2
Or
X*X – Y*Y
So we set up our x and y values in Excel:
Our function is called Demonstrate_Function and will have two arguments (x and y). The following is the code for this function:
Function Demonstrate_Function(x_arg As Long, y_arg As Long) As Long 'Dim Demonstrates purpose of function interacting with Excel 'The answer will be stored in "Demonstrate Function" 'the function will just take the difference between the squared values of x and y Demonstrate_Function = (x_arg * x_arg) - (y_arg * y_arg) End Function
Note that this code must be inserted in a module. So to call the function with an x-value of 1 and a y value of 2 we put the following code into a cell:
T3= Demonstrate_Function (1,2)
which gives a value of -3.
And we now put this formulae into all the cells in our range:
However to see the function more clearly we can plot a surface chart:
