using a user defined function in two modules

User avatar
stuck
UraniumLounger
Posts: 6042
Joined: 25 Jan 2010, 09:09
Location: up North (but it's not that grim)

using a user defined function in two modules

Post by stuck »

I'm running Word VBA that runs a loop that
- prompts you to browse to an Excel workbook
- which opens in the background
- the Word VBA then reaches into the workbook and copies data into a Word doc
- closes the workbook
- prompts for the next workbook
I'm using a variable called 'Z' as the loop counter.

The title of the File | Open dialog that is presented is tweaked so that the first time round the loop it says "open the 1st workbook", then next time, "open the 2nd workbook", then "Open the 3rd workbook", then "open the [n]th". The ordinal suffixes are generated using a UDF from Chip Pearson's website. The UDF is at the bottom of the module holding all the other code.
[code]
With dlgOpen
.InitialFileName = DefDocPath
.Filters.Clear
.Filters.Add "Excel workbooks", "*.xls*"
.Title = "Select the " & Z & OrdinalSuffix(Z) & " workbook:"
[/code]
This all works tickety boo.

However, if I duplicate this construct in a new module (so can update the surrounding Word VBA to extract data from different workbooks and process it in a subtly different way) the VBE throws a compile error:
    Ambiguous name detected: OrdinalSuffix.

I've tried moving the UDF into its own module and heading it 'Public Function OrdinalSuffix' and there is definitely only one copy of the UDF code in the entire VBA Project but the second use of it is still giving 'Ambiguous name detected: OrdinalSuffix'.

Any clues as to how I can use this UDF in more than one module?

Thanks,

Ken


EDITED: Forget all this, turns out the code for OrdinalSuffix was duplicated in another module. Having removed that, the compile error has gone away.
Last edited by stuck on 12 Feb 2020, 13:42, edited 1 time in total.

User avatar
HansV
Administrator
Posts: 67739
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: using a user defined function in two modules

Post by HansV »

Did you name the module OrdinalSuffix perhaps? That would confuse the VBA interpreter.
Regards,
Hans

User avatar
stuck
UraniumLounger
Posts: 6042
Joined: 25 Jan 2010, 09:09
Location: up North (but it's not that grim)

Re: using a user defined function in two modules

Post by stuck »

No, the new module was called 'modUDFs'.

However, I've updated my OP because the problem turns out (again) to be an 'ID10T User' error.

Ken

User avatar
HansV
Administrator
Posts: 67739
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: using a user defined function in two modules

Post by HansV »

It happens to the best of us... :grin:
Regards,
Hans