NameError: name ‘REF’ is not defined

Python in Excel Anaconda Code
1

am trying this in the excel add-in

Add imports

import math
import numpy as np
import pandas as pd

Define classes and functions

def to_df(data) → pd.DataFrame:
“”"
Converts a REF (i.e. list of lists) into a pandas DataFrame
Assumes that the first row contains column headers

Usage: df = to_df(REF("F15:H22"))
"""
return pd.DataFrame(data[1:], columns=data[0])

def to_array(data) → np.ndarray:
“”"
Converts a REF (i.e. list of lists) into a 2-dimensional numpy array

Usage: arr = to_array(REF("B3:C44"))
"""
return np.array(data)

test_df = to_df(REF(“A1:L30”))
print(test)

get this in logs… I am missing something obvious or misusing ?
Traceback (most recent call last):
File “Imports and Definitions”, line 24, in
test_df = to_df(REF(“A1:L30”))

2

NameError: name ‘REF’ is not defined

3

REF cannot be used within the Imports and Definitions.

REF is a special piece of machinery that allows Anaconda Code cells to refer to values in other Excel cells.

Normally, PyScript is not aware that it is being run within Excel. It cannot arbitrarily access the .xlsx file nor the running Excel instance. Instead, Anaconda Code looks for REF() calls, then injects the appropriate data into a dictionary within the PyScript runtime. After that, it executes the actual Python code which grabs the value from the dictionary and returns a value.

Imports and Definitions is meant to pre-load libraries and functions into the PyScript runtime exactly once. Afterwards, it does not re-run as individual Python cells are run. As a result, if the values of a REF defined within Imports and Definitions changed, that change would not get picked up by the runtime. That is why REF is disabled within Imports and Definitions.

1 Like
4

Typical usage is:

  1. Go to the Anaconda Code dashboard
  2. Click + New to create a new Code cell
  3. In the editor, click Link Range and choose the cells to reference (e.g. A1:L30).

The code in the editor will automatically write the text REF("A1:L30"). Write the same code you were trying to test and it should work.

1 Like
5

Thanks Jim, my error was very dumb, but I have been catching up on Owen’s video series and going back to basics on python in excel… walk before trying to run…

1 Like
6

It’s definitely a learning curve! I’m glad you’re starting on the journey.

1 Like