| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Keyboard Macros
What's the best way to set the following keystrokes up as a
keyboard macro? F2, CTRL+A, F4, [Enter] I keep wanting real keyboard macros but for some reason, the recording of these keypresses doesn't work. F2 doesn't get recorded, the cell contents gets recorded (and that's not any part of the keystroke sequence), F4 doesn't get recorded even though F4 forces the cell into an Absolute Reference, CTRL+A isn't recorded. I'm using Excel XP. I keep thinking that I'm going to have to use SendKeys, but this is getting way to complicated to do something that is a basic component of DOS spreadsheets and advanced DOS code editors. I must have overlooked something somewhere. I'm not sure what a Macro after trying to do this. ;-) -- Jim Carlock http://www.microcosmotalk.com Feel free to post back to the newsgroup! |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Keyboard Macros
I toyed with this but didn't find a straight-forward solution. I was hoping
one of the gurus would know just the right method call. Is it feasible to get the cell value, parse the cell value for cell references, make them absolute (e.g., add $s), then assign the new value back to the cell? -- Bob Kilmer "Jim Carlock" wrote in message ... What's the best way to set the following keystrokes up as a keyboard macro? F2, CTRL+A, F4, [Enter] I keep wanting real keyboard macros but for some reason, the recording of these keypresses doesn't work. F2 doesn't get recorded, the cell contents gets recorded (and that's not any part of the keystroke sequence), F4 doesn't get recorded even though F4 forces the cell into an Absolute Reference, CTRL+A isn't recorded. I'm using Excel XP. I keep thinking that I'm going to have to use SendKeys, but this is getting way to complicated to do something that is a basic component of DOS spreadsheets and advanced DOS code editors. I must have overlooked something somewhere. I'm not sure what a Macro after trying to do this. ;-) -- Jim Carlock http://www.microcosmotalk.com Feel free to post back to the newsgroup! |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Keyboard Macros
Jim,
You can use the HasFormula property to determine whether a cell contains a formula. E.g., If ActiveCell.HasFormula = True Then ' do something End If -- Cordially, Chip Pearson Microsoft MVP - Excel www.cpearson.com "Jim Carlock" wrote in message ... In addition to determining if the cell is absolutely or relatively referenced, what's the suggested mannerism for determining if there's a formula in the cell? If (cell holds a formula) Then If (cell is relatively referenced) Then ActiveCell.Formula = Application.ConvertFormula( _ ActiveCell.Formula, _ FromReferenceStyle:=xlA1, _ ToAbsolute:=True) Else ActiveCell.Formula = Application.ConvertFormula( _ ActiveCell.Formula, _ FromReferenceStyle:=xlA1, _ ToAbsolute:=False) End If End If -- Jim Carlock http://www.microcosmotalk.com Feel free to post back to the newsgroup! "Jim Carlock" wrote in message ... Thanks Chip. Ended up using the following to get what I wanted. ActiveCell.Formula = Application.ConvertFormula( _ ActiveCell.Formula, _ FromReferenceStyle:=xlA1, _ ToAbsolute:=True) Took me a while to figure out that xlA1 wasn't a cell and that it was a style of referencing cells. I was going nuts wondering why you gave an example that referenced Cell ("A1"). Now I'm wondering how to get to the ToAbsolute information so that I can read that information. -- Jim Carlock http://www.microcosmotalk.com Feel free to post back to the newsgroup! "Chip Pearson" wrote in message ... You can use the Application.ConvertFormula method to change a formulas reference style. Range("A1").Formula = Application.ConvertFormula(Range("A1").Formula, _ FromReferenceStyle:=xlA1, ToAbsolute:=True) -- Cordially, Chip Pearson Microsoft MVP - Excel www.cpearson.com |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Keyboard Macros
Jim,
I've tried ActiveCell.LocalAddress(RowAbsolute) but these Excel functions don't seem to operate in the fashion that C functions operate in. The := stuff is really bugging me, big time. It almost appears that they originally were thinking that RowAbsolute:=True and ColumnAbsolute:=True could be placed anywhere in the function parameter list in any order, but I doubt that's the case. In fact, that is indeed the case. When you use Named Arguments, you can put them in any order you like. However, once you use one named argument in a function call, you must use named arguments for all subsequent arguments. For example, the following calls are syntactically correct, r = f(1,2,3,4) r = f(1, 2, c:=1, d:=4) but r =f(1,2,c:=1,4) is not. You don't have to use Named Argument if you prefer the "C-style" positional syntax, but named arguments make the code vastly more readable. For example, which is more self-documenting? r = f(0,,,,,,,,,,,,,,,,1) or r = f(arg1:=0,arg15:=1) -- Cordially, Chip Pearson Microsoft MVP - Excel www.cpearson.com "Jim Carlock" wrote in message ... Thanks Chip. I've tried ActiveCell.LocalAddress(RowAbsolute) but these Excel functions don't seem to operate in the fashion that C functions operate in. The := stuff is really bugging me, big time. It almost appears that they originally were thinking that RowAbsolute:=True and ColumnAbsolute:=True could be placed anywhere in the function parameter list in any order, but I doubt that's the case. I can see the names being used as place holders which means... it's all very interpretive. I'm babbling now. Guessing I'll need to create a function to check to check to see if the strings contain $. InStr should work well enough for that. Thanks for your time. -- Jim Carlock http://www.microcosmotalk.com Feel free to post back to the newsgroup! "Chip Pearson" wrote in message ... Jim, You can use the HasFormula property to determine whether a cell contains a formula. E.g., If ActiveCell.HasFormula = True Then ' do something End If -- Cordially, Chip Pearson Microsoft MVP - Excel www.cpearson.com "Jim Carlock" wrote in message ... In addition to determining if the cell is absolutely or relatively referenced, what's the suggested mannerism for determining if there's a formula in the cell? If (cell holds a formula) Then If (cell is relatively referenced) Then ActiveCell.Formula = Application.ConvertFormula( _ ActiveCell.Formula, _ FromReferenceStyle:=xlA1, _ ToAbsolute:=True) Else ActiveCell.Formula = Application.ConvertFormula( _ ActiveCell.Formula, _ FromReferenceStyle:=xlA1, _ ToAbsolute:=False) End If End If -- Jim Carlock http://www.microcosmotalk.com Feel free to post back to the newsgroup! "Jim Carlock" wrote in message ... Thanks Chip. Ended up using the following to get what I wanted. ActiveCell.Formula = Application.ConvertFormula( _ ActiveCell.Formula, _ FromReferenceStyle:=xlA1, _ ToAbsolute:=True) Took me a while to figure out that xlA1 wasn't a cell and that it was a style of referencing cells. I was going nuts wondering why you gave an example that referenced Cell ("A1"). Now I'm wondering how to get to the ToAbsolute information so that I can read that information. -- Jim Carlock http://www.microcosmotalk.com Feel free to post back to the newsgroup! "Chip Pearson" wrote in message ... You can use the Application.ConvertFormula method to change a formulas reference style. Range("A1").Formula = Application.ConvertFormula(Range("A1").Formula, _ FromReferenceStyle:=xlA1, ToAbsolute:=True) -- Cordially, Chip Pearson Microsoft MVP - Excel www.cpearson.com |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Keyboard Macros
Hi Chip:
r = f(0,,,,,,,,,,,,,,,,1) or r = f(arg1:=0,arg15:=1) << You must have meant: r = f(arg1:=0,arg17:=1) I just couldn't help counting those commas <vbg. Regards, Vasant. "Chip Pearson" wrote in message ... Jim, I've tried ActiveCell.LocalAddress(RowAbsolute) but these Excel functions don't seem to operate in the fashion that C functions operate in. The := stuff is really bugging me, big time. It almost appears that they originally were thinking that RowAbsolute:=True and ColumnAbsolute:=True could be placed anywhere in the function parameter list in any order, but I doubt that's the case. In fact, that is indeed the case. When you use Named Arguments, you can put them in any order you like. However, once you use one named argument in a function call, you must use named arguments for all subsequent arguments. For example, the following calls are syntactically correct, r = f(1,2,3,4) r = f(1, 2, c:=1, d:=4) but r =f(1,2,c:=1,4) is not. You don't have to use Named Argument if you prefer the "C-style" positional syntax, but named arguments make the code vastly more readable. For example, which is more self-documenting? r = f(0,,,,,,,,,,,,,,,,1) or r = f(arg1:=0,arg15:=1) -- Cordially, Chip Pearson Microsoft MVP - Excel www.cpearson.com "Jim Carlock" wrote in message ... Thanks Chip. I've tried ActiveCell.LocalAddress(RowAbsolute) but these Excel functions don't seem to operate in the fashion that C functions operate in. The := stuff is really bugging me, big time. It almost appears that they originally were thinking that RowAbsolute:=True and ColumnAbsolute:=True could be placed anywhere in the function parameter list in any order, but I doubt that's the case. I can see the names being used as place holders which means... it's all very interpretive. I'm babbling now. Guessing I'll need to create a function to check to check to see if the strings contain $. InStr should work well enough for that. Thanks for your time. -- Jim Carlock http://www.microcosmotalk.com Feel free to post back to the newsgroup! "Chip Pearson" wrote in message ... Jim, You can use the HasFormula property to determine whether a cell contains a formula. E.g., If ActiveCell.HasFormula = True Then ' do something End If -- Cordially, Chip Pearson Microsoft MVP - Excel www.cpearson.com "Jim Carlock" wrote in message ... In addition to determining if the cell is absolutely or relatively referenced, what's the suggested mannerism for determining if there's a formula in the cell? If (cell holds a formula) Then If (cell is relatively referenced) Then ActiveCell.Formula = Application.ConvertFormula( _ ActiveCell.Formula, _ FromReferenceStyle:=xlA1, _ ToAbsolute:=True) Else ActiveCell.Formula = Application.ConvertFormula( _ ActiveCell.Formula, _ FromReferenceStyle:=xlA1, _ ToAbsolute:=False) End If End If -- Jim Carlock http://www.microcosmotalk.com Feel free to post back to the newsgroup! "Jim Carlock" wrote in message ... Thanks Chip. Ended up using the following to get what I wanted. ActiveCell.Formula = Application.ConvertFormula( _ ActiveCell.Formula, _ FromReferenceStyle:=xlA1, _ ToAbsolute:=True) Took me a while to figure out that xlA1 wasn't a cell and that it was a style of referencing cells. I was going nuts wondering why you gave an example that referenced Cell ("A1"). Now I'm wondering how to get to the ToAbsolute information so that I can read that information. -- Jim Carlock http://www.microcosmotalk.com Feel free to post back to the newsgroup! "Chip Pearson" wrote in message ... You can use the Application.ConvertFormula method to change a formulas reference style. Range("A1").Formula = Application.ConvertFormula(Range("A1").Formula, _ FromReferenceStyle:=xlA1, ToAbsolute:=True) -- Cordially, Chip Pearson Microsoft MVP - Excel www.cpearson.com |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Keyboard Macros
"Chip Pearson" wrote:
Jim, I've tried ActiveCell.LocalAddress(RowAbsolute) but these Excel functions don't seem to operate in the fashion that C functions operate in. The := stuff is really bugging me, big time. It almost appears that they originally were thinking that RowAbsolute:=True and ColumnAbsolute:=True could be placed anywhere in the function parameter list in any order, but I doubt that's the case. In fact, that is indeed the case. When you use Named Arguments, you can put them in any order you like. However, once you use one named argument in a function call, you must use named arguments for all subsequent arguments. For example, the following calls are syntactically correct, r = f(1,2,3,4) r = f(1, 2, c:=1, d:=4) but r =f(1,2,c:=1,4) is not. That explains a lot of the confusion I was going through. Thanks for the explanation! Would you know if all varieties of VBApp are like that? That is, Access and PowerPoint? I know vbScript runs through a different interpreter and is useful for networking, scheduling, email, user login information, etc and most vbScript is CreateObject("object") and then using the object.properties / methods. Right now I'm referencing DAO through Excel to grab data and put the information into a few Excel spreadsheets. Things are working out very well, better than I could have ever imagined. VBApp is a little slow, but I'm going to try setting AutoCalc off as I've read in some of Ogrevies statements in this newsgroup. ;-) He reminds me of an ogre and thus I remember his name. I promise not to refer to you as Frito-Lay. Maybe Ruffles, but definitely not Frito-Lay. ;-) You don't have to use Named Argument if you prefer the "C-style" positional syntax, but named arguments make the code vastly more readable. For example, which is more self-documenting? r = f(0,,,,,,,,,,,,,,,,1) or r = f(arg1:=0,arg15:=1) Things sure do look different when I get a grip on what's really happening. Thanks much. -- Jim Carlock http://www.microcosmotalk.com Feel free to post back to the newsgroup! -- Cordially, Chip Pearson Microsoft MVP - Excel www.cpearson.com "Jim Carlock" wrote in message ... Thanks Chip. I've tried ActiveCell.LocalAddress(RowAbsolute) but these Excel functions don't seem to operate in the fashion that C functions operate in. The := stuff is really bugging me, big time. It almost appears that they originally were thinking that RowAbsolute:=True and ColumnAbsolute:=True could be placed anywhere in the function parameter list in any order, but I doubt that's the case. I can see the names being used as place holders which means... it's all very interpretive. I'm babbling now. Guessing I'll need to create a function to check to check to see if the strings contain $. InStr should work well enough for that. Thanks for your time. -- Jim Carlock http://www.microcosmotalk.com Feel free to post back to the newsgroup! "Chip Pearson" wrote in message ... Jim, You can use the HasFormula property to determine whether a cell contains a formula. E.g., If ActiveCell.HasFormula = True Then ' do something End If -- Cordially, Chip Pearson Microsoft MVP - Excel www.cpearson.com "Jim Carlock" wrote in message ... In addition to determining if the cell is absolutely or relatively referenced, what's the suggested mannerism for determining if there's a formula in the cell? If (cell holds a formula) Then If (cell is relatively referenced) Then ActiveCell.Formula = Application.ConvertFormula( _ ActiveCell.Formula, _ FromReferenceStyle:=xlA1, _ ToAbsolute:=True) Else ActiveCell.Formula = Application.ConvertFormula( _ ActiveCell.Formula, _ FromReferenceStyle:=xlA1, _ ToAbsolute:=False) End If End If -- Jim Carlock http://www.microcosmotalk.com Feel free to post back to the newsgroup! "Jim Carlock" wrote in message ... Thanks Chip. Ended up using the following to get what I wanted. ActiveCell.Formula = Application.ConvertFormula( _ ActiveCell.Formula, _ FromReferenceStyle:=xlA1, _ ToAbsolute:=True) Took me a while to figure out that xlA1 wasn't a cell and that it was a style of referencing cells. I was going nuts wondering why you gave an example that referenced Cell ("A1"). Now I'm wondering how to get to the ToAbsolute information so that I can read that information. -- Jim Carlock http://www.microcosmotalk.com Feel free to post back to the newsgroup! "Chip Pearson" wrote in message ... You can use the Application.ConvertFormula method to change a formulas reference style. Range("A1").Formula = Application.ConvertFormula(Range("A1").Formula, _ FromReferenceStyle:=xlA1, ToAbsolute:=True) -- Cordially, Chip Pearson Microsoft MVP - Excel www.cpearson.com |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Keyboard Macros
Jim,
VBScript doesn't support named arguments, but VBA does, regardless of the host application (e.g., Excel, Word, etc). -- Cordially, Chip Pearson Microsoft MVP - Excel www.cpearson.com "Jim Carlock" wrote in message ... "Chip Pearson" wrote: Jim, I've tried ActiveCell.LocalAddress(RowAbsolute) but these Excel functions don't seem to operate in the fashion that C functions operate in. The := stuff is really bugging me, big time. It almost appears that they originally were thinking that RowAbsolute:=True and ColumnAbsolute:=True could be placed anywhere in the function parameter list in any order, but I doubt that's the case. In fact, that is indeed the case. When you use Named Arguments, you can put them in any order you like. However, once you use one named argument in a function call, you must use named arguments for all subsequent arguments. For example, the following calls are syntactically correct, r = f(1,2,3,4) r = f(1, 2, c:=1, d:=4) but r =f(1,2,c:=1,4) is not. That explains a lot of the confusion I was going through. Thanks for the explanation! Would you know if all varieties of VBApp are like that? That is, Access and PowerPoint? I know vbScript runs through a different interpreter and is useful for networking, scheduling, email, user login information, etc and most vbScript is CreateObject("object") and then using the object.properties / methods. Right now I'm referencing DAO through Excel to grab data and put the information into a few Excel spreadsheets. Things are working out very well, better than I could have ever imagined. VBApp is a little slow, but I'm going to try setting AutoCalc off as I've read in some of Ogrevies statements in this newsgroup. ;-) He reminds me of an ogre and thus I remember his name. I promise not to refer to you as Frito-Lay. Maybe Ruffles, but definitely not Frito-Lay. ;-) You don't have to use Named Argument if you prefer the "C-style" positional syntax, but named arguments make the code vastly more readable. For example, which is more self-documenting? r = f(0,,,,,,,,,,,,,,,,1) or r = f(arg1:=0,arg15:=1) Things sure do look different when I get a grip on what's really happening. Thanks much. -- Jim Carlock http://www.microcosmotalk.com Feel free to post back to the newsgroup! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| find keyboard short-cut to type symbols on keyboard | Excel Discussion (Misc queries) | |||
| Excel 2007 macros - how to merge 5 macros together into one | Excel Discussion (Misc queries) | |||
| Macros warning always shows up, even if all macros removed | Excel Discussion (Misc queries) | |||
| Training: More on how to use macros in Excel: Recording Macros | Excel Worksheet Functions | |||
| Keyboard Macros | Excel Programming | |||
![Jim Carlock[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
