ExcelBanter - volatile

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - volatile (https://www.excelbanter.com/excel-programming/409080-volatile.html)

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