Avatar of WeThotUWasAToad
WeThotUWasAToad

asked on 

Auto-calculate cell values returned by Excel 4, ie =GET.CELL() formulas in Excel

Hello,

Is there a way to auto-calculate cell values returned by Excel 4, ie =GET.CELL() formulas in Excel?

I've been experimenting with Excel 4 formulas for a way to display Column widths and/or Row heights in Excel and took the following definitions:
   6   Formula in reference, as text, in either A1 or R1C1 style depending on the workspace setting.

    17  Row height of cell, in points.  

    42  The horizontal distance, measured in points, from the left edge of the active window to the left edge of the cell.
            May be a negative number if the window is scrolled beyond the cell.

    44  The horizontal distance, measured in points, from the left edge of the active window to the right edge of the cell.  
            May be a negative number if the window is scrolled beyond the cell.

    Tip Use GET.CELL(17) to determine the height of a cell and GET.CELL(44) - GET.CELL(42) to determine the width.

...from the list of formulas posted here:
    https://www.mrexcel.com/forum/excel-questions/20611-info-only-get-cell-arguments.html

Row 8 in the following screenshot, shows manual column-width settings for columns B:F and Row 3 shows column widths for the same columns as returned by the formula named LR_Width (see below):

User generated image
The next screenshot includes the Name Manager in order to display definitions of the formulas in use which, for cells F1:F8, are manifest in column G by use of the formula named =zFormula.

User generated image
The formulas work great until the width of one or more of the columns is changed — in which case, the Excel 4 formulas do not automatically update. In addition, found that clicking

    Formulas menu > Calculate Now

is not recognized by the Excel 4 formulas as it would be by a typical Excel formula, e.g. =RANDBETWEEN().

The only way I could find to update the values was to click in the Formula Bar then press {Return} — and this process had to be repeated for each cell containing an Excel 4 formula. I was surprised by this result because I have been using =GET.CELL(6,F1) for many years and it has always auto-updated immediately when making a change to the referenced cell.

Am I missing something or is there a trick that will cause all the Excel 4 formulas to auto-update?

Thanks
Microsoft OfficeMicrosoft Excel

Avatar of undefined
Last Comment
Alan
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

can you provide that excel sample file here as well?
ASKER CERTIFIED SOLUTION
Avatar of Alan
Alan
Flag of New Zealand image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of WeThotUWasAToad
WeThotUWasAToad

ASKER

Thanks for the responses.

Comment by: Ryan Chong
can you provide that excel sample file here as well?
Sorry about that Ryan. I was thinking the whole time while writing my OP that I would be attaching  the file and then forgot to do it. Here it is:

2017-11-23_EE_Excel-4-formulas-.xlsm

Thanks, Steve
Avatar of Alan
Alan
Flag of New Zealand image

Hi Steve,

Does that mean the above does not work for you?

If not, please post back with how it doesn't work, and what you need instead.

Thanks,

Alan.
Avatar of WeThotUWasAToad
WeThotUWasAToad

ASKER

Hey Alan, sorry about that. I did look at your comment when I was on earlier but then only had enough time to reply to one post. :P
Comment by: Alan
To get the Excel4 macro functions to update you have two options (at least):
I don't remember using that shortcut {Ctrl+Alt+F9} in the past so thanks for including it. I actually googled it looking for more info and came across the following on Microsoft's Excel shortcuts page (at https://support.office.com/en-us/article/Excel-keyboard-shortcuts-and-function-keys-for-Windows-1798d9d5-842a-42b8-9c99-9b7213f0040f):

User generated image
— although I'm not sure why they specify "dependent" formulas in the last one since it seems like every formula is dependent on something.

Regarding your VBA solution, I don't think that would be as useful because I am specifically interested in seeing the current width setting of the column — including when it changes.

Therefore, you've given me a sort of semi-automatic solution which is not quite the same as fully-automatic but better than single-shot. :)

Thanks, Steve
Avatar of Alan
Alan
Flag of New Zealand image

Yes, but F9 if insufficient whereas Ctrl-Alt-F9 is sufficient.

There is no need in your situation to go to Ctrl-Alt-Shift F9 - no harm, but it might take longer in some situations - mostly it is just another key to find :-).

Alan.
Microsoft Excel
Microsoft Excel

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

144K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo