Home » Forums » Open Source Learning Center » Question and Answer

IF Function in VBA

Submitted by silky on 14 November, 2012 - 20:46

I managed to create a macro to show the following:

If (I3<>0,I3*G3,H3*G3) and this repeats itself for cell N3,R3, V3,Z3 ETC.

Option Explicit
Sub Eg()
Range("J3, N3,R3, V3,Z3,AD3,AH3,AL3,AP3,AT3,Ax3,BB3,XF3,BJ3").Formula = "=IF(RC[-1]<>0,RC[-1]*RC[-3],RC[-2]*RC[-3])"
End Sub

Let me explain a bit more how this should work:

This report needs to be downloaded from an application.
The macro needs to be attached to this report so that when I download the report the macro automatically runs this formula in the appropriate columns.

Also I 'll have to populate the spreadhseet for all the rows with this formula.

The columns where the formula should sit are not blank but this needs to be catered for in the report automatically once the macro is run.

What am I missing here?

Hope you'll be able to help.

Thanks.

AttachmentSize
Example_Actual_Spend_on_Time_Sheet_16_11_v1.xls40.5 KB
‹ lower to upper case letter Sending word document to multiple email addresses from a list box ›
»

Well, it is a normal

Submitted by Argyronet on 22 November, 2012 - 20:22.

Well, it is a normal behavior...
In your Excel application, you have created your own PERSONAL.XLSB and the macro is only available from this hidden workbook.
To share the macro for another user, the target user has to create also his own PERSONAL.XLSB and then, insert the entire bloc of code I wrote for you.
To be more understandable, all the users that want to use this process have to do all what you have made yourself :
1/ Record a void macro
2/ Copy the code of the Eg_New into the module of the VBA Project of personal.xlsb
3/ Put the command button into a new group within the home tab of the ribbon
4/ Select the macro PERSONAL.XLSB!Eg_New from the command list and drag the EG_New into the right frame where the new group has been created
5/ Renaming the macro and attach an icon

There is another solution that consists to export the module of your PERSONAL.XLSB by a right click on the module name. Save it with an appropriate name on you Hard disk. Send it to all target users by a mail (take care of the *.bas files as attached file, some mail clients such as outlook are configured to avoid this type of file : so you have to change the extension "bas" as "txt" for example).
Once the user received your mail, he creates his PERSONAL.XLSB by recording a void macro. From the Visual Basic Editor, he imports the file you sent him just after stored the file into his own hard disk (he has to rename the file with "bas" if the attached had its extension as "txt" one)...

Did you understand these explanations ?

»

I think I have understood it.,,,,

Submitted by silky on 22 November, 2012 - 20:51.

Is there no other way?

The reason I'm asking is we could have random users wnating to runt he report and it would be quite tedious for remembering to tell them to add the macro.The risk here is that people might mess this macro by too many manipulations.

Is it possible not to have this personal.xlsb and make it public so that they can see the icon?

So near yet so far....

Please advise.

»

Oops, sorry , I forgot to

Submitted by Argyronet on 28 November, 2012 - 08:15.

Oops, sorry , I forgot to click to the next page and I didn't received notification for your message.

Well, sharing a personal.xlsb is strongly not recommanded because of the safe policies defined within Office 2010.
What you can do at the end is to create an addin (xlam) that is stored into the Addins folder.
When Excel starts, the addin is loaded and the button is installed automatically into the ribbon without doing nothing. The code is wrote into the addin so there is nothing to create and it is not necessary to create a personal workbook.

Do you think it's a better way for you...

»

Yes this will work better for me......

Submitted by silky on 29 November, 2012 - 08:16.

I'll try to create an addin. Do I just create an addin then add the code?

»

Well, yes and no...It is not

Submitted by Argyronet on 29 November, 2012 - 09:09.
Well, yes and no...

It is not suffiscient

You have to do the things in this order :

A)First, read this page :

http://msdn.microsoft.com/en-us/library/office/cc508991(v=office.11).aspx

B)Then download and intall the CustomUIEditor tool from this address:

http://openxmldeveloper.org/cfs-file.ashx/__key/communityserver-components-postattachments/00-00-00-02-39/OfficeCustomUIEditorSetup.zip

1/ Create a new empty xlsm workbook

2/ Save-it as EGDataTool.xlsm

3/ Open VBE and add a new module you name "basMain"

4/ Copy the functional code of the subroutine "Eg_New()" from personal.xlsb

5/ Modify the subroutine as is :

Sub Eg_New(ByRef Button As IRibbonControl)

6/ Close the workbook

C)Open it with CustomUIEditor program

You will have a blank page inside which you have to write XML lines to create the custom button into the ribbon.

Then, Copy and Paste the below XML bloc :

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui"> 	
	<!-- Created par Argyronet  - nov 2012 -->
  <ribbon>
    <tabs>
      <tab idMso="TabHome" >
        <group id="tabTools" label="Total Spend" insertBeforeMso="GroupClipboard" >
					<button id="cmdApplyFormulas" label="Apply formulas" onAction="basMain.Eg_New" imageMso="PasteFormulasAndNumberFormatting" size="large" supertip="Click here to apply formulas ans subtotals" showLabel="false" />
        </group>
      </tab>
 
    </tabs>
  </ribbon>
</customUI>

Save the document and close CustomUIEditor.exe

1/ Reopen Excel and open again this workbook...

- Normally, if you have made all what I wrote you, you will have a customized button inserted into the Home tab at the first position.

- You can click on this to test.

2/ Answer No at the MsgBox.

3/ Save the Workbook as an Addin *.xlam

- It will be normally stored into your own Addins folder.

4/ Close all opened workbooks

D) Go to Option by the File menu.

Click on Add-Ins item

Choose Excel Add-ins

Browse to locate the addin you've just created

(By default, the path is C:\Users\%USERNAME%\AppData\Roaming\Microsoft\AddIns) so you will find "EGDataTool.xlam"

Check the checkbox if it is not checked

Your addin is now loaded each time you will open Excel.

E) Finally, for your users, you will just have to distibute this addin and configure it with the same explanations just above.

»