SOLVED

How to inspect an obfuscated (VBA?) cell formula

New Contributor

Hi folks,

 

I'm a programmer, so I'm not intimidated if this discussion leads to VBA, even though I have never programmed in VBA.

 

I was just handed an .xlsm spreadsheet.  It has a cell whose formula reads "=@Velocity(Pressure,Density)".

 

I have never seen the @ symbol in an Excel formula before, but presumably this is some sort of marker for the user function "Velocity"?

 

I have never seen names in the place of cell references, but presumably "Pressure" and "Density" are aliases for specific cells on the spreadsheet?

 

I would like to see the actual math was used to perform this computation.  This is currently hidden.  Thanks for your help in figuring this out!

3 Replies
best response confirmed by John_Ladasky (New Contributor)
Solution
Velocity is most likely a UDF (macro function) but could be a LAMBDA function, and as you suspected, Pressure and Density are "Named" ranges. Go to Formulas -> Name Manager to see the defined names and you almost certainly see Pressure and Density. If (and this is unlikely) Velocity is a LAMBDA function you will see it listed there also. But more likely you will need to go into the VBA editor (Alt-F11) . In the VBA editor you hopefully have the explorer window open on the left but if not hit Ctrl-R. There will be the name of your workbook and worksheets but most likely you have a subfolder called Modules. open the module(s) and you should find the code there.
Finally that @ symbol is used in a couple ways in excel. in a table reference it means 'this row' but in this context I believe it was added to legacy functions when dynamic arrays got introduced if it was unclear if an array or single value was returned and I believe it forces a single value to return instead of an array. Basically in the old excel that function would return a single value but in the new dynamic array it thinks it might try to return an array so it adds the @ symbol meaning return a single value to help keep compatibility.
actually just found a good source:
https://support.microsoft.com/en-us/office/implicit-intersection-operator-ce3be07b-0101-4450-a24e-c1...

@mtarler Thanks, that was good guidance.

For some reason, the Alt-F11 hotkey shortcut is not working on my system.  However, I followed Microsoft's guidelines here to add the Developer menu to my ribbon.  I can click Developer > Visual Basic and reach the VBA editor, even though the keyboard command does not work.  My velocity formula is there.

Glad it helped and good to know that Alt-F11 isn't as ubiquitous as I thought.