Applying Workbook functions to MS Access

[Hazo]

How do I reference the Excel Workbook so I can use Workbook functions? I have referenced the workbook through Tools>References in the Module window and assume I have done it correctly.
The function I want to use (QUOTIENT) returns the NAME# error when applied.
The Help functions tells me I may need to install a .dll file. The .dll file is already on my system through the normal install process for MS Office. Is there anything else I need to do in order to install the nominated .dll and/or run a Workbook function???

 

[MichaelRed]

use of the VBA functions of Excel doesn't require a reference to a workbook, but the inclusion of the Excel Library in the REFERENCES (Menu Tools->References). Use of the workbook could -perhaps- supply the argumant list, but that would APPEAR to be "the long way 'round the barn" at least twice. In general, once you have the Excel library included in your REFERENCES, you only need to figure out the propper way to the arguments and how to address the return value.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[cheerio]

It is unclear whether you are trying to access the function QUOTIENT from VBA within Excel or within another Office Application.

Michael's note above covers the general process of using the Excel library from another office application.

However, you have a much more fundamental problem. The worksheet function library available to VBA does not include those Excel functions where there is a VBA equivalent. QUOTIENT is regarded as one of those functions. That's because QUOTIENT(10,3) can be expressed in VBA as 10\3.

So you cannot access QUOTIENT from within VBA even in the Excel environment never mind any other Office Application.

I did try using the Excel PROPER function from Access 2000 VBA after creating a reference. Proper on its own failed but the full call of

worksheetfunction.Proper("xyz&quot

was ok so you need to point to the right branch in the library. Some older versions of Excel did not use worksheetfunction and it is probable that Excel itself implies worksheetfunction to obtain backwards compatibility. However, calling from another VBA host requires the full call.

Ken

 

[Hazo]

Thank you for your tips. I am trying to call the QUOTIENT workbook function from a SetValue action in a macro in MSAccess 2000.
the Help Guide (Office Assistant) does specify the QUOTIENT function can be called (along with a lot of other workbook functions). I can not however get any of them to work.
I have referenced the Excel 9.0 Object Library through Tools-->References in the module window but it does not seem to want to work.

 

[cheerio]

It won't work for the reasons I explained.

If you really want a QUOTIENT function then go to the modules window in Access - Insert a new module and paste in the following:

Public Function QUOTIENT(a As Long, b As Long) As Long
QUOTIENT = a \ b
End Function

You should then have a QUOTIENT function.

However, that is all unnecessary. Access itself understands the backslash operator as a quotient function so you could simply use a\b in Access itself.

Ken

 

[MichaelRed]

Since I do not use Macros in Ms. A., I am not familiar with any restrictions on the use of functions (eithier native ones or those included via reference). In a procedure, it can does/will work - provided it is given the proper arguments and the results are assigned to a valid variable (including having hte proper type). Further, by calling in a module you could (perhaps) get a reasonable error message re WHY it doesn't work (prpoerly).


(Hint - quit using macros and learn to code).





MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[Hazo]

Thank you Ken and Michael. Ken's suggestion has worked. It was sooo simple in the end.
And yes Michael, everybody tells me to learn to code instead, but I have not yet bit the bullett.
Thanks guys
Chris

 

[Hazo]

Another question in the same vein - is there a simple character or operator for the MOD function (like the backslash for QUOTIENT)???
I would like to return a value equal to the remainder of a division.

 

[cheerio]

You can do this in VBA. Create a module as instructed above and paste in

Public Function MyMOD(a, b)
MyMOD = a Mod b
End Function

Then use MyMOD as your function.

Because the MOD function exists in VBA I don't think you would be able to reference the Excel MOD function. In any event that route would require Excel to be installed. The VBA route works for Access as a stand alone application.

 

[MichaelRed]

"Mod" is a core VB function and thus is available in all 'flavors' of the language.

The variations in the dialects are (primarily) for addressing the specific applications' "Object Model", not the general purpose functionallity.






MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[Hazo]

Hi
I have tried this and get a value I was not expecting. As an example:
33 Mod 4 = 0.25 (I assume this is how the MOD function should work)
Instead I get:
33 Mod 4 = 1
Why?

 

[MichaelRed]

See the ubiquitous {F1}. It is some what clearer then many of the Ms. Speak jargon explinations. MOD is an INTEGER function.

Ass-U-Me may have been derived from some concatenation of the words of hte syllables?)





MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[cheerio]

33 MOD 4 means if I share 33 sweets between 4 children how many are left over. If I am fair and give 8 each 1 is left over (for me). So the answer is 1. It will always be an integer answer.