Menu
I defined a function in VBA as follows
Public Function Distance(X, Y) as Double
Then in a cell I try to use the function. I type "@Dis" and get the drop down menu, select the function and then enter the parameters ending up with @Distance(A1, A2) in the cell.
When I press Enter I get the Error "That function is not valid".
I enabled macros throughout the system, tried saving it as the old format and as the macro enabled workbook format to no avail.
What's wrong with my usage of this function?
305
Insert VBA code to Excel Workbook Open your workbook in Excel. Press Alt + F11 to open Visual Basic Editor (VBE). Right-click on your workbook name in the "Project-VBAProject" pane (at the top left corner of the editor window) and select Insert -> Module from the context menu. Copy the VBA code (from a web-page etc.)
When you have created a Function procedure using VBA, you can use it in three ways: As a formula in the worksheet, where it can take arguments as inputs and returns a value or an array of values. As a part of your VBA subroutine code or another Function code. In Conditional Formatting.
Sometimes writing, testing and debugging the script will take longer than using worksheet features. VBA does not adjust in the way that formulae do when you move data from one worksheet to another, insert a column, delete rows, etc. Example: you have a sheet called February. Now you want to rename it Feb.
Try using:
=Distance(A1, A2)
Instead of
@Distance(A1, A2)
I've never seen @ as the correct character to call a function in Excel.
I tried the following in Excel, and it works like a charm:
In Module1:
Public Function Distance(X as Double, Y as Double) as Double
Distance = 10
End Function
In a cell:
=Distance(A1, A2)
Which produces the result:
10
as expected.
176
You'll also need to make sure that the VBA code for your function is in a Module and not in the code area of the Worksheet.
45
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
