where's the formula defined ? 3

[rb1957]

Hi,

I have a s/sheet from a vendor to refers to a formula "Fcc_bruhn_C7_30(...)".

I recognise what the formula is trying to do, but I can't find the expression in the s/sht.

Thoughts ?

another day in paradise, or is paradise one day closer ?

 

[rb1957]

where are macros defined ?

sorry Excel 11 (I think !?)

another day in paradise, or is paradise one day closer ?

 

[rb1957]

found "macros" (on the "view" ribbon ... Y oh Y) ... but my formula isn't there ??

another day in paradise, or is paradise one day closer ?

 

[rb1957]

actually Excel 2016

another day in paradise, or is paradise one day closer ?

 

[SkipVought]

Upload the workbook?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[rb1957]

its cell V34 I'm looking at. If I copy and paste I think it recalculates and says "WTF?" as only Excel can !?

another day in paradise, or is paradise one day closer ?

 

[GregLocock]

You need to look at the Name Manager which is under Formulas, about half way along the ribbon.

Cheers

Greg Locock


New here? Try reading these, they might help FAQ731-376

http://eng-tips.com/market.cfm?

 

[rb1957]

thx greg, tried that, got the usual Excel "WTF?" ... (screen shot attached).

I think this means the formulas/macros are on the vendor's computer and didn't ship with the s/sht.
If I force a re-calc I get "WTF?".

another day in paradise, or is paradise one day closer ?

 

[rb1957]

but humm ... if I change the precedents, the result changes ... so the formula is live ? but doesn't survive "cut and paste" ??

another day in paradise, or is paradise one day closer ?

 

[jotunn]

rb: Developer tab. Visual basic.

 

[SkipVought]

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[rb1957]

thx guys ... it'd help if I had the "developer" tab on my ribbon !? must ask "Mordac" ...

another day in paradise, or is paradise one day closer ?

 

[SkipVought]

Alt+F11 toggles between sheet and VB Editor.

Did you Enable Macros?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[jotunn]

it'd help if I had the "developer" tab on my ribbon

Oh yeah...not on by default. File > Options > Customize Ribbon

 

[jotunn]

Alt+F11 toggles between sheet and VB Editor.

Didn't know that shortcut. Thanks!

 

[rb1957]

damnit ! I was looking for that ribbon thingy ...

another day in paradise, or is paradise one day closer ?

 

[GregLocock]

I should have looked at your screenshot, yes developer->view code

Function Fcc_bruhn_C7_30(Fcy As Double, Ec As Double, bt As Double, edge As Integer) As Double

Dim k_edge As Double
Dim Fcc As Double

If edge = 1 Then
k_edge = 0.566
ElseIf edge = 0 Then
k_edge = 1.425
End If

Fcc = (k_edge * (Fcy ^ 0.6) * (Ec ^ 0.4)) / (bt ^ 0.8)

If Fcc > Fcy Then Fcc = Fcy

Fcc_bruhn_C7_30 = Fcc



Cheers

Greg Locock


New here? Try reading these, they might help FAQ731-376

http://eng-tips.com/market.cfm?

 

[SkipVought]

As you can see there are 4 modules in the VB Editor…
buckling
crippling
Interpolation
section_analysis

Each has one or more procedures (subs & functions).

Fcc_bruhn_C7_30 function is in crippling.
flng_sect_props is in section_analysis.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[IDS]

Some more hints for working with VBA:

Alt-F8 will open a dialog with a list of macros (i.e. routines defined as Sub()), and you can choose Run, or Edit or Step Into to view the code.
Alt-F8 will not display functions, but if you start to type =function_name the VBA functions will be included in the list of available functions, as well as the built in ones. You can then select the one you want, and click on the function wizard (fx, next to the name box), to see the required input.
To view a function code you have to go to the VBA Editor (Alt-F11, or Developer tab, if you must), then search for it. If using the search function (Alt-F), and you want to search all modules, make sure the search Project option is selected.
To step through a function:
- Go to the function code in the VBA Editor, and set a break point near the start (or from wherever you want to step)
- Go back to the worksheet and select a cell calling the function, then press F2-Enter.
- You can then step through the code with F8, etc, as for a subroutine.


Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[Agent666]

To add to what IDS noted, Ctrl+shift+A is also your friend when working with udfs, it will fill out the variables so you can replace with your own references.

UDFs do not autocomplete like the built in excel functions, so it's harder to know what you should be entering.

https://engineervsheep.com