Using VBA instead of cell formula

litestream

Active Member
Joined
Jul 24, 2006
Messages
323
I have some very long formulas on a worksheet which encroach on the top of the sheet when they are selected. Is it feasible to enter the formula in the VBA module and if so, how would I do this?

For example, in cell H10, I have the formula:

=IF(AND(E10=1,C21="Yes"),C17,IF(AND(E10=1,C22="Yes"),(C17+C12),""))

which looks a little messy when the cell is selected.
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Barry Katcher

Well-known Member
Joined
Feb 25, 2002
Messages
4,053
What's causing the mess is the long formula wrapping in the formula bar when the cell is selected. You could go to Tools>Options>View and uncheck "Formula bar". That would keep the the top of the sheet clean but you would, of course, lose the use of the Formula Bar and Name Box.
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
Another thought might be to put your formulas in another location on your worksheet (or even a different sheet) somewhere out of range of the user, (say in Z100) and then change the formula in H10 to simply =Z100.
Then you could retain use of the formula bar & namebox.

If you put them into a module they'd need to be somewhat edited to be compatable with vba and then they'd need to be called using vba to get inserted into a cell. (And if they were inserted as formulas (as opposed to static values) they'd still look the same as they do now when the cell gets selected.)
 

Barry Katcher

Well-known Member
Joined
Feb 25, 2002
Messages
4,053
Or, you could place these two little macros in a module and assign them to two buttons. Fast on-and-off of your formula bar.

Code:
Sub HideFormulaBar()
    Application.DisplayFormulaBar = False
End Sub

Sub ShowFormulaBar()
    Application.DisplayFormulaBar = True
End Sub
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454

ADVERTISEMENT

Or... you could use just one little macro and one button. . .
Code:
Sub ToggleFormulaBar()
Application.DisplayFormulaBar = Not Application.DisplayFormulaBar
End Sub
:LOL:


[EDIT:]
Barry, your dog's freakin' me out man.
 

Barry Katcher

Well-known Member
Joined
Feb 25, 2002
Messages
4,053
Nice one, HalfAce, I just added your code to my Personal macro book. Sweet.

There must be other things I can apply the same Application...Not Application to - where do I find a list of Applications?

And my host seems to be down, so you don't have to put up with SmileDog for awhile. Ooops! Hosty just came back up again. So sorry. :devilish:
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454

ADVERTISEMENT

Hi Barry,
Pretty much anything that is a boolean type (either true or false) can be toggled back & forth like that. The default condition is generally expected to be true.
Example, if you were working with, say a checkbox, you could just write:
If CheckBox1 Then. . . (same as If CheckBox1 = True Then. . .)

As for the 'members' available to any of the objects, you can see them by going into the vbe and from the menu choose Tools > Options > Editor tab and in the Code Settings section make sure 'Auto List Members' is ticked. Then, while in a module type Application. (and once you type the period) a list of members should drop down that are available for that.
Then type in Range. and you should see what's available for the range object, etc.


(And I can get used to the dog's smile, it's just the winking at me that's creepin' me out.) :LOL:
 

Barry Katcher

Well-known Member
Joined
Feb 25, 2002
Messages
4,053
Thanks, HalfAce. I knew I had seen that list somewhere's in VBA; now it comes back to me. I just did a "Toggle Full Screen" button and it works great. Gotta experiment even further. My end-users are going to get SO sick of all the new buttons that will soon be appearing on their worksheets......

By the way, we've apparently lost the OP somewhere's along the way.

I'll see if I can work up a "Toggle Wink" for people who are wary of strange dogs. :wink:
 

Forum statistics

Threads
1,137,337
Messages
5,680,904
Members
419,939
Latest member
AJWildOne

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top