Using VBA instead of cell formula

[litestream]

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.

 

[Barry Katcher]

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]

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]

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.

Sub HideFormulaBar()
    Application.DisplayFormulaBar = False
End Sub

Sub ShowFormulaBar()
    Application.DisplayFormulaBar = True
End Sub

 

[HalfAce]

Or... you could use just one little macro and one button. . .

Sub ToggleFormulaBar()
Application.DisplayFormulaBar = Not Application.DisplayFormulaBar
End Sub

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

 

[Barry Katcher]

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.

 

[HalfAce]

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.)

 

[Barry Katcher]

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:

 

[HalfAce]

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

You'd do that just for me?

 

[litestream]

Thats very helpful, thanks all!