Posts by Mike Willerton

All
i am trying to do a two way data table ... ouput=irr, column inputs = gearing, row inputs = acquistion price ... however i need to include a macro to calculate each option ... any ideas ... thx
Mike

Re: use of application.run & filenames

actually it was a school boy level solution at the end ... worked it out my self use the phrase run then the macro name in inverted commas i.e ... #

run "integratedrecalcscen"

in place of #

application.run"template.xls!integratedrecalcscen"

... sorry to have raised an issues thats so simple!! ... in hindsight
thanks to you all anyway

i use a piece of code from the XL Logic site on most of my models as
scenario/sensitivity analysis ... linking into other macros which
perform sub routines using the style : Application.Run "'template.xls'!integratedrecalcscen"

How can i change this code so i do not have keep writing in the file
name for each version and /or when other people use the model they
dont cause problems when they change the filename ... i look forward to your
advice shortly ... many thanks

#Sub Scenario()
Dim Scen_Range As Range, cell As Range, Output_Range As Range,
Sel_Range As Range
Set Scen_Range = Intersect(Range("VBA_ActiveScen").EntireRow,
Range("k:IV")).SpecialCells(xlConstants)
Act_Scen = Range("VBA_ActiveScen").Value
For Each cell In Scen_Range
Range("VBA_ActiveScen").Value = cell.Value
Application.Run "'template.xls'!integratedrecalcscen"
Set Output_Range = Intersect(cell.EntireColumn, Range
("VBA_Results").EntireRow)
Output_Range.Select
Output_Range.Value = Range("VBA_Results").Value
Next cell
Range("VBA_ActiveScen").Value = Act_Scen
Set Scen_Range = Nothing
Set Output_Range = Nothing
Application.Run "'template.xls'!integratedrecalc"
Sheets("scen").Select
End Sub

I use names for formulas /arrays on a regular basis ... however the name box in the tool bar ( as standard ) always seems to small to refer to the title or to use as an index .... is there a way of enlarging the name box either from excel settings or by writing a VBA macro?