Free Microsoft Excel 2013 Quick Reference

How to Use a VBA Macro to Sum Only Visible Cells

How to Use a VBA Macro to Sum Only Visible Cells

For more information about creating custom functions in Microsoft Excel 97, from the Visual Basic Editor, click the Office Assistant, type function statement , click Search, and ...


Post your answer or comment

comments powered by Disqus
Please Help,

I have run across a problem where I am forced to use a VBA macro in bringing up a word document (the word document is setup with a Password). When clicking on the macro, a pop up window will appear asking for a Password! I can view the word document (Read Only, which works great ), but if click on “CANCEL” or the OK Button, A Microsoft Visual Basic Error Debug Pop Up Window will appear giving a error code of “Run Time Error 5408” :o . ( ***Please remember to have the word document setup with a Password.***)

Below is what I have. Is there a way to trap the error code?

Sub PRD77()

Dim appWord As New Word.Application
Dim docWord As Word.Document

Set docWord = appWord.Documents.Open("S:VB2 Product DevelopmentPRDsPRD#77-

Instant VB2 User RegistrationPRD#77-InstantVB2UserRegistration.doc")

appWord.Visible = True

End Sub

Please help...

How to use a logical AND or OR in a SUM+IF statement in Excel

In Microsoft Excel, when you use the logical functions AND and/or OR inside a SUM+IF statement to test a range for more than one condition, it may not work as expected. A nested IF ...

Hello,

can anybody tell me how to use a macro from another excel sheet.
Situation :

I have 1 file called records.xls
This file has to be overwritten every time when filled (export from access).
When this opens I have to start an Excel macro.
How can I use or start a macro which is in my other file : macro.xls (empty
sheet with just a macro in it).
Or any other solution is welcome.

Jeroen

The article at this link,
http://support.microsoft.com/default...b;en-us;555159, describes
"How to use a class (object) from outside of the VBA project in which it is
declared."

I need to do something similar, but not quite the same: I have a class
module in an Add-in (vba) file that contains application level events.
Rather than using an object as described in the article, I need to use the
application level events that are defined in a class module that is located
outside of the VBA project in which it is declared.

Since I have not actually defined a class, I'm not sure what to do in the
step where code is written to instantiate the 'foreign' object.

I have attached the add-in file, in case my question is not clear. This is a
little beyond my normal level of expertise, so I will appreciate any help
that may be offered.

Thanks,

Keith

Hi,

I would like to know how to use a combo box to perform Autofilter functions for a particular column, say, column "R".
I need the combo box to have a list of items, e.g.
Apples
Oranges
Bananas
All

so that when any of the item is selected, the column "R" will automatically be filtered to display only the selected items. In addition, when the "All" list is selected, all the items would be displayed under column "R".
Hope to receive some help.

Hi,
I wish to use a VBA macro for printing a sheet by selecting a list of defined printers. I tested this code:
Sheet3.Select
Application.ActivePrinter = "Ricoh Aficio MP01 on Ne00:"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"Ricoh Aficio MP01 on Ne00:", Collate:=True

and I can print the sheet3 correctly but I wont to have the possibility to change the string "Ricoh Aficio MP01 on Ne00:".

Using:
Sheet3.Select
ActiveWindow.SelectedSheets.PrintPreview

I have the list of the printers selected but I can keep the name in a variable.

Any suggestion will be well appreciated.
Thanks in advance.
Regards,

Giovanni

--------------------------------------------------------------------------------

Hi,

I would like to know how to use a combo box to perform Autofilter functions for a particular column, say, column "R".
I need the combo box to have a list of items, e.g.
Apples
Oranges
Bananas
All

so that when any of the item is selected, the column "R" will automatically be filtered to display only the selected items in another worksheet in a transpose form. In addition, when the "All" list is selected, all the items would be displayed under column "R".
Hope to receive some help.

Hello Fellow Members,

I' am trying to figure out how to use the VLOOKUP, MAX, SUM Function together in a formula. Here is what I' am trying to do...

for some reason it wont let me post a image but here is a direct link to it:
http://i4.photobucket.com/albums/y14...hx99/excel.jpg

as you can see there are numbers in cells: C5:E13 AND G5:G13

What I want to do is find the highest number in the cells C5:E13 and SUM it with the number that is in the same ROW of G5:G13.

EXSAMPLE:

let's say the hightest number is in cell D7, I would want to ADD cell D7 with G7

here is a other one

let's say the hightest number is in cell E12, I would want to ADD cell E12 with G12

and one more

let's say the hightest number is in cell C9, I would want to ADD cell C9 with G9

and the formula will go in cell M5.

That's basically what i am trying to do, I hope I'm explaning myself right..

Thank You In Advance
_____________
Post moved from Excel programming - Richard Buttrey

Please also note the forum rule which asks you to attach actual workbook files rather than mere pictures. You can't expect those who want to help, to re-type your data.

Is it possible to get a directory listing of filenames using a VBA macro?
I know there are add-ins which you can buy to do this .... but has anyone
done it just with macros?

Nigel

XL2000: How to Use a Custom Function in Another Workbook

In Microsoft Excel, you can create custom Microsoft Visual Basic for Applications functions to perform calculations. By default, these custom functions are available to all ...

XL: How to Use a Custom Function in Another Workbook

In Microsoft Excel, you can create custom Microsoft Visual Basic for Applications functions to perform calculations. By default, these custom functions are available to all ...

Hello,

I need to figure out how to do a COUNTIFS formula on only visible cells. My original formula is: =COUNTIFS('Master List'!F2:F15000,"1.Executive Platinum",'Master List'!W2:W15000,"YES") which obviously counts all cells that meet both criteria (filtered or not). I will also add a couple conditions to it but just need help getting started. I have seen people do the SUMPRODUCT function with the OFFSET but I can't seem to get that to work for me. I welcome all advice whether it be for a UDF or a workaround or whatever.

Please Help!

Good Afternoon!
I am having an issue with trying to figure out how to program a specific macro in VBA using Excel 2007. This macro that I want to develope will concatenate a range (single rows only (example: select C20:X20)) of X (variable) amount of Cells into 1 single cell, preferably the cell preceeding the selected range (ie B20). The issue is that not all the cells in that range would be filled and each row has a different column filled in with text. Thusly, how do I program a macro that will allow me to select a range of cells, pick out only the cells filled with text, and concatenate those cells into a single cell just preceeding the range in the same row? Then from there i would be able to copy that macro and and paste it in an infinite number of rows below each row selecting it's own row range next to it and applying the concatenation (example concatenate C20:X20, copy and paste the macro to the next cell and it will automatically select and concatenate C21:X21... etc...). Thanks for taking the time in considering my post

thanks!

I don't understand how to use the Caller Property. I tried using the example shown in the VBA Help, but it gets an error. They show something like:
v = Application.Caller.Address
I would dearly like to be able to determine a calling macro's
name, but haven't a clue about how to do it.

I am using Excel 2007, and almost know enough to be dangerous. Today I learned the term VBA for the first time, but have ABSOLUTELY NO IDEA how to use it. Discovering the "countif" function late last week was one of my excel crowning achievements. Please keep this in mind while explaining the solution to me. Please "break it down Barney style" (in kindergarten terms) for me, and don;t worry about insulting my intelligence. I am building a spreadsheet that will have a row in which the only acceptable input for each cell is "1" (cell maybe left empty), but the "1" may be typed in either red, blue, or black font. I need two seperate rules. I need one rule to add (or count how many) Red "1"s are in the row, and a second to count how many Blue "1"s are in the row.
Your assistance will be greatly appreciated,
Thanks,
Chris

This macro check all cells in columns A; in my VBA a Module call a Function
and it runs after all cell values in column A has been checked. During the
process It prompts with a MsgBox if this cell has errors ONLY (this MsgBox is
inside this Function).

if this CELL has errors. I want to add in this MsgBox an option to:

Yes:GOTO cell with errors and then CLOSE vba macro OR
No: continue with the loop checking all values in column A.

My question is:
There is a command that I can put inside a function to kill or force the
Macro to end?

I tried

"End Function"

but it kills only the function and not the entire forms (3 forms)

Thanks

Coco

Hello. This is a pretty simple question, but for some reason I haven't been
able to figure out how to do it. If I write some vba code and compile it, is
it possible to get this macro to show up on the Tools->Macro menu so people
don't have to enter the code editor to run the macro? Thanks.

I was wondering if it is possible to use a value that the user inputs into a cell, in a filename for linking to another spreadsheet in VBA.

For example, if the user enters "C-11838" in cell B4, I want to be able to set the forumla in C4 to ='K:GMGNets on FloorFinished Nets[C-11838.xlsm]Net Summary'!$B$4

I can't figure out how to set it up so that the number before .xlsm can be a variable.

Any help would be greatly appreciated!

I have a question on how to access a VBA macro from a web page. Pardon me if this is a wrong place to ask this question.

Scenario: I have a macro in VBA. I would like it to be accessed via a webpage. The reason I want the macro to be leveraged is that it involves simulations & regressions that can be performed in Excel only.

For eg.: There is a macro that calculates product of two numbers when supplied with two numbers. I want the user to input 2 numbers on the web interface and submit a button so that it triggers the macro from Excel on the server and returns the output to the GUI.

Background: I have moderate level of understanding of VBA. I do not have any back ground on web development/scripting language.

I would like to know how I can approach the above problem. Thanks in advance.

I am trying to sort out how to add a sub-form to a form. There seems to be lots of advice on how to do this in Access, however I haven’t found a solution in Excel.

I have a user form that will collect some data from the user through combo boxes and populate the appropriate cells in the spreadsheet. One of the pieces of data will be a date that the user selects from a calendar. I am quite certain that there will be a few users that do not have the mscal (I think it is called) control on their machine so I would like to use a version of calendar that is programmed in vba that I discovered.

If someone could point me to an example of where this is done or point me in the right direction I would be mighty grateful.

I suspect that this will turn out to be obvious, nevertheless the solution is eluding me.

Many thanks,

winger

I am trying to start a VBA Macro in Excel using a VBScript in my browser.

Below you can see my - not working - chunk of code.

All suggestions are welcome!

Code:
Test
******** LANGUAGE="VBScript">

*********>


Push the button to start the script.



Thanks in advance.

Bye!

Wouter1234

Hmmm - Just tried it on XL2000 and it worked fine. You haven't got any event
macros that might be conflicting as it was a new workbook.

Do other macros work OK, eg just doing basic stuff. Is this peculiar to just
Solver?

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Wayne Stewart" > wrote in message
...
> Thanks for the response Ken.
>
> I just loaded up a new excel workbook and inserted your
> ExampleSolv macro in a module within this new workbook. I
> established the Solver reference as instructed since the
> module is new (this was done already on my original macro).
>
> When I run the macro nothing happens (just like my
> original macro). The screen seems to blink for a
> microsecond, as if something is being calculated in the
> background. However, cell A2 doesn't change from 5 to 1
> like it's supposed to.
>
> This is a very basic macro test and solver doesn't
> respond. Is the problem with Excel?
>
> For you reference, I am running Windows 2000, Excel 2000
> SP-3
>
> Any other thoughts?
>
> Thanks.
> -Wayne
>
> >-----Original Message-----
> >Do you mean you still have to hit OK on the dialog box
> for it to take the value?
> >Have you referenced the Solver addin from your project
> (tools / References /
> >Check 'Solver')
> >
> >Does your code look something like this:-
> >
> >Sub ExampleSolv()
> > SolverOk SetCell:="$A$3", MaxMinVal:=3,
> ValueOf:="10", ByChange:="$A$2"
> > SolverSolve (True)
> >End Sub
> >
> >Note you need the parameter True set on the SolverSolve
> bit if you want it to
> >accept the value within the code without a dialog box.
> >
> >Does the above code work for you if you try that with say
> 10 in A1, 5 in A2 and
> >=A1*A2 in A3?
> >
> >--
> >Regards
> > Ken....................... Microsoft MVP -
> Excel
> > Sys Spec - Win XP Pro / XL 00/02/03
> >
> >----------------------------------------------------------
> ------------------
> >It's easier to beg forgiveness than ask permission :-)
> >----------------------------------------------------------
> ------------------
> >
> >
> >
> >"Wayne Stewart" > wrote in message
> ...
> >> I am trying to automate the Solver routine (Under the
> >> Tools pull-down menu) using a VBA macro. I have
> succeeded
> >> at setting the constraints and the various options using
> >> the "SolverAdd" and "SolverOptions" commands. However,
> >> the problem seems to be with the "SolverOK" command,
> where
> >> the "Set Target Cell", "MaxMinValue", and "By Changing
> >> Cells" fields are set. Solver does not seem to register
> >> these values when the "SolveOK" command is used. Also,
> >> when I use the "SolverReset" command followed
> >> by "SolverAdd", and "SolverOptions" commands, no
> >> parameters are passed to solver either.
> >>
> >> The only way I can get the "By Changing Cells" cells
> >> actually changing on the excel worksheet is to first
> >> invoke solver manually (outside the macro), get the
> >> solution by clicking "solve", then deleting the fields
> in
> >> the solver dialog box, closing solver, and then running
> >> the macro!
> >>
> >> Is there a glitch with Excel? Is there a patch that can
> >> be downloaded to fix this problem? I'm positive that my
> >> syntax is correct (the help examples are very straight
> >> forward).
> >>
> >> I don't get any error messages when I run the macro. It
> >> simply just doesn't present the solution on the
> worksheet.
> >>
> >> Hope someone can help me!
> >
> >
> >---
> >Outgoing mail is certified Virus Free.
> >Checked by AVG anti-virus system (http://www.grisoft.com).
> >Version: 6.0.567 / Virus Database: 358 - Release Date:
> 24/01/2004
> >
> >
> >.
> >

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.567 / Virus Database: 358 - Release Date: 24/01/2004

Hello,

I am fairly new to VBA and macro programming. I would like to devise a
macro that processes a bunch of workbooks. I would like to create a
userform such that I can drag and drop workbooks from windows explorer
into a listbox to determine which files I would like to process.

I found tons of examples online of how to devise a VBA program to
accept "drag and dropped" files from windows explorer, however when I
try to do this from within an excel vba program, I find that the
listbox is slightly different and does not contain the odeDragDrop
functionality.

Is there some kind of library or something I need to import into my
excel vba program? I hope I am making some sense... please help!

Hi
am a novice in excel...i have a excel sheet with 2 columns with around 10000
records.i have placed a button in the excel sheet and on clicking the button
it should find all the values in the first column which doesnt have a match
in the second column.for that i can use VLOOKUP function.but i dont know how
to use that one.
Can any one help me?

Thanks in advance....

With Regds

Sunil.T


No luck finding an answer? You could always try Google.