セルへの値の入力

VBAを使ったセルの値への入力をしていきます。

まずは先ほどのExcelブックに新規にシートを追加します。

追加したシートに次の画像のようにセルに値を入力していきます。実際に入力する値は基本的にどんなものでも構いません。

次に「開発」タブのマクロボタンを押し「Macro1」というものが選択されているので「編集」ボタン押します。

すでにマクロを記録するために作成した「Module1」というファイルが表示されるので以下のコードの追記します。

'
' Macro1 Macro
' テスト用マクロ
'
    MsgBox "Hello World!"
'
End Sub
'前回作成したHello Worldを出力するプログラムの下に書いていきます
Sub EnterCellValue()
'先頭をアポストロフィーにするとコメントアウトできます
'ExcelのBook名を指定します
Dim sample_book As Workbook
Set sample_book = Workbooks("SampleBook.xlsm")
'ExcelのSheet名を指定します
Dim sheet_a As Worksheet
Set sheet_a = sample_book.Worksheets("Sheet1")
'Sheet2のE4の値をsheet_aの3行3列目に代入します
sheet_a.Cells(3, 3) = sample_book.Worksheets(2).Range("E4")
'Sheet2のC6からE6までの値をsheet_aのB6,C6,D6にそれぞれ代入します
sheet_a.Range("B6,C6,D6").Value = ThisWorkbook.Worksheets("Sheet2").Range("C6:E6").Value
'Sheet2の8行2列から8行6列までの値をsheet_aの8行2列から8行6列に代入します
sheet_a.Range(sheet_a.Cells(8, 2), sheet_a.Cells(8, 6)).Value = Worksheets("Sheet2").Range(Worksheets("Sheet2").Cells(8, 2), Worksheets("Sheet2").Cells(8, 6)).Value
End Sub

編集画面だと以下のようになります。

ここでExcelBookのウィンドウを開き「Sheet1」を選択後、再度「開発」タブから「マクロ」をクリックします。

すると先ほど作成した「EnterCellValue」というマクロが表示されるので実行を押します。

すると「Sheet1」に値が入力されます。

ここから解説に入ります。

単純にセルに値を入力といってもやり方はいろいろあるので初見だと良く分からないものが多いのではないかとも思います。

最初にSub ~ End Subですがこれは「Subプロシージャー」と呼ばれるものです。構文(書き方)としては

Sub プロシージャー名()

End Sub

となります。

Excelマクロを実行する場合これが一つの塊となって実行されます。今回は「ExcelCellValue」というのがプロシージャー名となります。プログラミングの命名規則をご存じの方ならこれはパスカルケースだとすぐにわかると思います。名前の付け方は自分がわかれば良いのですが適当につけると量が増えたときに困るので最初からある程度決めておくと良いと思います。ちなみにプロシージャー名はパスカルケースですがそれ以降(変数部分)はスネークケースと呼ばれる命名規則に則って書いています。あくまで個人の趣味です。

次にDim~やSet~と書かれた部分ですがここが変数宣言になります。通常の数字や文字の変数はDimだけでも良いですが今回はそれだと上手くいかないのでDim+Setで記述しています。そして最初の部分ですが

'先頭をアポストロフィーにするとコメントアウトできます
'ExcelのBook名を指定します
Dim sample_book As Workbook
Set sample_book = Workbooks("SampleBook.xlsm")

これでExcelのBook名を指定しています。これは何かというと実務ではExcelファイルを複数開きながら作業する場合があり、その時に異なるExcelファイルで同じシート名を使用していると名前の衝突が起こるため事前にBook名というのを指定して衝突を避けようということです。これも構文にすると

'ExcelのBook名を指定します
Dim 変数名 As Workbook
Set 変数名 = Workbooks("(拡張子を含めたExcelファイル名)")

となります。

セルへの値の入力」への274件のフィードバック

  1. Hello there, You have done an excellent job. I will certainly digg
    it and personally recommend to my friends. I am sure they’ll be benefited from this web site.

  2. magnificent points altogether, you simply won a new reader.
    What could you recommend in regards to your submit that you simply
    made a few days in the past? Any certain?

  3. Hey I know this is off topic but I was wondering if you knew of
    any widgets I could add to my blog that automatically tweet my newest twitter updates.
    I’ve been looking for a plug-in like this for
    quite some time and was hoping maybe you would
    have some experience with something like this. Please let me know if you run into anything.
    I truly enjoy reading your blog and I look forward to your new updates.

    Also visit my web blog: https://www.tarikubogale.com/the-weekday-plan-from-the-cyclical-ketogenic-diet-2/

    1. Twitter to WordPress…?
      There are plugins that do the opposite, but currently the only thing I can think of is to embed a timeline.

  4. Hi there to all, for the reason that I am really eager of reading this web site’s post to
    be updated regularly. It carries fastidious data.

  5. What i do not understood is actually how you are now not really much more
    smartly-preferred than you may be now. You are so intelligent.
    You recognize therefore considerably relating to this
    topic, made me in my opinion believe itt from a lot of numefous angles.
    Its liie men and woimen are not fascinated ecept it is one thing to accomplish
    with Lady gaga! Your individual stuffs outstanding. All the time maintain it up!

    https://australianessaywritingservice.info/expository-essay.html
    cheap essay help
    cheap essawy help
    https://australianessaywritingservice.info/essay-on-goal.html https://australianessaywritingservice.info/

  6. Hi there very cool site!! Guy .. Beautiful .. Wonderful ..
    I will bookmark your web site and take the feeds additionally?I’m satisfied to search out a
    lot of helpful info right here in the put up, we want work out extra strategies
    in this regard, thank you for sharing.

    My blog post; SlimForm Keto Reviews

  7. Having read thks I thought it was veery enlightening.

    I appreciate you finding the time and effort to put this information together.
    I once again find myself spending a lot of time both reading and leaving comments.
    But so what, it was still worthwhile!
    https://gennieq3fr.medium.com/conclusion-aeff89acdac9
    paper writer
    paper writer
    https://kayleenhqf.medium.com/veterans-day-essay-contest-ce0b866ff825 https://ingamarad15nnh.medium.com/scholarship-essay-contest-dc2ca5e252d8

  8. Hi this is somewhat of off topic but I was wondering if blogs use WYSIWYG editors or if you have to manually code with HTML.

    I’m starting a blog soon but have no coding skills so I wanted to get advice from someone with experience.
    Any help would be greatly appreciated!

    my webpage: BHB Fit Review

  9. I really like your blog.. very nice colors & theme. Did you create this website yourself or did you hire someone to do it for you?
    Plz respond as I’m looking to construct my own blog and
    would like to know where u got this from. many thanks

  10. Appreciating the persistence you put into your skte and
    in depth information you present. It’s great to come across a blog every once in a while thgat isn’t the same out of date rehashed information. Fantastic read!
    I’ve saved your site and I’m including your RSS feeds
    to my Google account.
    https://highqualitywritingservice.com/
    order custom terrm paper
    order custom term paper
    https://highqualitywritingservice.com/ https://highqualitywritingservice.com/

  11. Hi, i think that i saw you visited my weblog thus i came to “return the
    favor”.I am trying to find things to improve my website!I suppose
    its ok to use some of your ideas!!

  12. Wow that was strange. I just wrote an really long comment but after I clicked submit my comment didn’t show up.
    Grrrr… well I’m not writing all that over again. Anyway, just wanted to say excellent blog!

  13. obviously like your web site butt you have to check the spelling on quite a few of your posts.
    A number of them aree rife with spelling problems
    and I in finding itt very bothersome to inform the reality then again I’ll
    definitely come back again.
    https://writingmypaper.info/marketing-plan-for-business-plan.html
    buy essays online
    buy essays online
    https://writingmypaper.info/uk-essays-writing.html https://writingmypaper.info/how-to-write-a-tok-essay-2018.html

  14. Hello to every body, it’s my first go to see of this blog; this weblog carries amazing and actually fine data in favor of readers.

  15. What’s up to all, because I am truly eager of reading
    this website’s post to be updated daily. It contains pleasant stuff.

  16. Very nixe post. I just stumbled uon your wenlog and wished to say
    that I have truly enjoyed surfing around your blog posts.

    In any case I will be subscribing to your feedd and I hope you write again very soon!
    https://collegeapplicationessaywritingservice.info/example-of-acknowledgement-in-term-paper.html
    english essay writing
    english essay writing
    https://collegeapplicationessaywritingservice.info/romeo-and-juliet-essay-introduction.html https://collegeapplicationessaywritingservice.info/

  17. Hi excellent website! Does running a blog like this take a lot of
    work? I’ve virtually no understanding of coding but I was hoping to
    start my own blog iin the near future. Anyway, should you
    have anny ideas or tips for neew blog owners please share.
    I understand this is off subject however I just had to ask.

    Kudos!
    https://persuasiveessays.info/
    write essays for me
    write essays foor me
    https://persuasiveessays.info/marketing-research-literature-review-sample.html https://persuasiveessays.info

  18. Its not my first time to pay a visit this site,
    i am visiting this web site dailly and take pleasant information from here every day.

  19. I get pleasure from, result in I found just what I used to be having a look for.
    You have ended my four day lengthy hunt! God Bless you man. Have a great day.
    Bye

  20. My spouuse and I absolutely love your blog and find nearly
    all of your post’s to bee just what I’m looking for.

    cann you offer guest writers to write content for you personally?
    I wouldn’t mind creating a post orr elaborating on some of
    tthe subjects you write in relation to here.

    Again, aawesome site!
    https://writeanessayforme.info/918-poem-critiques.html
    custom dissertation writers
    custom dissertation writers
    https://writeanessayforme.info/1960-order-thesis-proposal.html https://writeanessayforme.info/3096-write-my-research-paper-for-me-cheap.html

  21. First of all I want to say awesome blog! I had a
    quick question that I’d like to ask if you don’t mind. I was interested to know how you center yourself and clear your head before writing.
    I’ve had a difficult time clearing my thoughts in getting my thoughts out.
    I truly do take pleasure in writing but it just seems like the
    first 10 to 15 minutes are generally wasted simply just trying to
    figure out how to begin. Any recommendations or hints?
    Kudos!

  22. Thanks a lot for sharing this with all folks you actually
    recognize what you’re talking about! Bookmarked. Please also talk over with
    my web site =). We will have a link exchange arrangement among us

  23. Hi, Neat post. There’s a problem with your website in internet explorer,
    would check this? IE nonetheless is the market chief and a big element
    of other folks will pass over your fantastic writing due to this problem.

  24. I know this if offf topic but I’m looking into starting my own blog and was wondering wjat all is
    needed to get set up? I’m assuming having a blog like yoours would cost a pretty penny?
    I’m not very web savvy so I’m not 100% certain. Any suggestions or advice would be greatly
    appreciated. Cheers
    https://buyessayonlinecheap.info/type-of-research-papers.html
    paper for writing
    paper for writing
    https://buyessayonlinecheap.info/characteristics-of-academic-writing-style.html

  25. I was suggested this website by my cousin. I’m not sure
    whether this post is written by him as no one else know such detailed about my difficulty.
    You are wonderful! Thanks!

  26. An outstanding share! I have just forwarded this onto a
    friend who was conducting a little research on this.
    And he actually ordered me dinner due to the fact that I stumbled upon it for him…
    lol. So allow me to reword this…. Thanks for the meal!!
    But yeah, thanx for spending the time to talk about this subject here on your web site.

  27. Thank you, I’ve recently been searching for information approximately
    this topic for ages and yours is the greatest I’ve found
    out so far. But, what concerning the conclusion? Are you certain concerning the source?

  28. Wonderful blog! I found it while searching on Yahoo News.
    Do you have any tips on how to get listed in Yahoo News? I’ve been trying for a while but I never seem to get there!
    Thanks

    Here is my blog … mtpolice24

  29. Thank you so much pertaining to giving my family an update on this subject on your site.
    Please know that if a new post becomes available or if any variations occur about the current submission, I would be interested in reading more and knowing how to make good use of those approaches
    you write about. Thanks for your time and consideration of
    people by making your blog available.

    my webpage; NutraKinetic Keto

  30. I’m really loving the theme/design of your site. Do you ever run into any internet browser compatibility problems?
    A number of my blog readers have complained about my
    website not working correctly in Explorer but looks
    great in Safari. Do you have any solutions to help
    fix this problem?

    Here is my webpage: 메이저사이트

  31. I was curious if you ever thought of changing the page layout of your website?
    Its very well written; I love what youve got to say. But maybe you
    could a little more in the way of content so people could connect with
    it better. Youve got an awful lot of text for only having one or 2 pictures.
    Maybe you could space it out better?

  32. Great web site you have here.. It’s difficult to find high-quality writing like yours nowadays.
    I honestly appreciate individuals like you! Take care!!

    Here is my web blog … Superior TRT

  33. Greetings, There’s no doubt that your web site may be having browser compatibility problems.

    Whenever I look at your website in Safari, it looks fine however
    when opening in Internet Explorer, it’s got some overlapping issues.
    I merely wanted to give you a quick heads up! Apart from that, excellent site!

    Stop by my web blog; Cyalix Ingredients

  34. Please let me know if you’re looking for a article author for your site.
    You have some really good articles and I think I would be a good asset.

    If you ever want to take some of the load off, I’d love to write some content
    for your blog in exchange for a link back to mine.
    Please blast me an e-mail if interested. Regards!

    Here is my website: ICureO CBD Oil

  35. I simply needed to thank you very much all over again. I do not
    know the things I could possibly have used in the absence of those tricks revealed by
    you over such a field. Certainly was a very difficult matter in my view, but noticing the specialized
    fashion you dealt with it took me to weep over happiness.

    I’m happier for the support and expect you comprehend what an amazing job you are always carrying
    out teaching people today by way of your webpage.
    Most probably you have never got to know any of us.

    Here is my webpage :: http://slayeveryday.com/2020/10/23/drug-education-and-children-home-v-school-7/

  36. You have made some really good points there.
    I checked on the net to learn more about the issue and
    found most individuals will go along with your views on this website.

    Feel free to visit my page: MiracleWatt Device

  37. This is the perfect website for anybody who really wants to understand this
    topic. You know so much its almost hard to argue with you (not
    that I personally would want to…HaHa). You
    definitely put a brand new spin on a topic that’s been discussed for a long time.
    Great stuff, just great!

    My web page :: Libo Max Male Enhancement

  38. I am regular visitor, how are you everybody? This article posted at this website is actually pleasant.

    Here is my site … خرید بک لینک تضمینی (Elena)

  39. Hello, every time i used to check weblog posts here in the early hours in the morning,
    because i enjoy to gain knowledge of more and more.

    Here is my site – خرید بک لینک دائمی (Shanice)

  40. Good day I am soo glad I found your web site, I really found you by mistake, while I was looking
    onn Aol for something else, Nonetheless I am here now and
    would just like to say thanks for a fantastic post and a all round enjoyable blo (I also love
    the theme/design), I don’t have time to browse it all at
    the minute but I have saed it and also included your RSS feeds, so when I have time I will bee baxk to read mucfh more, Please
    do kwep up thee great work.
    http://breadexperts.us/__media__/js/netsoltrademark.php?d=writingmypaper.com
    cheap essay writing service
    cheap essay wriying service
    http://www.101-fm.org/__media__/js/netsoltrademark.php?d=writingmypaper.com http://newcouponalerts.net/__media__/js/netsoltrademark.php?d=writingmypaper.com

  41. For newest news you have to pay a visit the web and on the web I found
    this web site as a most excellent site for newest updates.

  42. We wish to thank you once more for the wonderful ideas you offered Jesse when preparing her post-graduate research in addition to, most
    importantly, pertaining to providing all the ideas in one blog post.
    Provided that we had been aware of your blog a year
    ago, we may have been saved the unwanted measures we were selecting.

    Thank you very much.

  43. Nice weblog here! Also your website loads up fast!
    What web host are you the use of? Can I get your affiliate hyperlink on your
    host? I desire my web site loaded up as fast as yours lol

    my blog :: fun88

  44. Thanks so much with regard to giving me an update on this theme on your site.
    Please know that if a new post becomes available or in the event any modifications occur about the current post, I would be considering reading more and finding out how to make good utilization of those methods you discuss.
    Thanks for your efforts and consideration of other men and women by making
    this website available.

  45. Hurrah! At last I got a web site from where I can truly
    get valuable facts concerning my study and knowledge.

    Feel free to visit my web-site: w88

  46. Hey there I am so excited Ifound your website, I really found you by mistake, while I was looking on Yaho for something else, Anyways I am here now and would just like
    to say kudos foor a incredible post and a alll round
    interesting bllog (I also love the theme/design), I don’t have time too browsee it all at tthe
    moment but I have saved it and also added in your RSS
    feeds, so when I have time I will be back to read much more, Please do keep up the fantastic
    work.
    https://clients1.google.com.my/url?q=https://kratomwwwtea.com
    Mozelle https://clients1.google.co.ve/url?q=https://bestwwwkratom.com

  47. I’ve been exploring for a bit for any high-quality artyicles or weblog posts in this kind of area .
    Exploring in Yahgoo I at last stumbled upon this website.

    Studying this information So i am happy tto express that I have a vvery excellent unxanny feeling I cae upon exactly what I needed.
    I so muxh without a doubt will make certain to don?t disregard
    this webb site andd provides itt a glance on a continuhing basis.

    https://buyanessayonlinecheap.com/

    website
    https://buyanessayonlinecheap.com/ https://buyanessayonlinecheap.com

  48. There’s certainly a great deal to know about this
    topic. I like all the points you have made.

  49. It’s really very complicated in this active life to listen news on TV, thus I just use
    web for that purpose, and take the newest information.

コメントは受け付けていません。