noob question on UDFs

[jrg24]

Hi all.

I have found a UDF online that does what I need it to do, but I am not sure how to make this function available on all workbooks I open. I know the workbooks will have to be maco enabled, but even upon opening a macro enabled workbook my UDF will not work. Will I have to copy and paste the data into the workbook containing the UDF everytime I want to use the function (or add the code for the UDF to each workbook) or is there a way to make it work in all macro enabled workbooks?

 

[wigi]

Store the UDF in the personal macro workbook, then it's available all the time working in Excel.

 

[jrg24]

Store the UDF in the personal macro workbook, then it's available all the time working in Excel.

I just tried this and it is not working. I am using excel 2010. I read an article somewhere that made it seem that only macros will work from the personal workbook. I tried saving it as an addin, and it only gave an approximately close answer with information in decimal places beyond the hundreths place. All data in the workbook i tested does not have any information beyon the hundreths place, and I have manually checked the data and know the correct sum.. I have verified that the VBA code is in the personal workbook.

My UDF is designed to find the sum of all bold formatted numbers (formatted as accounting) in a given column of data. The code follows:

Public Function SumBold(rngSumRange As Range) As Single
Dim rngCell As Range
For Each rngCell In rngSumRange
If IsNumeric(rngCell.Value) Then
If rngCell.Font.Bold = True Then
SumBold = SumBold + rngCell.Value
End If
End If
Next rngCell
End Function

Would creating a real addin be a better option? I know a little C# programming, but I would have to learn some more to make this happen. Does anyone have any tips on learning the creation of addins? This is just the first of many projects that I will be working on to help automate my work. Thanks for your time.

 

[dk]

Hi

I think your problem is because the UDF you posted uses the Single data type - change it to Double and it should be ok. I cannot explain the exact technical reason in this instance but it's basically due to the fact that computers cannot represent decimal numbers precisely using binary which results in "incorrect" results in some instances - here is a brief explanation: http://msdn.microsoft.com/en-us/library/system.single(VS.85).aspx

If you stick with doubles then you're using the same data type as Excel uses internally so you will be less likely to run into problems.

HTH
DK