0
# User Defined Functions (UDFs)
1

2
Decorators and functions for creating custom Excel functions and subroutines in Python. xlwings UDFs allow you to replace VBA functions with Python implementations that can be called directly from Excel worksheets, supporting both Windows COM server and Office.js implementations.
3

4
## Capabilities
5

6
### Function Decorators
7

8
Core decorators for converting Python functions into Excel User Defined Functions (UDFs) that can be called from Excel worksheets.
9

10
```python { .api }
11
def func(f=None, *, category: str = None, volatile: bool = False, 
12
         call_in_wizard: bool = True, macro_type: str = None):
13
    """
14
    Decorator to create Excel functions from Python functions.
15
    
16
    Args:
17
        f (callable, optional): Function to decorate.
18
        category (str, optional): Excel function category for organization.
19
        volatile (bool): If True, function recalculates on every calculation cycle.
20
        call_in_wizard (bool): If True, function appears in Excel Function Wizard.
21
        macro_type (str, optional): Macro type for advanced scenarios.
22
    
23
    Returns:
24
        callable: Decorated function that can be called from Excel.
25
    
26
    Platform Support:
27
        - Windows: COM server integration
28
        - Web: Office.js via xlwings PRO
29
    """
30

31
def sub(f=None, *, call_in_wizard: bool = True):
32
    """
33
    Decorator to create Excel subroutines from Python functions.
34
    Subroutines are called as macros and don't return values to cells.
35
    
36
    Args:
37
        f (callable, optional): Function to decorate.
38
        call_in_wizard (bool): If True, appears in Excel macro list.
39
    
40
    Returns:
41
        callable: Decorated function callable as Excel macro.
42
    
43
    Platform Support:
44
        - Windows only (COM server)
45
    """
46

47
def arg(convert=None, *, ndim: int = None, transpose: bool = False):
48
    """
49
    Decorator for UDF argument conversion and validation.
50
    
51
    Args:
52
        convert: Converter class or conversion options.
53
        ndim (int, optional): Force specific number of dimensions.
54
        transpose (bool): Transpose input data before processing.
55
    
56
    Returns:
57
        callable: Argument converter decorator.
58
    """
59

60
def ret(convert=None, *, transpose: bool = False, expand: str = None):
61
    """
62
    Decorator for UDF return value conversion.
63
    
64
    Args:
65
        convert: Converter class or conversion options.
66
        transpose (bool): Transpose output data.
67
        expand (str, optional): Dynamic array expansion ('table', 'down', 'right').
68
    
69
    Returns:
70
        callable: Return value converter decorator.
71
    """
72
```
73

74
Usage examples:
75

76
```python
77
import xlwings as xw
78
import numpy as np
79
import pandas as pd
80

81
# Basic UDF function
82
@xw.func
83
def hello(name):
84
    """Simple greeting function callable from Excel."""
85
    return f"Hello {name}!"
86

87
# Function with category and options
88
@xw.func(category="Math", volatile=False)
89
def multiply_by_two(x):
90
    """Multiply input by 2."""
91
    return x * 2
92

93
# Array function with converters
94
@xw.func
95
@xw.arg('x', np.array, ndim=2)
96
@xw.ret(np.array)
97
def matrix_multiply(x, y):
98
    """Matrix multiplication using NumPy."""
99
    return np.dot(x, y)
100

101
# DataFrame function
102
@xw.func
103
@xw.arg('data', pd.DataFrame, index=False, header=True)
104
@xw.ret(pd.DataFrame, expand='table')
105
def process_dataframe(data):
106
    """Process DataFrame and return results."""
107
    result = data.copy()
108
    result['total'] = result.sum(axis=1)
109
    return result
110

111
# Subroutine (macro)
112
@xw.sub
113
def clear_sheet():
114
    """Clear active worksheet content."""
115
    xw.sheets.active.clear_contents()
116

117
# Advanced UDF with multiple converters
118
@xw.func
119
@xw.arg('prices', np.array, ndim=1)
120
@xw.arg('volumes', np.array, ndim=1) 
121
@xw.ret(expand='down')
122
def calculate_vwap(prices, volumes):
123
    """Calculate Volume Weighted Average Price."""
124
    return np.sum(prices * volumes) / np.sum(volumes)
125
```
126

127
### UDF Management Functions
128

129
Functions for managing UDF modules, COM server lifecycle, and dynamic UDF loading.
130

131
```python { .api }
132
def serve(clsid: str = "{506e67c3-55b5-48c3-a035-eed5deea7d6d}"):
133
    """
134
    Start the COM server for UDFs on Windows.
135
    Must be called to enable UDF functionality.
136
    
137
    Args:
138
        clsid (str): COM class ID for the server. Default is xlwings' standard ID.
139
    
140
    Platform: Windows only
141
    
142
    Usage:
143
        if __name__ == '__main__':
144
            xw.serve()
145
    """
146

147
def import_udfs(module_names: str, xl_workbook):
148
    """
149
    Import UDF functions from Python modules.
150
    
151
    Args:
152
        module_names (str): Semicolon-separated module names containing UDF functions.
153
        xl_workbook: Excel workbook object (internal parameter).
154
    
155
    Note: This function is typically called internally by xlwings.
156
    
157
    Examples:
158
        # Import UDFs from multiple modules
159
        xw.import_udfs('my_udfs;other_udfs', workbook)
160
    """
161

162
def get_udf_module(module_name: str, xl_workbook):
163
    """
164
    Get reference to UDF module for introspection.
165
    
166
    Args:
167
        module_name (str): Name of UDF module.
168
        xl_workbook: Excel workbook object (internal parameter).
169
    
170
    Returns:
171
        dict: Module information including the Python module object.
172
    """
173
```
174

175
### UDF Development Patterns
176

177
Common patterns and best practices for developing robust UDFs:
178

179
```python
180
# Pattern 1: Error handling in UDFs
181
@xw.func
182
def safe_divide(a, b):
183
    """Division with error handling."""
184
    try:
185
        if b == 0:
186
            return "#DIV/0!"
187
        return a / b
188
    except Exception as e:
189
        return f"#ERROR: {str(e)}"
190

191
# Pattern 2: Optional parameters
192
@xw.func
193
def format_currency(amount, currency="USD", decimals=2):
194
    """Format number as currency with optional parameters."""
195
    if currency == "USD":
196
        symbol = "$"
197
    elif currency == "EUR":
198
        symbol = "€"
199
    else:
200
        symbol = currency
201
    
202
    return f"{symbol}{amount:.{decimals}f}"
203

204
# Pattern 3: Range processing
205
@xw.func  
206
@xw.arg('data', np.array, ndim=2)
207
@xw.ret(expand='table')
208
def process_range(data, operation="sum"):
209
    """Process 2D range with specified operation."""
210
    if operation == "sum":
211
        return np.sum(data, axis=1).reshape(-1, 1)
212
    elif operation == "mean":
213
        return np.mean(data, axis=1).reshape(-1, 1)
214
    elif operation == "std":
215
        return np.std(data, axis=1).reshape(-1, 1)
216
    else:
217
        return data
218

219
# Pattern 4: Caching expensive operations
220
_cache = {}
221

222
@xw.func(volatile=False)
223
def expensive_calculation(input_value):
224
    """Cache expensive calculations."""
225
    if input_value in _cache:
226
        return _cache[input_value]
227
    
228
    # Simulate expensive operation
229
    import time
230
    time.sleep(0.1)
231
    result = input_value ** 2
232
    
233
    _cache[input_value] = result
234
    return result
235

236
# Pattern 5: Database integration
237
@xw.func
238
def query_database(sql_query, connection_string):
239
    """Execute SQL query and return results."""
240
    import sqlite3
241
    
242
    try:
243
        conn = sqlite3.connect(connection_string)
244
        cursor = conn.cursor()
245
        cursor.execute(sql_query)
246
        results = cursor.fetchall()
247
        conn.close()
248
        return results
249
    except Exception as e:
250
        return f"#ERROR: {str(e)}"
251
```
252

253
### Advanced UDF Features
254

255
Advanced UDF capabilities including async functions, caller context, and dynamic arrays:
256

257
```python
258
# Async UDF (Windows only)
259
@xw.func
260
async def async_web_request(url):
261
    """Asynchronous web request UDF."""
262
    import aiohttp
263
    
264
    async with aiohttp.ClientSession() as session:
265
        async with session.get(url) as response:
266
            return await response.text()
267

268
# Caller context UDF
269
@xw.func
270
def get_caller_info():
271
    """Get information about the calling cell."""
272
    caller = xw.caller()  # Special function to get caller context
273
    return f"Called from {caller.address}"
274

275
# Dynamic array UDF (Excel 365/2021)
276
@xw.func
277
@xw.ret(expand='table')
278
def generate_multiplication_table(size):
279
    """Generate multiplication table as dynamic array."""
280
    import numpy as np
281
    
282
    if not isinstance(size, (int, float)) or size <= 0:
283
        return "#ERROR: Size must be positive number"
284
    
285
    size = int(size)
286
    table = np.outer(np.arange(1, size + 1), np.arange(1, size + 1))
287
    return table
288

289
# Real-time data UDF
290
@xw.func(volatile=True)
291
def get_stock_price(symbol):
292
    """Get real-time stock price (volatile function)."""
293
    import requests
294
    
295
    try:
296
        # Simulated API call
297
        url = f"https://api.example.com/stock/{symbol}"
298
        response = requests.get(url, timeout=5)
299
        data = response.json()
300
        return data.get('price', '#N/A')
301
    except:
302
        return '#N/A'
303
```
304

305
### UDF Deployment and Distribution
306

307
Setting up UDF projects for distribution and deployment:
308

309
```python
310
# udfs.py - Main UDF module
311
import xlwings as xw
312
import numpy as np
313
import pandas as pd
314

315
@xw.func
316
def my_function(x):
317
    return x * 2
318

319
# Server startup
320
if __name__ == '__main__':
321
    # Start COM server for UDFs
322
    xw.serve()
323

324
# setup.py for UDF package distribution
325
from setuptools import setup
326

327
setup(
328
    name='my-excel-udfs',
329
    version='1.0.0',
330
    py_modules=['udfs'],
331
    install_requires=['xlwings', 'numpy', 'pandas'],
332
    entry_points={
333
        'console_scripts': [
334
            'start-udfs=udfs:main',
335
        ],
336
    },
337
)
338
```
339

340
Excel VBA code for UDF integration:
341

342
```vba
343
' In Excel VBA (ThisWorkbook or Module)
344
Sub ImportPythonUDFs()
345
    ' Import UDFs from Python module
346
    Application.Run "xlwings.import_udfs", "udfs"
347
End Sub
348

349
Sub StartUDFServer()
350
    ' Start Python UDF server
351
    Shell "python -c ""import udfs; udfs.serve()"""
352
End Sub
353
```
354

355
## Platform-Specific Considerations
356

357
### Windows COM Server
358

359
```python
360
# Windows-specific UDF features
361
@xw.func
362
def windows_specific_function():
363
    """Function available only on Windows."""
364
    import win32api
365
    return win32api.GetComputerName()
366

367
# COM server configuration
368
if __name__ == '__main__':
369
    # Register COM server
370
    import sys
371
    if len(sys.argv) > 1 and sys.argv[1] == '--register':
372
        import win32com.server.register
373
        win32com.server.register.UseCommandLine()
374
    else:
375
        xw.serve()
376
```
377

378
### Office.js (PRO)
379

380
```python
381
# Office.js UDFs (PRO version)
382
from xlwings.server import func, arg, ret
383

384
@func
385
@arg('data', convert='dataframe')
386
@ret(expand='table')
387
def web_compatible_function(data):
388
    """Function that works in both desktop and web Excel."""
389
    return data.describe()
390
```
391

392
## Types
393

394
```python { .api }
395
# UDF decorator types
396
UDFDecorator = Callable[[Callable], Callable]
397
ArgDecorator = Callable[[Callable], Callable]  
398
RetDecorator = Callable[[Callable], Callable]
399

400
# UDF function signatures
401
UDFFunction = Callable[..., Any]
402
SubroutineFunction = Callable[..., None]
403

404
# Server management
405
ServerFunction = Callable[[], None]
406
ImportFunction = Callable[[str], None]
407
GetModuleFunction = Callable[[str], Any]
408
```