Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto-refreshing results of module-calls
Hey all, I have for you today a tricky question which perhaps is harder to
pose than answer. First of all, parden me if I am miss-using any of excel terms; my excel is in Norwegian, and hence translating is not always correct. I have a workbook with 4 different sheets. In one of theese sheets the user is supposed to plot in parameters - approx. 100 different types - and several charts. After plotting in these parameters, the charts are meant to be updating themselves while one alters and adapts the parameters in order to get the desired charts. Unfortunately, when one alters the parameters that are the arguements for a function I've defined my self. Like this: * You alter the value of Cell B5 * Cell G5 is =Stoppe(A5,B5,1), where "Stoppe is a function I've written in VBA The Value of G5 is contrary to my desire not updated! This is how I've defined my function: Under Modules of the VBAProject of my workbook, in Moduel 1, I've written Option Explicit Function Stoppe(Pos As Double, Tog As Integer) As Double Dim Ja As Boolean Dim i As Integer For i = 0 To 7 If Pos = Ark4.Cells(2 * i + 6, (Tog - 1) * 3 + 2) Then Stoppe = Ark4.Cells(2 * i + 7, (Tog - 1) * 3 + 2) Ja = True End If Next i If Ja Then Else Stoppe = 0 End If End Function .. Does anyone know why the cell calling on this function does not update automatically when it's parameters/arguments are altered? Thanks to anyone with a piece of advice! Sebastian |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto-refreshing results of module-calls
Try:
Set your calc mode to automatic Tools / Options / Calculation tab, check "Automatic" Use Application.Volatile as the first line after your declarations. Wrap your function call inside a volatile Excel Function, like =IF(TODAY()=INT(NOW()),stoppe(A1,B1),"") Of course, you need to translate the first and third into Norwegian.... HTH, Bernie MS Excel MVP "Sebastian Stormbo" wrote in message ... Hey all, I have for you today a tricky question which perhaps is harder to pose than answer. First of all, parden me if I am miss-using any of excel terms; my excel is in Norwegian, and hence translating is not always correct. I have a workbook with 4 different sheets. In one of theese sheets the user is supposed to plot in parameters - approx. 100 different types - and several charts. After plotting in these parameters, the charts are meant to be updating themselves while one alters and adapts the parameters in order to get the desired charts. Unfortunately, when one alters the parameters that are the arguements for a function I've defined my self. Like this: * You alter the value of Cell B5 * Cell G5 is =Stoppe(A5,B5,1), where "Stoppe is a function I've written in VBA The Value of G5 is contrary to my desire not updated! This is how I've defined my function: Under Modules of the VBAProject of my workbook, in Moduel 1, I've written Option Explicit Function Stoppe(Pos As Double, Tog As Integer) As Double Dim Ja As Boolean Dim i As Integer For i = 0 To 7 If Pos = Ark4.Cells(2 * i + 6, (Tog - 1) * 3 + 2) Then Stoppe = Ark4.Cells(2 * i + 7, (Tog - 1) * 3 + 2) Ja = True End If Next i If Ja Then Else Stoppe = 0 End If End Function . Does anyone know why the cell calling on this function does not update automatically when it's parameters/arguments are altered? Thanks to anyone with a piece of advice! Sebastian |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto-refreshing results of module-calls
thanks, Bernie. I've tried all your steps, but It didnt fix the problem --
they're still not auto-updating. Additionally, it seems to extend the computing process. Sebastian "Bernie Deitrick" wrote: Try: Set your calc mode to automatic Tools / Options / Calculation tab, check "Automatic" Use Application.Volatile as the first line after your declarations. Wrap your function call inside a volatile Excel Function, like =IF(TODAY()=INT(NOW()),stoppe(A1,B1),"") Of course, you need to translate the first and third into Norwegian.... HTH, Bernie MS Excel MVP "Sebastian Stormbo" wrote in message ... Hey all, I have for you today a tricky question which perhaps is harder to pose than answer. First of all, parden me if I am miss-using any of excel terms; my excel is in Norwegian, and hence translating is not always correct. I have a workbook with 4 different sheets. In one of theese sheets the user is supposed to plot in parameters - approx. 100 different types - and several charts. After plotting in these parameters, the charts are meant to be updating themselves while one alters and adapts the parameters in order to get the desired charts. Unfortunately, when one alters the parameters that are the arguements for a function I've defined my self. Like this: * You alter the value of Cell B5 * Cell G5 is =Stoppe(A5,B5,1), where "Stoppe is a function I've written in VBA The Value of G5 is contrary to my desire not updated! This is how I've defined my function: Under Modules of the VBAProject of my workbook, in Moduel 1, I've written Option Explicit Function Stoppe(Pos As Double, Tog As Integer) As Double Dim Ja As Boolean Dim i As Integer For i = 0 To 7 If Pos = Ark4.Cells(2 * i + 6, (Tog - 1) * 3 + 2) Then Stoppe = Ark4.Cells(2 * i + 7, (Tog - 1) * 3 + 2) Ja = True End If Next i If Ja Then Else Stoppe = 0 End If End Function . Does anyone know why the cell calling on this function does not update automatically when it's parameters/arguments are altered? Thanks to anyone with a piece of advice! Sebastian |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Auto-refreshing VBA modules/functions/macroes | Excel Programming | |||
Auto refreshing data via VBA | Excel Programming | |||
Tool for auto loading/refreshing/updating/etc... | Excel Programming | |||
Calls from sheet module to ThisWorkbook module | Excel Programming | |||
Auto Refreshing - values | Excel Programming |