Author Topic: VBA - Excel  (Read 3065 times)

0 Members and 1 Guest are viewing this topic.

Reinaldo Sampaio

  • Guest
VBA - Excel
« on: February 28, 2007, 06:04:09 PM »
I am new in this site and in VBA too.

I am creating  a routine to fill an excell spread sheet with a bill of materials.

I need to perform some calculations.

Now I need to discover the great commom denominator betwen some numbers

Any function to do it in VBA for Excel?

Thanks for your help.

JohnF

  • Guest
Re: VBA - Excel
« Reply #1 on: February 28, 2007, 10:58:59 PM »
From Excel Help:
Returns the greatest common divisor of two or more integers. The greatest common divisor is the largest integer that divides both number1 and number2 without a remainder.

If this function is not available, run the Setup program to install the Analysis ToolPak. After you install the Analysis ToolPak, you must enable it by using the Add-Ins command on the Tools menu.

How?

Syntax

GCD(number1,number2, ...)

Number1, number2, ...   are 1 to 29 values. If any value is not an integer, it is truncated.

Remarks

If any argument is nonnumeric, GCD returns the #VALUE! error value.


If any argument is less than zero, GCD returns the #NUM! error value.


One divides any value evenly.


A prime number has only itself and one as even divisors.

Examples

GCD(5, 2) equals 1

GCD(24, 36) equals 12

GCD(7, 1) equals 1

GCD(5, 0) equals 5


Reinaldo Sampaio

  • Guest
Re: VBA - Excel
« Reply #2 on: March 03, 2007, 11:01:42 AM »
I have done all procedures to use GCD in Excel.

When I open a new instance of excel, and I create a formula like

=gcd (A2:A4), it works fine.


There is still a problem.

When the program opens an instance of Excel, the formula in the cell returns
#NAME?, although "analisis tool" box In add ins options is checked.

This is the command line to put the formula in Excel. 
  ExcelSheet.Cells(StartRow, 7).Formula = "= gcd(G" & StartRow + 1 & ":G" & EndRow & ")"

- If I copy the fine working formula from its sheet and paste into the bad working cell,
it works fine, too. !!!!!
- If I copy the bad working to the good working, it doesn´t work too (???)


How can I solve this problem?

Thanks for help.
« Last Edit: March 03, 2007, 11:08:30 AM by Reinaldo Sampaio »

Murphy

  • Guest
Re: VBA - Excel
« Reply #3 on: March 03, 2007, 01:54:22 PM »
Please post the bad formula that the code generated so we can try to figure out what is wrong.

Reinaldo Sampaio

  • Guest
Re: VBA - Excel
« Reply #4 on: March 03, 2007, 02:46:43 PM »
The formula is = gcd (g7:g15)

I am using a template sheet.

When I open it and create the formulas manualy, it works fine.

But when the program does it, the cell shows a #Name? error.


I guess the problem is  Excel is not configured in the rigth way, and is not founding the Analisis Tools .

Or, maybe some configuration in the references on Visual BAsic Editor.

Thanks for your reply.


Murphy

  • Guest
Re: VBA - Excel
« Reply #5 on: March 03, 2007, 03:35:27 PM »
The formula you are creating with VBA is wrong. There is a space between the = and gcd. Remove that space and it should work fine.
Code: [Select]
ExcelSheet.Cells(StartRow, 7).Formula = "=gcd(G" & StartRow + 1 & ":G" & EndRow & ")"