with NotePad
Before you read this page you should read Excel API > using NotePad with JavaScript
Install node.js and npm
more information
visit nodejs.org and download for Windows.
When you download node.js you automatically get npm installed as well.
This installs into the following folder:
C:\program Files\nodejs\
You will need to restart your computer.
Check that node and npm are installed.
node -v // v16.14.0
npm -v // 8.3.1
Generate package.json file
Open File Explorer and create the (C:\temp\notepad) folder.
Create a new subfolder called "excel-custom-functions" (C:\temp\notepad\excel-custom-functions).
Open a command prompt (as administrator).
Change to that folder and initialise npm.
cd c:\temp\notepad\excel-custom-functions
npm init
Press Enter to accept all the defaults.
This will create a "package.json" file in the folder.
(C:\temp\notepad\excel-custom-functions).
Install Webpack
more information
This lets you bundle all your javascript files into a single file and provides a local development server.
npm install --save-dev webpack
npm install --save-dev webpack-cli
npm install --save-dev webpack-dev-server
npm install --save-dev html-webpack-plugin
npm install --save-dev copy-webpack-plugin
This will create a new subfolder called "node_modules".
(C:\temp\notepad\excel-custom-functions\node_modules).
The '--save-dev' switch will add these modules as a devDependency in the package.json file.
Install Babel
more information
This lets you transpile ES 2015 (ES6) Javascript into ES 2009 (ES5) Javascript code.
npm install --save-dev babel-loader
npm install --save-dev @babel/core
View package.json file
Open a Microsoft Edge browser and drag the "package.json" file onto a new tab to view the contents.
Alternatively open NotePad++ and drag the "package.json" file onto it.
{
"name": "excel-custom-functions",
"version": "1.0.0",
"description": "",
"main": "index.js",
"scripts": {
"test": "echo \"Error: no test specified\" && exit 1"
},
"author": "",
"license": "ISC",
"devDependencies": {
"@babel/core": "^7.19.3",
"babel-loader": "^8.2.5",
"copy-webpack-plugin": "^11.0.0",
"html-webpack-plugin": "^5.5.0",
"webpack": "^5.74.0",
"webpack-cli": "^4.10.0",
"webpack-dev-server": "^4.11.1"
},
}
Create index.html
Open File Explorer in the root folder (C:\temp\notepad\excel-custom-functions).
Create a NotePad file with the following contents and save it as 'index.html'.
Add the script reference to the Office.js file
<!DOCTYPE html>
<html>
<head>
<script src="https://appsforoffice.microsoft.com/lib/1/hosted/Office.js"
type="text/javascript"> </script>
</head>
<body>
<h1>Excel Demo</h1>
<div id="app"></div>
</body>
</html>
Create index.js
Open File Explorer in the root folder (C:\temp\notepad\excel-custom-functions).
Create a NotePad file with the following contents and save it as 'index.jsx'.
Include an empty Office.initialize function.
window.onload = function Start() {
console.log('custom function');
var app_1 = document.getElementById("app");
app_1.innerHTML = '<b>custom function</b>';
}
Office.initialize = function (reason) {
}
Create functions.html
Open File Explorer in the root folder (C:\temp\notepad\excel-custom-functions).
Create a NotePad file with the following contents and save it as 'functions.html'.
<!DOCTYPE html>
<html>
<head>
<script src="https://appsforoffice.microsoft.com/lib/1/hosted/custom-functions-runtime.js"
type="text/javascript"> </script>
</head>
<body>
<!-- The body is empty on purpose -->
</body>
</html>
Create functions.js
Open File Explorer in the root folder (C:\temp\notepad\excel-custom-functions).
Create a NotePad file with the following contents and save it as 'functions.js'.
CustomFunctions.associate("ID_ADDFUNCTION", ADD_FUNCTION);
function ADD_FUNCTION(first, second) {
return first + second;
}
Create functions.json
Open File Explorer in the root folder (C:\temp\notepad\excel-custom-functions).
Create a NotePad file with the following contents and save it as 'functions.json'.
{
"$schema": "https://developer.microsoft.com/en-us/json-schemas/office-js/custom-functions.schema.json",
"functions": [
{
"id": "ID_ADDFUNCTION",
"name": "ADDFUNCTION",
"description": "adds up two numbers",
"helpUrl": "https://bettersolutions.com/excel/functions/complete-list.htm",
"parameters": [
{
"name": "first",
"description": "my description",
"type": "number",
"dimensionality": "scalar"
},
{
"name": "second",
"description": "my description",
"type": "number",
"dimensionality": "scalar"
}
],
"result": {
"type": "number",
"dimensionality": "scalar"
},
"options": {
"stream": false,
"cancelable": false
}
}
]
}
Create webpack.config.js file
Open File Explorer in the root folder (C:\temp\notepad\excel-custom-functions).
Create a NotePad file with the following contents and save it as 'webpack.config.js'.
This file tells webpack the location and filename of our top level/entry javascript file ("index.js").
This file tells webpack to bundle the javascript files into two separate files called "index-bundle.js" and "functions-bundle.js".
var HTMLWebpackPlugin = require('html-webpack-plugin');
var CopyWebpackPlugin = require('copy-webpack-plugin');
module.exports = {
devtool: false,
entry: {
index: __dirname + '/index.js',
functions: __dirname + '/functions.js'
},
output: {
filename: '[name]-bundle.js',
path: __dirname + '/dist',
},
module: {
rules: [
{
test: /.js?$/,
exclude: /node_modules/,
use: {
loader: 'babel-loader'
}
}
]
},
plugins: [
new HTMLWebpackPlugin({
filename: 'index.html',
template: __dirname + '/index.html',
chunks: ["index"]
}),
new HTMLWebpackPlugin({
filename: 'functions.html',
template: __dirname + '/functions.html',
chunks: ["functions"]
}),
new CopyWebpackPlugin({
patterns: [
{
from: 'functions.json',
to: 'functions.json'
},
],
})
]
};
Launch Browser
You are now in a position to launch webpack and start the local development server.
Open a command prompt (as administrator).
Change to the corresponding folder.
cd c:\temp\notepad\excel-custom-functions
Execute the following 2 commands.
node ./node_modules/webpack/bin/webpack --mode development
node ./node_modules/webpack/bin/webpack serve --mode development
Open a Microsoft Edge browser and type in the URL - localhost:8080
 |
Create manifest.xml
Open File Explorer in the root folder (C:\temp).
Create a new subfolder called 'shared-catalog' (C:\temp\shared-catalog).
Create a NotePad file with the following contents and save it as 'manifest.xml' in the shared-catalog subfolder.
For more information on individual elements refer to the page under Manifest > Elements.
<?xml version="1.0" encoding="UTF-8"?>
<OfficeApp
xmlns="http://schemas.microsoft.com/office/appforoffice/1.1"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:bt="http://schemas.microsoft.com/office/officeappbasictypes/1.0"
xmlns:ov="http://schemas.microsoft.com/office/taskpaneappversionoverrides"
xsi:type="TaskPaneApp">
<Id> UNIQUE GUID </Id>
<Version>1.0.0.0</Version>
<ProviderName>Better Solutions Limited</ProviderName>
<DefaultLocale>en-US</DefaultLocale>
<DisplayName DefaultValue="ExcelCustomFunction" />
<Description DefaultValue="ExcelCustomFunction"/>
<IconUrl DefaultValue="https://bettersolutions.com/AddinIcon.png"/>
<SupportUrl DefaultValue="https://bettersolutions.com"/>
<AppDomains>
<AppDomain>AppDomain1</AppDomain>
</AppDomains>
<Hosts>
<Host Name="Workbook" />
</Hosts>
<DefaultSettings>
<SourceLocation DefaultValue="http://localhost:8080/index.html" />
</DefaultSettings>
<Permissions>ReadWriteDocument</Permissions>
<VersionOverrides xmlns="http://schemas.microsoft.com/office/taskpaneappversionoverrides"
xsi:type="VersionOverridesV1_1">
<Hosts>
<Host xsi:type="Workbook">
<AllFormFactors>
<ExtensionPoint xsi:type="CustomFunctions">
<Script>
<SourceLocation resid="Functions.Script.Url"/>
</Script>
<Page>
<SourceLocation resid="Functions.Page.Url"/>
</Page>
<Metadata>
<SourceLocation resid="Functions.Metadata.Url"/>
</Metadata>
<Namespace resid="Functions.Namespace"/>
</ExtensionPoint>
</AllFormFactors>
</Host>
</Hosts>
<Resources>
<bt:Urls>
<bt:Url id="Functions.Script.Url" DefaultValue="http://localhost:8080/functions-bundle.js"/>
<bt:Url id="Functions.Page.Url" DefaultValue="http://localhost:8080/functions.html"/>
<bt:Url id="Functions.Metadata.Url" DefaultValue="http://localhost:8080/functions.json"/>
</bt:Urls>
<bt:ShortStrings>
<bt:String id="Functions.Namespace" DefaultValue="CUSTOM"/>
</bt:ShortStrings>
</Resources>
</VersionOverrides>
</OfficeApp>
Enter a valid GUID Id (visit www.guidgen.com)
Save the changes.
You can then side load this add-in using a Shared Folder.
Launch Excel
Display the Developer Tab
Click "Add-ins" and select the 'Shared Folder' tab
You will see an entry "ExcelCustomFunction"
Double click it to load the add-in.
Select cell "B2" and type "=CUSTOM."
 |
GitHub Repo
You can view the project at - github.com/OfficeAddins/custom-functions
© 2023 Better Solutions Limited. All Rights Reserved. © 2023 Better Solutions Limited TopPrevNext