Forum Discussion
Solved
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...
- 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-c1c999be2b34
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-c1c999be2b34
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
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.
- Glad it helped and good to know that Alt-F11 isn't as ubiquitous as I thought.
