I have a function that i need to be volatile, but when i run a short
macro that usually takes about 30 seconds with this function as
volatile it takes a long time to run ( i have never bothered even
letting it complete). I wanted to do boolean logic and an if then but
this doesnt seem to work any ideas on how to have a function not
recalculate when a macro is running but recalculate when it is not?

Please be more specific; What is the code of your macro and of your function? Under what conditions is your macro executed? etc.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

wrote in message ...
|I have a function that i need to be volatile, but when i run a short
| macro that usually takes about 30 seconds with this function as
| volatile it takes a long time to run ( i have never bothered even
| letting it complete). I wanted to do boolean logic and an if then but
| this doesnt seem to work any ideas on how to have a function not
| recalculate when a macro is running but recalculate when it is not?

Macro is only executed when a button is pressed.

Code:

Private Function state(sheet, row, column)

Application.Volatile

state = Sheets(sheet).Cells(row,
WorksheetFunction.VLookup(WorksheetFunction.VLooku p(Sheets("dummy
state").Range("c3"), Sheets("io").Range("alphastate"), 2),
Sheets("io").Range("columns"), column, 0))


End Function

This runs when button is pressed:

Sub hideshowstates()

Application.ScreenUpdating = False

For j = 1 To 2

For i = 6 To 30
If Sheets("Basic Data").Cells(i, j) = "True" Then ' show
in projections sheets
For z = 2 To 28
sht = Sheets("io").Cells(2, z)
If j = 1 Then
col = Sheets("io").Cells(i - 2, z)
Sheets(2).Rows(i + 11).Hidden = False ' show
on percentages sheet
Else
col = Sheets("io").Cells(i + 23, z)
Sheets(2).Rows(i + 36).Hidden = False ' show
on percentages sheet
End If
Sheets(sht).Columns(col).Hidden = False
Next z


Else
For z = 2 To 28 ' hide on projections sheets
sht = Sheets("io").Cells(2, z)
If j = 1 Then
col = Sheets("io").Cells(i - 2, z)
Sheets(2).Rows(i + 11).Hidden = True ' hide on
percentages sheet
For r = 2 To 10
Sheets(2).Cells(i + 11, r).ClearContents
Next r
Else

col = Sheets("io").Cells(i + 23, z)
Sheets(2).Rows(i + 36).Hidden = True ' hide on
percentages sheet
For r = 2 To 10
Sheets(2).Cells(i + 36, r).ClearContents
Next r

End If
Sheets(sht).Columns(col).Hidden = True
Next z
End If

Next i
Next j

End Sub

Try this:

Start your macro wit
Application.Calculation = xlManual
and end it with
Application.Calculation = xlAutomatic

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

wrote in message ...
| Macro is only executed when a button is pressed.
|
| Code:
|
| Private Function state(sheet, row, column)
|
| Application.Volatile
|
| state = Sheets(sheet).Cells(row,
| WorksheetFunction.VLookup(WorksheetFunction.VLooku p(Sheets("dummy
| state").Range("c3"), Sheets("io").Range("alphastate"), 2),
| Sheets("io").Range("columns"), column, 0))
|
|
| End Function
|
| This runs when button is pressed:
|
| Sub hideshowstates()
|
| Application.ScreenUpdating = False
|
| For j = 1 To 2
|
| For i = 6 To 30
| If Sheets("Basic Data").Cells(i, j) = "True" Then ' show
| in projections sheets
| For z = 2 To 28
| sht = Sheets("io").Cells(2, z)
| If j = 1 Then
| col = Sheets("io").Cells(i - 2, z)
| Sheets(2).Rows(i + 11).Hidden = False ' show
| on percentages sheet
| Else
| col = Sheets("io").Cells(i + 23, z)
| Sheets(2).Rows(i + 36).Hidden = False ' show
| on percentages sheet
| End If
| Sheets(sht).Columns(col).Hidden = False
| Next z
|
|
| Else
| For z = 2 To 28 ' hide on projections sheets
| sht = Sheets("io").Cells(2, z)
| If j = 1 Then
| col = Sheets("io").Cells(i - 2, z)
| Sheets(2).Rows(i + 11).Hidden = True ' hide on
| percentages sheet
| For r = 2 To 10
| Sheets(2).Cells(i + 11, r).ClearContents
| Next r
| Else
|
| col = Sheets("io").Cells(i + 23, z)
| Sheets(2).Rows(i + 36).Hidden = True ' hide on
| percentages sheet
| For r = 2 To 10
| Sheets(2).Cells(i + 36, r).ClearContents
| Next r
|
| End If
| Sheets(sht).Columns(col).Hidden = True
| Next z
| End If
|
| Next i
| Next j
|
| End Sub
|