trying to get a VB module to work

[twiggin]

Hello,

I've been trying to calculate a weighted average and weighted standard deviation in Excel 2007/Windows 7. Since there is no formula for it I looked for one on the web and found a UDF that I copied into a VB module. It worked and I got the result I was looking for. I saved the workbook as a macro-enabled workbook. I may have done some other things in the process that messed things up because...
When I next opened the workbook the results were no longer there, instead there was a #NAME error in each cell.
I checked and the modules were still there, and when I typed "=WtStd" the formula showed up in a drop down list. I tried deleting the modules and copying the code in again as a new module with no luck.
I'm brand new to working with VB and modules, so I imagine there may be something really obvious that I'm not doing. I thought maybe there was a way to have modules apply to some workbooks and not others that I missed, but I couldn't see anything to that effect.
Any help you can give me would be most appreciated.

Tom

 

[Randall00]

Did you possibly save the macro in a module of your personal macro workbook instead of the workbook in which the formula is located...?

 

[shg]

If your workbook has an xlsx extension, it means you saved it in a format that does not allow (and strips) macros, and you missed the warning about losing your macro.

In that case, you'll need to install it again, and then save the workbook as an xlsm.

 

[twiggin]

hi and thanks for responding,

the workbook is saved as .xlsm. Looking in the VBAProject window, the modules are saved in a folder branching from the workbook I'm working in.

I just opened a new workbook, inserted a new module for WtAvg and copied the code from the old module into this one then ran it on three weights and three numbers. It worked. Did the same with WtStd and it worked. Back in the original workbook it still doesn't work, doing the identical thing.

The folder trees showing the modules in relation to the workbook look identical between the two workbooks in VBAProject window. The code is identical since I copied it from one to the other.

In fact, I just copied the whole sheet with all my calculations from one workbook to the other -- I got a warning that there were macros in the sheet, I said go ahead and copy anyway. Once the sheet opened in the new workbook, the standard deviation was calculated.

This could get me around my problem -- I can get my answers now -- but I'm still curious what I am doing wrong in the first workbook.

thanks
Tom

 

[shg]

Did you enable macros in the problematic workbook?

 

[twiggin]

I think the enabling macros is the issue. Thanks!

I just changed my settings to enable all macros and then opened the file again and the calculations were there. I changed back to disable macros with notification in the excel Trust Center. I reopened the program and got the option to enable or disable macros. I enabled and it opened fine again.

I must have disabled them when I first opened it, though I don't recall doing that.

I suspected it was something easy like that!
Thanks for your help!

 

[shg]

You're welcome.

I would URGE you to change your settings to Disable all macros with notification, and you then enable macros only when you trust the source.