• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

UDF lose their value until CTR-ALT-F9

SirJB7

SirJB7

Excel Rōnin
Hi!

I've been using a book which has two sheets per month (one for buying, one for selling).

It accumulates info from Aug-2009, and it weights 30Mb, but I run it on a i7-920 with 6Gb, so it flyes.

It has several worksheets for summary and briefing, and I have no problem with them, the calculation is immediate.

But I recently added a new worksheet that retrieves information (just a dozen of columns) from each -just selling- sheets. In order to don't write many different formulaes in that sheet (among other issues as converting to constant values and so) I decided to write an UDF function, and as the workbook was an .xlsm with thousand of VBA code lines, I supposed that 20 more lines wouldn't affect the performance at all.

Wrong. This new worksheet has 800 rows and less than 20 columns, with 14 of then using this UDF, so... 800*14=1120 formulaes identical (they use first column and the first row as parameters, and four cells of the same row too, none of these with UDFs).

The problem? I can only view the result of the cells containing this UDF until I press CTRL-ALT-F9. The calculation method is set as automatically (including tables). I've tried adding Volatile, and nothing. Even more, if I'm working with this workbook -in any sheet-, and I switch to another workbook -even if they're not relationed with the first one-, when I come back to the original I lose all the values shown, and I have to press CTRL-ALT-F9 again... action that takes 1'40"...

The function code isn't complex, it looks in a table to which month corresponds each bill, with this month builds a string for the sheet's name, and does a vertical look up with the bill number (the first column parameter) and extracts the value corresponding to the header column (the first row parameter).

Thanks for your interest and help.

Regards for all!
 
SirJB7

Can you post the UDF so we can see what it is doing and possibly offer an alternative approach?
 
SirBJ,


One thing I would recommend immediately is to save your file as Excel Binary File (xlsb).


This will reduce the size of the file (to a huge extent) and working on the file will be even easier.


Regarding the UDF, as Hui asked, please post this for review.


~VijaySharma
 
Hi, Hui!

This is the funtion VBA code.


-----

Public Function DatoFactura(psLetra As String, plNúmero As Long, psDato As String, pbAcumular As Boolean) As Variant

' constantes

' datos factura

Const ksColLetra = "Letra"

Const ksColNúmero = "Número"

Const ksColFecha = "Fecha"

Const ksColCódigo = "Código"

Const ksColCliente = "Cliente"

Const ksColNoGravado = "No gravado"

Const ksColGravado = "Gravado"

Const ksColIVA = "IVA"

Const ksColIB = "IB"

Const ksColImporteDól = "Importe U$S"

Const ksColTipoCambio = "T.C."

Const ksColImportePes = "Importe $"

Const ksColVencimiento = "Vencimiento"

Const kiColLetra = 6

Const kiColNúmero = 7

Const kiColFecha = 4

Const kiColCódigo = 8

Const kiColCliente = 9

Const kiColNoGravado = 16

Const kiColGravado = 20

Const kiColIVA = 27

Const kiColIB = 36

Const kiColImporteDól = 41

Const kiColTipoCambio = 15

Const kiColImportePes = 40

Const kiColVencimiento = 16

' tablas

Const ksTablaÚltimaFacturaMes = "TablaÚltimaFacturaMes"

Const ksTablaPersonas = "'Papelería a mano.xlsm'!TablaClientesProveedores"

' vecimientos

Const ksContado = "CONTADO"

' meses

Const kiFilaMes = 1

Const kiColumnaMesInicio = 3

Const ksMes = "Balance"

' declaraciones

Dim I As Long, J As Long, K As Long, A As String, F As Date

Dim sHoja As String, sTabla As String

Dim V As Variant, W As Currency

' inicio

On Error GoTo ÚltimaFactura_Exit

sTabla = ksTablaÚltimaFacturaMes + psLetra

I = Application.WorksheetFunction.HLookup(plNúmero - 100000000 - 1, Range(sTabla), 1, True)

I = Application.WorksheetFunction.Match(I, Range(sTabla), 0)

F = CDate(Range(sTabla).Cells(1, 1).Offset(-Range(sTabla).Row + 1, I).Value)

A = CStr(F)

' sHoja = Format$(Year(F), "yy") & Format$(Month(F), "mm") & "V"

sHoja = Mid$(A, 9, 2) + Mid$(A, 4, 2) + "V"

' proceso

V = ""

W = 0

With ActiveWorkbook.Worksheets(sHoja)

J = 3

Do While Len(.Cells(J, kiColLetra).Value)

If .Cells(J, kiColLetra).Value = psLetra And _

.Cells(J, kiColNúmero).Value = plNúmero Then

' dato

Select Case psDato

Case ksColLetra

I = kiColLetra

Case ksColNúmero

I = kiColNúmero

Case ksColFecha

I = kiColFecha

Case ksColCliente

I = kiColCliente

Case ksColNoGravado

I = kiColNoGravado

Case ksColGravado

I = kiColGravado

Case ksColIVA

I = kiColIVA

Case ksColIB

I = kiColIB

Case ksColImporteDól

I = kiColImporteDól

Case ksColTipoCambio

I = kiColTipoCambio

Case ksColImportePes

I = kiColImportePes

Case ksColVencimiento

I = -1

Case Else

I = 0

End Select

'

Select Case I

Case Is < 0

W = .Cells(J, kiColFecha).Value

A = Application.WorksheetFunction.VLookup( _

.Cells(J, kiColCódigo).Value, Range(ksTablaPersonas), kiColVencimiento, False)

If A <> ksContado Then

W = W + Val(A)

End If

Case Is = 0

V = ""

Case Is > 0

If pbAcumular Then

W = W + .Cells(J, I).Value

Else

V = .Cells(J, I).Value

End If

End Select

'

If Not (pbAcumular) Then Exit Do

End If

J = J + 1

Loop

End With

' fin

ÚltimaFactura_Exit:

If V = "" Then V = CVar(W)

DatoFactura = V

End Function

-----


I was thinking in uploading a modified version (just entreprises and amounts) to help any helper. It would be a lot of work, but if it's necessary I'll do it, maybe in three or four days.

Thanks again.

Regards!
 
Hi ,


I copied your VBA code into a blank worksheet , and inserted the function in two cells in the worksheet.


When I changed the relevant cells , affecting the cells which had the UDF , I did not have to press CTRL ALT F9.


Of course , I had to insert Application.Volatile into the function.


We had a similar post sometime back , where there was a problem , which could not be reproduced. However , the problem was resolved by copying the whole workbook to a new one , I think.


Narayan
 
Hi, Naryank991!

I'm going to prepare the file to upload in order to being able to reproduce the real case. I'll think I can do it in a couple of days.

Thanks for your advise.

Regards!
 
Hi ,


That will be nice.


The following links have a lot of information on UDFs which you may find useful :


1. http://www.decisionmodels.com/calcsecretsj.htm


2. http://www.decisionmodels.com/calcsecretsh.htm


Narayan
 
Hi SirJB7,

It seems possible that your code is generating an error & terminating early.

Try setting a breakpoint in your error handler to check for that, and if so, use other debugging methods to pinpoint where the error is occurring.

re:Application.Volatile, it's required anytime your function is dependant on any other inputs, cells or data besides that which was passed to it in its parameters. Since Excel doesn't know what your function depends on, it will be recalculated at every change in your workbook or if there is a calculation for any reason. This is probably undesirable, especially in a large set of data like this. One way to make your function non- volatile looks to be specifying the tables as parameters (you could use defined names for them if desired, similar to constants).


I don't see specifically what is causing your problem offhand, though.


Asa
 
The problem with switching workbooks is probably your use of Activeworkbook. In a udf, to refer to the workbook containing the udf, use Application.Caller.Parent.Parent (Application.Caller refers to the cell calling your function and you work your way up the object tree from there).
 
Hi, NARAYANK991 & asa!

Thanks for your advises, I'll check them asap.

In the meanwhile, here's a link to a copy of real files (with data changed) so those who want to get a nearer approach to the problem.

There are 2 files:

"Papelería a mano.xlsm", which I use to print invoices and receipts, and it has a worksheet named "ClientesProveedores" that contains a table referenced in the file with the UDF problem.

"IVA.xlsm" is that file.

It has 2 sheets per month starting in Aug-09, with its names in the format YYMMX, where X is "C" for buying ('compras' in spanish) and "V" for selling ('ventas'). Thet have a similar format that includes the same command buttons with unified VBA code, that supports the usual features needed every day (hiding/showing columns, adding/deleting rows, sorting, i.e.).

Originally intended to print the mensual reports that the accountants need for our IRS (tax office, called AFIP), as time went on -sorry, but IT's are like that- I added many other sheets: those at the end, independent; those at the beginning, as summaries and other dependent stuff.

Everything went OK, no problems with calculations, the file size (now 10Mb, but reached 30Mb before redesign) didn't affect, so I decided to add the 2 sheets with problems.

"Cobros", i.e. payments received for the invoices of every sheet YYMM"V", and "FacturasV", a single sheet with the all the invoices of all YYMM"V".

In order to do this, I added to the sheet "Balance" the rows 114 and 115, where I find for each month the last invoice number used (2 rows, one for each of the two invoice types -irrelevant to the problem-).

The very core of the problem are columns D:N of "Facturas" and columns "D:I" of Cobros, columns that simply display information related to the invoices, for making a visual check in order to prevent errors while entering information in "Cobros". In "Facturas" I don't enter info, just display from each YYMM"V" sheet and summarizes from "Cobros" to get the state of the invoices.

Sorry for the length, but I intended to help those willing to aid.

Thank you very much, in advance.

Regards!

Link: http://www.2shared.com/file/1WBdxvPv/UDF_problem_until_CTRL-ALT-F9.html

Sorry for the labels and plain text in both workbooks, it's in spanish, my native langue.

One more thing, both files are .xlsm because they have a lot of VBA code included, which is not protected because this files are used exclusively by me, firstly to provide info and control the accountants, and lastly as a tool to supervise the business... that doesn't run as good as expected and needed, should I sadly say.

I understand if some people are reluctant to open files with VBA code from unknown origins, but as a developer I include VBA code everywhere it can automatize anything and whenever it outpeforms non-VBA Excel features, like normal worksheet functions.
 
PS: needless to say, but in the paragraph where it says "rows 114 an 115", the data in each column of this row is used to get in which worksheet YYMM"V"-formatted is the related invoice... that's to say with the cells in those rows (and the date in row 1) I build each sheet name to be referred in "Facturas" and "Cobros".

Regards!
 
@asa (with the copyrighted permission from Chandoo for his notation, I hope) :)

Hi!

I've been using this files for over 2 years and the code is safe, that's to say it works, doesn't have known bugs, and I've got no issues pending to resolve. Just the recalculation issues.

Related specifically to the use of Volatile, I tried it expecting that it would demand more calculation time each time I used worksheets "Cobros" (where I input data manually), but at least I would have the full data always displayed. Wrong, the recalculation took place every time I modified ¡any cell in the book!, making it unable.

Besides I didn't know if the eventual use of Volatile would have necessary in that function, considering that the arguments passed are all the needed -obviously, I realize while writting-. Should I read then your comment like passing as params all the 'tables' involved? The huge amount of data doesn't reside on named ranges, but indeed in different worksheets (YYMM"V"). So if understood correctly your point, I can't apply it in this case. Am I wrong? Or I misunderstood something, which it's probable.

Regards!
 
@NARAYANK991 (abusing of the previous and not asked copyright permission)

Hi!

Followed the 2 links, interesting info, thank you. I confirmed I'm not violating the best practices related to UDF params and dependant cells, and on another side let me tell you that I'm not using any forced recalculation, nor recalculation neither full calculation, from inside VBA code.

Regards!
 
@sirjb7 :)

Should I read then your comment like passing as params all the 'tables' involved? The huge amount of data doesn't reside on named ranges, but indeed in different worksheets (YYMM"V"). So if understood correctly your point, I can't apply it in this case. Am I wrong?
Click to expand...

Named ranges can have a workbook-wide context and be valid from any sheet, and they can even refer to other workbooks.


The main thing is to pass as parameters all ranges that your function depends on. It would be acceptable without using named ranges, although I see you keep a nice separation of constants and calculations in your code and named ranges would fit that model nicely.

Asa
 
Hi, NARAYANK991!

Couldn't believed when I read your post... Even worse indeed! I wasn't neither at the office nor home, some I wasn't able to get my hands on my loved-and-hated workbooks.

I use those Excel's files at work (notebook Windows Vista 32 x86 and Office 2007 4Gb RAM, and desktop Windows 7 32 x86 and Office 2007) and home (same notebook and desktop Windows 7 x64 and Office 2010). Daily, I use the files and I always have the problem descripted.

The files that I uploaded yesterday were modified, saved and upld from my home PC, so after reading your post from the street I downloaded again the files at home, created a new folder, open both of them (first 'Papelería a mano.xlsm' and after 'IVA.xlsm'), and I could verify that the data in sheets 'FacturasV' and 'Cobros' was still there, displayed.

And I got a message that I forgot I couldn't resolve since I moved to Excel 2010: I opened first the book that is referred from the second-opened, and I had never got before the "This book contains one or more link that couldn't be updated. To change the data source...", options "Continue" or "Modify". Did you get it? If so, how can I remove it, taking in consideration that in 2007 it worked fine, and that there are no other external links involved. No need to say that since the very first day I got this message, I always choose to modify the data source, press the button Update Values, nothing changed in the legend of the external links windows, then press the button Check State, just to change the legend from something as "Unknown" to "The origin or data source is already open".

After that I added an operation in "1111V" sheet, line 29 -the first free-, ... the movement with the cursor key from one cell to the adjacent required a calculation of 20 seconds (on my i7-920 6Gb 2000Mhz & GPU 1Gb DDR5 machine!!!). Even the normal cells that had no VBA code behind or VLookUp in dependant cells.

After that I registerd a new payment in 'Cobros', line 663. Checked 'Cobros', ... the navigation from cell to cell still demanded 20 seconds with the calculation legend at bottom.

But, at last but not least, I still could mantain the data displayed in cells with the UDF (I'm thinking in renaming it to WTF -i. e. 'wow... this function'-). I activated and checked 'FacturasV' and OK too.

Then I opened a simple sheet workbook, with nothing but a list of to-do stuff, wrote two numbers in to cells, added a formula to another multiplying them, and shifted again to 'IVA.xlsm' workbook... and... guess what?... no data displayed in the cels with the nearly now-yet-damnded UDF function. Solution? Said give a try to simple F9, but didn't work: so CTRL-ALT-F9.

Would you be as kind as to try to reproduce this new case?

Very pleased and grateful with your dedication.

Thank you very much.

Regards!
 
@asa :D

Hi!

Gonna review and try the use of ActiveWorkbook in UDF, I think tomorrow if I can reschedule a couple of things.

On the other hand I deeply appreciate your very kind comments about my code. It's just the result of paying high costs when doing things right but not doing right things. As this workbooks were developed for personal use, I didn't strictly follow my own version of the best practice's rules (class modules, encapsulating, private and public stuff and so on). The main problem was that I was greedy and increased my addiction to these books, making them become almost a basic management system, I know that and I realize the collaterals -always damages- involved.

May I abuse of your time and ask for examples of your idea of the named ranges that you would implement in this model? I'm always willing to analize different points of view (if you want, attack tactics) that would result in performance improvements or new adquired skills.

Very grateful.

Regards!
 
Hi SirJB7!

Happy holidays and new year!


Regarding defined names, looking in Name Manager I see you have already defined named for several tables. The Names "TablaClientesProveedores" and "TablaClientesProveedoresNombre", for example, refer to all or part of the data area on the TablaClientesProveedores sheet. At first look, it appears you could use the name TablaClientesProveedoresNombre instead of the constant ksTablaPersonas already. It would just be a convenient way of passing each of the ranges required to your UDF, enabling you to simultaneously avoid having your UDF be a volatile (read:slow) function and maintaining the maintainability/transparency that your constants currently provide by centralizing the definitions of the ranges.


I am extremely occupied with pressing matters this season, so hopefully others can help you along further. If I have time and energy, and you have remaining concerns, I will also try to help, but that may not be soon.


Take care!

Asa
 
Hi, asa!

Happy both for you too.

I appreciate a lot your effort and time dedication. Just relax, take it easy. I think I got your point, and I'll begin to works towards that idea. Keep you updated.

Regards!
 
You must log in or register to reply here.
Back
Top