Free Microsoft Excel 2013 Quick Reference

Programming Keyboard Commands in VBA

hello there people.

I was just wondering if theres possible to program a keyboard coammnd in VBA? Like using a commando button that executes something like CTRL+J at once? (CTRL+J is a command for stepper to execute a G coding, just and example tho)

Best Regards - Sebastian a.k.a Gobiten (Sugar in swedish moahaha)


Post your answer or comment

comments powered by Disqus
I am attempting to use the methods described in Rod Stephens "Visual Basic Graphics Programming" (2nd edition) in VBA with Excel. I'm hoping to be able to add a graphics window to a workbook that shows the relative position and orientation a couple of rectangles that represent two colliding vehicles. The position and orientation are based on data listed on Sheet1 of an Excel workbook.

I created a new module in the VB Editor and borrowed some simple code from the book that draws a rectangle using the Line command after changing the scale using the ScaleX and ScaleY commands. VBA did not like the scale commands but did not seem to mind the Line command.

The code I used:

'wid = ScaleX(5, vbPixels, ScaleMode)
'hgt = ScaleY(5, vbPixels, ScaleMode)
wid = 50
hgt = 50
Line (10, 10)-Step(wid, hgt), vbRed, BF

So I commented out the scale commands and filled numbers directly in the Line command. The code executes successfully (at least no errors) but I never get a window that opens up to display the rectangle.

I'm using Excel 2003 on a Vista machine.

Am I missing something in the availability of the VB commands in the VBA/Excel environment? Shouldn't I have all VB available to me?

Is this so simple that I'm really missing the boat on this? I am a novice at the graphics part of the VBA development, but have done a bit of programming in VBA.

Oh just something to ponder for a Friday.

What's the most powerful command in VBA?
Hmmm... perhaps this one. But few seem to use it, or mention it. I posted this one to my mail group and received no comments... it got burried quickly so I thought I'd allow it to live a little longer here. It's one of my favorites.


	VB:
	
 'anyone knows about it, understands it or uses it.
 
 'Can't use worksheet formulas directly in VBA right?
 'Run this macro:
 
Sub Neato() 
    MsgBox = EVALUATE("SUM(A1:A10)") 
End Sub 
 
 
 'Yeah I know, what about:
Set Fn = Application.WorksheetFunction 
x = Fn.SUM(Range("A1:A10")) 
 
 'or if you prefer just:
x = Application.SUM(Range("A1:A10")) 
 
 '...but in most cases, why bother?
 
 
 'Another little known EVALUATE fact; you're familiar with the
 'shorthand brackets for referencing ranges right?
 
Range("A1:A10").Select 
[A1:A10].Select 
 
 'Did you know those brackets were shorthand for EVALUATE?
 
Sub NeatoNeato() 
     
     'given...
    [A1:A10].Select 
     
     'is the same as...
    Evaluate("A1:A10").Select 
     
     'then this should work right?
    x = [SUM(A1:A10)] 
    MsgBox x 
     
     'or just...
    MsgBox [SUM(A1:A10)] 
     
     'hey... you know with those brackets, it looks just like a cell
     'in VBA doesn't it? hehehehe...
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Oh, and did I mention you can use EVALUATE in defined range names
too? But thats EVALUATE as it existed in the old command language
as opposed to VBA, so there are ways to access the power of EVALUATE
in your cell formulas without even using VBA, perhaps a secret for
another day...

I'm working on posting an example file... here's the intro text I'm working on.

EVALUATE is probably the single most powerful command in VBA. It's a bit of a surprise to me that I don't see people suggesting its usage more often; probably just a situation where people in general 'think' they know what a command does but can't really see the benefit of it. Very similar I think to the SUMPRODUCT worksheet formula; if you just took it at face value it doesn't seem like that big of a deal. If you dig a little further, a whole world of options starts to unfold.

The VBA help topic for EVALUATE is a bit vague and probably doesn't do it justice. In fact, after reading through it for yourself, you probably wouldn't see much cause to use it. Here's what it can do:

1. Converts string math expressions to values.
2. Converts 1D and 2D string arrays to their array equivalents.
3. Capable of processing any formula a worksheet cell can process! (wow, WOW)

That's right, it can do anything a cell can do! It contains all the functionality of a worksheet cell wrapped in a single VBA command. In fact, it can even do one thing that cells can't do... it can return whole arrays. So it's like having free access to a worksheet cell... only it's better than a worksheet cell in the sense that it can evaluate and return arrays.

EVALUATE, this is one of the better ones, remember it!

Hmmmm... "can do anything a worksheet cell can do", a lofty claim. Maybe too much, maybe not?

Hi,

One question which have been on my mind for quite a while since I touched excel vba 2 years back.

Is it possible to run system command in excel program?

E.g. A excel program that calls a .bat program in it.

Rgds,
Samuel

Hi there,

does anyone know hw I can execute a DOS command from VBA?

when I am in DOS,
I type in the following:

"run -propertyfile nikuuat.properties.txt"

I need my VBA macro to execute this same command when a command button is pressed.

thanks in advance,
Matt

How would I write the following SQL statement in vba for excel? How would i assign the coulmn names?

Select * From DataSheet
Where Item 'CTU.Primary_SU"
AND (Initial State = 'Active' OR 'Terminate' OR DownMajor')

Hi Guys

I have a button on a Sheet1 that copies all data to Sheet2, now i have a very basic copy command in VBA to do this but i need it to be dynamic so basically if i enter more than 1 row of data it will copy over all the data and not just the first row.

So basically i think i am going to need a LOOP somewhere to loop through Sheet1's cells and then copy them over to sheet 2

Does this make sense ?

Cheers
Alistair

Hi there

I am using the Sumproduct formula in VBA and one of my criteria is a date "BudgetMonthValue"

At the moment the following command in VBA is returning a value in date format (eg 31/7/07), however I need it to return a serial number (ie 39294).

My command is:-
I'm sure it is proabably an easy fix, however it has stumped me..

Any help is appreciated

I would like to rename a file from a module witten in VBA in Excell. Any idea what the code looks like? I tried Call Shell() without success.

file to rename: HerculesNFTC OPSBriefing Shellsops briefOps_Notes.htm
new name: HerculesNFTC OPSBriefing Shellsops briefOps_Notes.txt

ps I work under Windows XP.

thanks

Acheo

I have a simple copy and paste macro that is activated from workbook A.xls, copies a sheet from b.xls and goes back and pastes it on a sheet "data" in workbook A.xls. So the code to get back to workbook A.xls is "Windows("A.xls").Activate"
The problem is I change the name of the workbook A.xls everyday, as it gets saved with each day's date on it. SO my macro needs to be changed each day.
Is there a command in VBA to go back to the workbook from which the original macro was run from ?
Thanks from a VBA beginner.....

I would like to turn:

=sum(D2*D4)-(D3*D4)

into something similar to http://www.excelforum.com/excel-programming/360577-formula-in-vba.html

I am using following code for an auto-filter in VBA.
The code works fine in Excel 2003, however I need to run the code for machines still using Excel 2000.  Any assistance w/
making modification to code appreciated.  Thanks.

I have a bit of code in which I am trying to use the or statement. I've tried a couple of methods but neither seem to work. Is "or" a valid command in VBA? Thanks.
            If Target.Column = or(3,17) Then
                SortOrder = 1
            Else
                SortOrder = 2
            End If
also tried
            If Target.Column = 3 Or 17 Then
                SortOrder = 1
            Else
                SortOrder = 2
            End If


I'm new here, so this may have been a topic already covered, if so, sorry!

I was wondering how to program timed events in VBA. I am creating a trading algorithm for one of my classes in school.

I cannot figure out how to use timed-events in my code to, for example, run the procedure every 5 seconds, instead of clicking on a button.

Also, how do I go about setting a trigger for whenever a certain condition is met to "buy" or "sell" a security?

Thanks!

Thank you, but I think I may be searching for something that can't be done, I
don't know. What I am trying to achieve is similar to a combox box
selection, it drops down and is linked to a value field beside it i.e.

Combox Box Selector Quantity Picked Price of Item Total
Fitting No 1 2 10.00
20.00
Fitting No 2 1 3.00
3.00

I can get this process to work using a combo box (form) but in order to get
multiple selections so that they can pick another field, I could only figure
out one way to achieve this and that was add another combox box which seems
to completely defeat the purpose.

I checked the contexture.com/excelfiles.html example, tried them all,
selections worked but didn't move to the column shown. Has anyone else been
able to get it to work?

Maybe another question, the Form List Box appears to work the same as the
combo box using the Input Range and cell link, with Multi chosen. Then the
value field using Function Arguments needs to have the same vlookup value and
table array. There seems to be something missing here. Even though I have
chosen Multi and the Lookup value shows '= any', it still gives me an #N/A.
Is it possible to pick a specific from a list box and link that specific to
the value field. There are 3 items in the list box all with a value in the
third column i.e.
Column A B C E
Line No. Fitting Descr Value Lookup
1 Fitting No 1 1.00
2
3
etc.
I want to pick Fitting No 1 and get a value, Fitting No 2 and get a value etc.

Any further help gratefully accepted.

"Max" wrote:

> "nutmeg@questions.com" wrote:
> > .. allow the users to be able to pick multiple items
> > from a drop down list. I have tried using a form combo box
> > but needed to put more than one on the sheet to get the
> > results. Can this be done with a 'list box' and if so, how.
> > I have tried by putting one one and selecting 'multi'
> > but it won't select properly.
>
> Some googled thoughts which may help ..:
>
> By Debra Dalgleish:
>
> " .. You can do this with programming.
> There's a sample file here:
> http://www.contextures.com/excelfiles.html
> Under Data Validation,
> look for 'Select Multiple Items from Dropdown List' "
>
> By Dave Peterson:
>
> > I created a list box, and want to allow a user to select multiple items
> within the cell. Is there a way to do that?
>
> ... Is the listbox on a worksheet?
>
> If yes, is the listbox from the Forms toolbar?
> Then rightclick on the listbox and choose: format control
> Then on the Control tab, choose Multi in the selection type frame.
>
> If the listbox is from the control toolbox toolbar, then
> go into design mode (another icon on that control toolbox toolbar
> Rightclick on the listbox, choose properties
> Change the .multiselect property to 1 - fmMultiSelectMulti
>
> By Ton Teuns:
>
> > .. created a list box which enables multiple selections.
> want to know how you can set up a cell link to return the selected values.
> This is easily done on a single selection list box by using cell link, but I
> understand that you have to program a command in VBA to do this for a
> multiple selection list box.
>
> This code is an example of how to do the job.
>
> Sub Tester()
> Dim i As Integer, li As Integer
>
> li = 1
> With Listbox1
> For li = 0 To .ListCount - 1
> If .Selected(li) = True Then
> Cells(1, 1).Offset(0, i) = .List(li)
> i = i + 1
> End If
> Next li
> End With
> End Sub
>
> --
> Max
> Singapore
> http://savefile.com/projects/236895
> xdemechanik
> ---
>
>
>

Does any one have a complete listing of the Excel commands that can be used in VBA programming?

Thank You

Hello

I have a question about the graphics used in VBA. I have created a spreadsheet that uses Command Buttons, Text Boxes and Mutli Page User Forms and all the other a sorted items found in VBA. Well, the Graphics of these items looks a little bit dated as to what is currently available with programs for developing UI's.

I was wondering if there is a update for VBA that has the latest toolbox for the UI's like what is available in C# programming? Or, does a person have to update to using Visual Studio to get the these updates?

If we need to switch to Visual Studio, which package will do the best job for updating Excel and Windows VBA code?

I see that Visual Studio 2005 Standard and Professional versions do not claim to support Microsoft Office Development. The only one that I found is Visual Studio Tools for Office, But this package does not contain all the current programming languages that the other packages do (J#, C++ ...).

Anyone have experience with Visual Studio 2005? Care to share which one will do the job on Excel development. Seems like a lot of money for the Office tools version (around 600+) for something that hopefully will be in the other cheaper verions, Well relatively cheaper anyway. :0)

Thank you for your time

Brian G.

Oh, by the way I write my code in Office 2003.

Hi ,

I have to run command prompt from with my VBA code , and run an application passing it 2 filenames and redirecting the output of the application to a third file. Also my VBA script should wait for the application to complete running before getting back to the next line of execution. HEre is what i have coded so far :


	VB:
	
 Macro1() 
     
    Dim dosCmd As String 
     
    dosCmd = "purge.exe  input1.txt input2.txt >> opFile.txt" 
     
    Call Shell("cmd.exe " & dosCmd, vbNormalFocus) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Now this code ends up just opening the command prompt and nothing else. It does not run purge.exe. Can someone guide me on this please ?

Can I create a stand alone program in VBA, specifically I'm wanting to make a screen saver, any suggestions, thanks

Hi, I am new to VBA and is muddling through it. I am trying to create a form so that a couple of us can enter student lunch choices for our school. This is what I have:

I have a workbook with 2 spreadsheets: Orders and Students. I have created a form that will be activated by a command button in the Orders spreadsheet. This Order form looks like a single month. Each day on this form is a frame. In the frame are 1 to 3 checkboxes and corresponding textbox(s). The checkboxes lists the lunch choices available for the day. The textbox(s) is(are) for the user to enter the number of lunches for the checked lunch (i.e. student wants 3 slices of pizza). There is a combo box which contains the students in the school. The combo box is populated by a named range in rowsource. The named range points to a column on the Students spreadsheet. I also have some fields that will show the total cost for the selected lunches (except for pizza, all lunches cost the same). There are also a save, clear, and exit button. The save button will write a row for each lunch choice to the Orders spreadsheet. The clear button will reinitialize the form. The exit button will return to the Orders spreadsheet and close the form.

Onchange in the combobox, I want to use the Find method to search for the student in the Student spreadsheet to pull in the class and homeroom data.

Now, my first set of (many) questions, can I use the Find method to search the Student spreadsheet? How do I pull the Class (column C) and Homeroom (column D) and populate 2 label fields in the form.

Here is what I have (I have not even tested this code yet):

Dim wks As Worksheet
Dim rFoundResult As Range
Dim sLookingFor As String
Set sLookingFor = cboStudent.Value
Set wks = Worksheets("Students")
wks.Activate
wks.Range("A1").Activate -- where student name is located

Set rFoundResult = wks.Cells.Find(What:=sLookingFor, _ after:=wks.Range("A1"), LookIn:=xlValues, LookAt:=xlWhole, _ SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False)

If rFoundResult Is Nothing Then
labGrade.Caption = ""
labHomeroom.Caption = "Student NOT FOUND"
Else
labGrade.Caption = -- put grade from Student here
labHomeroom.Caption = -- put homeroom from Student here
End If

Hi,

I am new to VBA programming and would like to know what is the format/syntax for the command Cells? For example, I am looking at some code which reads like this:

Cells(a, 23).Select

What does it mean?

Any help would be greatly appreciated. Apologies for those who think this is a stupid question.

Cheers,
Chrissie

Hi,

I want to get all of the programs name in VBA.
I can find a program name by FindWindow Function:
Declare Function FindWindow Lib "user32.dll" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long

But I can't get all of the programs name!!!
any help?

best wishes

I am trying to open two different program files, one after the other, in VBA
within an Excel macro.

This is what I have so far:

Shell ("C:Program FilesQuick3270Quick3270.exe" "C:Program
FilesQuick3270 IBM System.ecf")

This does not work, though. Is it because one of these files is an ecf?
Thanks

Hello,

I have a program that needs to be installed on customers' machines
This excel-VBA program is using the solver add-in in excel. I'm no
sure if the users will have this add in installed on their machine. S
I would like to see if there's a way to include the add-in file with m
software and some how install the add-in using VBA coding, at th
beginning of the setup, and on the first run, on client's computer.
If there is a way, please guide me as of how to write this code in VBA
(Hints on command would be of great help.)

Thanks in advance

--
Message posted from http://www.ExcelForum.com

I want to know if there is a quick keyboard command to swap between tabs in
an excel spreadsheet?


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