Limited) has been that I needed to open a new workbook using any name I want, create the VBA module containing my UDF, save the workbook as an Add-In (i.e.
I don't understand your point about "A function that is called from an Excel worksheet must be in a standard module, not in a sheet, userform or ThisWorkbook module". Anyway, I added the Option Explicit, declaredĭecCOLA, changed the name of the "year" variable, and changed the // to ' as you suggested (I'm really a C# programmer, which explains the //). But as I said, I can't really be sure what changes I made or were made on my system since I first did this. I am mystified by how the add-in could have worked initially given the problems you point out. at least until the primary year and eventually beyond)ĪdjustedAime = WorksheetFunction.Floor(adjustedAime * (1 + decemberCOLA / 100), 0.1) Apply the COLA starting in the eligYear + 1 and continuing to the max of the primary and current years Get the year from the current row in the colas range and the column before the rangeĭecemberCOLA = decCOLA.Offset(-1, 0).Value
Public Function Appl圜OLAs(eligYear As Variant, primaryYear As Variant, currentYear As Variant, aime As Variant, colas As Range) As Variant When I alter one of theįormula references to contain the name twice, I get the compiler syntax error. The add-in function appears in the Insert Function dialog box, but with the name AddInWorkbook.xlam!Appl圜OLAs.Appl圜OLAs (i.e.
VISUAL BASIC EDITOR EXCEL 2016 COMPILE ERROR SYNTAX CODE
On the off chance it's the add-in code itself that's causing the problem (one of the additional problems I mentioned when trying to regenerate the add-in was an inexplicable compiler syntax error that has since gone away), here's the code. I have loaded the add-in workbook (from the Add-Ins dialog box under the Developer tab), meaning the add-in is checked. Note: If no error handling is present in a sub/function routine then VBA climbs the call tree looking for an error handler to exercise. The use of "On Error Resume Next" should be limitied to sections of the code where it is specifically needed. The multiple name issue might be solved (helped) by using a new name for the Add-in. That will flag undeclared variables for you.Ī function that is called from an Excel worksheet must be in a standard module, not in a sheet, userform or ThisWorkbook module.Īlso, the module must not use an "Option Private Module" declaration and the function must be a "Public" function. It is good practice to use "Option Explicit" (no quotes) as the first line in each module. "Year" is the name of a function in Excel VBA better to use another variable name. In VBA the apostrophe ' is used for code comments, not the //. Then delete the original workbook before loading the new add-in. I would start fresh with a new wb name and a new module name and Creating a new workbook/add-in withĪ different name might "help" Excel become less confused. "I also don't understand "The multiple name issue might be solved (helped) by using a new nameĪ: Add-in & workbook are two different names for the same thing. ( commonly referred to as a standard or general module) The module you "insert" is not private and is the one you should place your code. These modules are of the nature "Private" and code placed in them is not accessible from a The workbook has its own module ("ThisWorkbook") and a UserForm has its own module.
In a standard module, not in a sheet, userform or ThisWorkbook module"Ī: There is more than one kind of module in a workbook. Q: "I don't understand your point about "A function that is called from an Excel worksheet must be So, it is not a special creation - its just another workbook, but one that is opened everytime Excel starts (if the workbook is checkmarked in the Add-ins dialog box). The name of the workbook is visible in the VBE (visual basic editor) Project window and the workbook can be modified using the VBE. An Excel add-in is a workbook that is not visible.