Forum Discussion
John_Ladasky
Jul 08, 2022Copper Contributor
How to inspect an obfuscated (VBA?) cell formula
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 fo...
- Jul 09, 2022Velocity 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-c1c999be2b34
John_Ladasky
Jul 11, 2022Copper Contributor
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 https://support.microsoft.com/en-us/office/find-help-on-using-the-visual-basic-editor-61404b99-84af-4aa3-b1ca-465bc4f45432 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.
mtarler
Jul 11, 2022Silver Contributor
Glad it helped and good to know that Alt-F11 isn't as ubiquitous as I thought.