Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

How to program Escape key function?

Is there a way to programmatically emulate pressing the Escape key with VBA?

Drile


Post your answer or comment

comments powered by Disqus
Hi, I want to allow users to interrupt a long updating process when users press Escape key. How VBA can detect that Escape key has been pressed while an updating process is running? Tks.

I'm stumped. Trying to code my first custom VBA function. The function is supposed to translate the words in a cell by repeatedly looping through the string and looking through a list of provided translation words.
Problem is the variable name changes with each loop, and I can't figure out how to program the name change.


	VB:
	
 
     
    Dim t As Long 
    Dim TranslateCeiling As Long 
    Dim SS1 As String 
    Dim SS As String 
    Dim Q, R As String 
    Dim W As String 
    Dim LLRString, MLRString, SLRString As String 
    Dim LRString, MRString, SRString As String 
    Dim TArray() As String 
     
    TranslateCeiling = 200 
    W = "translatelist" 
    LLRString = Worksheets(W).Cells((t + 2), 6).Text 
    LRString = Worksheets(W).Cells((t + 2), 7).Text 
    MLRString = Worksheets(W).Cells((t + 2), 9).Text 
    MRString = Worksheets(W).Cells((t + 2), 10).Text 
    SLRString = Worksheets(W).Cells((t + 2), 12).Text 
    SRString = Worksheets(W).Cells((t + 2), 13).Text 
     
    Redim TArray(2 To TranslateCeiling, 1) 
     
    Select Case ProductCell 
    Case "cs:leaderboard" 
        SS1 = Replace(TranslateCell, Worksheets(W).Range("F3").Text, Worksheets(W).Range("G3").Text) 
    Case "cs:mantle" 
        SS1 = Replace(TranslateCell, Worksheets(W).Range("I3").Text, Worksheets(W).Range("J3").Text) 
    Case "cs:skyscraper" 
        SS1 = Replace(TranslateCell, Worksheets(W).Range("L3").Text, Worksheets(W).Range("M3").Text) 
    End Select 
     
    For t = 2 To TranslateCeiling 
        Q = "SS" & t 
        R = "SS" & (t - 1) 
        Select Case ProductCell 
        Case "cs:leaderboard" 
            Q = Replace(R, LLRString, LRString) 
        Case "cs:mantle" 
            Q = Replace(R, MLRString, MRString) 
        Case "cs:skyscraper" 
            Q = Replace(R, SLRString, SRString) 
        End Select 
        TArray(t, 1) = Q 
    Next t 
     
    TestTranslate = Q 
     
End Function 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Looks like the loop keeps defining Q as "SS" & t instead of doing the select case formula. I tried removing the Q variable altogether and making it read just "SS" & t = Replace(R, LLRString, LRString) for example and keep getting a compile error.
Any ideas?

Hi,

I found that Excel does not allow C-lang style escape sequence. E.g. "
like escape is not allowed. I tried with other tinkering as well but
did not work. Could not found direct pointer from Excel help.

Can somebody help, how to put escape sequences in Excel functions?

Thanks for your time.

- KA

How to use the LOOKUP function in Excel

... searches for in the first vector. Lookup_value can be a number, text, a logical value ... The array form of LOOKUP is similar to the HLOOKUP and VLOOKUP functions. The difference is ...

How to use the LOOKUP function in Excel

The array form of LOOKUP is similar to the HLOOKUP and VLOOKUP functions. The difference is that HLOOKUP searches for lookup_value in the first row, VLOOKUP searches in the first ...

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 ...

How to use the LOOKUP function with unsorted data in Excel

LOOKUP("Mary", A2:A5, C2:C5) Both the functions return 22. Back to the top. HLOOKUP and VLOOKUP If you are looking for an exact match in Microsoft Excel, the first column of ...

How to use the LOOKUP function with unsorted data in Excel

ACC: How to Create Custom Domain Function Similar to DCount()

Advanced: Requires expert coding, interoperability, and multiuser skills. Custom domain functions can be used to provide functionality other than that provided by the standard ...

XL2000: How to Display a Custom Function in Step 2 of the Function ...

You can call custom functions with the Function Wizard in Excel just as you call built-in functions. However, there may be times when you want to use a Visual Basic for ...

Hi,

Appreciate very very much if someone can help to advise me on how to call the Public Function in an Update Query ?

Below is an example of Part Numbers in text:
12220-89210
12220-89211E
12221-00110
00866751158666E
00866751158666
00004255781123
00000125667233
00000027791001

There are no such PartNumbers as below :
12220E-89211
E12220-89211

I have from a freind the following Public Function statement that will loop through the sample PartNumbers as above and strip the left characters out until it runs into one that isn't a zero.

How can I call the Public Function in an Update Query in order to do this ?

Public Function StripZeros(strNum as String)

Do Until Left(strNum,1) < "0"
strNum = Right(strNum,Len(strNum)-1)
Loop

StripZeros = strNum

End Function

How to Remove User-Defined Functions from Function Wizard

When you create a custom function in Microsoft Excel using Visual Basic for Applications, it will be displayed in the Function Wizard under the User Defined Function category.

Does anyone have any suggestions on how to program excel to insert / delete
any worksheet?
For example,
Under column A, there is a list of numbers, such as
1,4,9, ...
If there is no existing worksheet named "1", then insert a new worksheet
named under "1", else do nothing.
Repeat this process until the end of the list.

For checking, if 3 is not included under column A and there is an existing
worksheet named under "3", then delete it.
Repeat this process until the last worksheet checked.

Does anyone have any suggestions on how to code it in excel?
Thanks in advance for any suggestions
Eric

Could someone please explain how to creat a User Function that:
- reads an input parameter (to a calculation)
- performs the calculation (spanning several hundred cells)
- returns the result (of the calculation)

I percieve the function would look something like:
- UserFunction(InputParameter,Result)

How to Create Custom Excel Functions. User Defined Function (UDF ...

Examples of user defined functions or UDF's for Excel written in VBA. Random numbers, Hyperlinks, count sum or sort by colors.

XL2000: How to Display a Custom Function in Step 2 of the Function ...

By default, this custom function appears in the Function Wizard under the function category called "User Defined." You can call custom functions with the Function Wizard in Excel ...

I want to know how to "program" a cell (for example A1 on a spreadsheet) so
that when you enter certain data into it (for example, the letter "A"),
certain data will automatically appear in other cells (for example the number
"24" in cell B1 and the number "35" in cell C1.

Dear All

I would like to learn how to program macros and functions. Could
someone please advice a good tutorial online for a beginner?

Thanks in advance,

Paul

Hey!

I want to make a sum of 5 columns.
I know how to do it with functions, but i dont want functions to be seen and neither to protect the document, so i think visual is a nice option.

How can i do it?

Thank you.

hey all,
I am new to this forum and have been using excel since several years. I am now learning VBA and want to migrate 2003 excel files to 2007 and hope that I get answers in this forum.

Specifically, I have inherited some old excel files which had macros that performed financial calculations using function names in the formula. When I save the file to convert to 2007 I get an error message which says that I should rename the user-defined functions.

Can someone please help me as to how to rename user-defined function using VBA code?

Thanks

Deepak

Hi,

I have been asked to create a spreadsheet with three columns Status - Qualified Date - Expired Date.

The Status column shows a green box with "Status" written in it

The Qualified date column is filled with dates.

I need to know how to program the Exipred Date column to show the date 6 onths later from the Qualified date Column.

and when a column reaches the date 6 month's on, for the status box to turn red with "expired written in it.

Can this be done and if so, could someone show me please.

Thanks

Hi ,
How to use the Prod Function in a list of averages to find the lease common
value !
here is an example.

AVRG
26.27
-------------------
29.27
25.61
26.17
25
25.32
--------------
3 | These are a list of Over and Under values of the above
-0.66
-0.1
-1.27
-0.95
--------------
3 | These are a list of compile Positive and Negitive values
also average

| trying to locate which one is within range of the
average,one group
-0.745
| is always known ,in this case it's (25) of the above
values,which now leave (4) items to be measure by an average, maybe if I
could find the Standard Error in the Averaging,it would help in locating(plus
or munis) and the amount !

Thanks in advance

Joseph..

i am trying to teach myself Excel plaese can you tell me good tutorial and i
would like to know how to use the " if " function


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