• VBA Return.zip

    2019-06-18 12:44:21
  • 28 AugReturn Stock Data Using Excel VBA Posted at 12:35hinExcel VBAbyRyan Clouse0 Comments 9Likes Howdee! Querying data over the web becomes more common every day. Almost every cloud based program ...

    28 Aug Return Stock Data Using Excel VBA

    Posted at 12:35h in Excel VBA by Ryan Clouse 0 Comments  


    Howdee! Querying data over the web becomes more common every day. Almost every cloud based program has the ability to serve up its data through some sort of API. Sometimes, the data can be extracted to Excel through some sort of reporting interface within the program. However, I’ve found that this functionality is usually difficult to use and/or incredibly slow or doesn’t exist at all. This is where the ability to make an Excel http get request is so valuable.

    In my example today I’ll be building a small excel application that will allow the user to search for a company’s ticker and then return the most recent stock data using an http request. To do this, we will use the site http://dev.markitondemand.com/MODApis/Api/v2/doc. It is a free service that provides the ability to search for tickers and company names and then fetch stock data on that entity.

    HTTP Basics

    Before diving into the technical aspects of an Excel http get request, let’s first explore what http requests are. HTTP stands for “Hypertext Transfer Protocol”. In laymen’s terms, it is a means of communicating between a host and a client.

    Let’s assume you type www.facebook.com into a web browser. You, the client, have just initiated an HTTP GET request to Facebook, the host (for thoroughness, it’s worth mentioning that sites like Facebook that contain sensitive information use an HTTPS request to secure the data being transferred). What you’ve sent is called the “Request” and the HTML page that Facebook sends back is the “Response”.

    The request is sent using a URL (Uniform Resource Locator). While you might be familiar with URL’s, you might not be familiar with their structure. They consist of these components:

    excel http get request

    Protocol is usually HTTP but, as I said earlier, HTTPS is used when the connection needs to be secured (personal data, credit card information, etc.). The host is just that, the domain address a host has chosen for you to reach their server. The port is usually not needed to be explicitly typed. The default port is 80 and the URL implicitly sends that if you exclude it. The resource path and the query are the import parts I want to point out.

    The resource path is instructions sent to the host that tell the server where to navigate to so that your information can be found. Lastly, the query section is always kicked off with a question mark. This section provides any parameters you want to include as well the option to explicitly list the precise data you want returned.

    This is as dep as I’ll go into HTTP since this is enough to get started querying information. However, if you’re planning to develop an application that will make HTTP calls, I recommend reading up on the subject so you can handle several types of responses. Let’s say a user passes a bad request to the program you build; the host will send back an HTTP response but it could be an error 404 response and not include any data. You’ll need to be able to handle that and other issues that could arise. This article on the Mozilla Developer Network is a great place to start.

    Excel HTTP Get Request – Creating the Search

    Now that we know a little about HTTP, let’s dive into the Excel http request using VBA. To get started open a new Excel file and press alt + F11 to open the VBA editor window. The first thing we need to do is add some library references so we can access the controls we will need to make HTTP calls. Click on “Tools” and then “References” and add a check box to Microsoft Scripting Runtime, Microsoft Internet Controls, and Microsoft WinHTTP Services, version 5.1 as pictured below.


    In this application, we want to allow the user the ability to search for a company’s stock ticker. We will then need to pass that data into our code. When my program needs to interact with data on a spreadsheet, I prefer to name the range or cell that it will be interacting with so that, when I’m writing code, it’s evident exactly what data I’m collecting or where I need to write data to. I’ve named a cell “Search_For_Company” and put instructions on the sheet so the user realizes that he/she needs to enter a search term there.


    Now, we need to write the code that will accept this input, and then make an http get request to return potential matches. But, before that, let’s consider the returned results. I user might type in “face” when searching for Facebook. That would return dozens of companies so we need somewhere to store those values temporarily. For this, I’ve created a basic user form with a list box to display the values. It looks like this:


    Typically I’ll break my code down in to sections to discuss, but I think this makes sense to look at the code in its entirety and then explain what’s happening step by step.

    Sub GetCompanyInformation()
    Dim hReq As Object, JSON As Dictionary
    Dim i As Long
    Dim var As Variant
    Dim ws As Worksheet
    Set ws = Sheet1
    'create our URL string and pass the user entered information to it
    Dim strUrl As String
        strUrl = "http://dev.markitondemand.com/MODApis/Api/v2/Lookup/json?input=" & ws.[Search_For_Company]
    Set hReq = CreateObject("MSXML2.XMLHTTP")
        With hReq
            .Open "GET", strUrl, False
        End With
    'wrap the response in a JSON root tag "data" to count returned objects
    Dim response As String
        response = "{""data"":" & hReq.ResponseText & "}"
    Set JSON = JsonConverter.ParseJson(response)
    'set array size to accept all returned objects
    ReDim var(JSON("data").Count, 1)
    i = 1
    'loop through returned data and get Symbol & Name
    For Each Item In JSON("data")
        var(i, 0) = Item("Symbol")
        var(i, 1) = Item("Name")
        i = i + 1
    ufCompaniesReturned.lbCompaniesReturned.List = var
    Erase var
    Set var = Nothing
    Set hReq = Nothing
    Set JSON = Nothing
    If ufCompaniesReturned.Visible = False Then
    End If
    End Sub

    Let’s skip over the variable declarations as we’ll discuss each as they’re used. The first line simply assigns the “ws” variable to the sheet where I’m working, Sheet1 in this case. Next, I dimension a new string variable (I could have dimensions this variable above with the others but this is just personal preference when I’m creating a variable that represents a URL) and assign the URL I’m using to it. Note here that for the query section of the URL, I’m using a combination of text entered and I’m pulling the value the user typed into the range we named earlier. This is why I like to use that approach in my Excel http get request programs. I can easily see that I’m passing a value from Sheet1 that represents a company to search for.

    Next set the Object variable “hReq” equal to a new object in the Microsoft XML Core Services. If you want to read more about what this entails, MSDN has a great write up on it. Suffice it to say this service allows us to send HTTP requests and parse the responses. Now, with that variable we will open a connection and pass it three parameters. We are telling it that we are sending a “GET” request, we pass the URL to use, and lastly, we want to explicitly tell it the call is not asynchronous (also could be said the call is synchronous). This prevents anything further from happening until we get a response from the host. If we left this blank, the default is true. The program could then move on while we are waiting the call and break. There are two more optional parameters in this Open method for username and password, in that order. If that information is required for your request, you’ll need to provide them. They are not required in this example however.

    In the next section of code, we create a response variable and store the response from the request as a string. Since this response is returned in JSON, I’m using the VBA-JSON library to parse the JSON. If you’re unfamiliar with this, click here for my blog post on how to use the library. Note that I wrap my response in some additional text. This is a root JSON object that I wrap the data in so I can perform a count on how many objects were returned, which I do in the next step when I’m resizing the array I declared at the beginning of the code.

    Lastly we will loop through each object returned by our Excell http get request, and assign the company’s symbol and name to the array. Once the array has been populated, we will pass that array to a listbox, dispose of some resources, and finally check if the form is already visible and, if it isn’t, show it.


    Excel HTTP Get Request – Returning the Stock Quote

    Now that we’ve successfully written code that will perform our search query, let’s move on to the http get request to return our stock quote. In the form that pops up, our user will select a company for which they want to return a stock quote. Once they click “OK” we will want the result to be returned. To do this, we need to have this process kick off when the event for the button click occurs. To create a sub routine that subscribes to that event, simply double click the “OK” on your user from inside the VBA editor window.

    We can certainly embed all the code we want to run inside this event and call it a day. However, I prefer to keep my code separate. I’ve created another module called “Functions” and created a function inside to run my Excel http get request stock quote call when passed the correct parameters. That function looks like this:

    Function GetStockData(company As String, companyName As String)
    Dim hReq As Object, JSON As Dictionary
    Dim sht As Worksheet
    Set sht = Sheet1
    Dim strUrl As String
        strUrl = "http://dev.markitondemand.com/Api/v2/Quote/json?symbol=" & company
    Set hReq = CreateObject("MSXML2.XMLHTTP")
        With hReq
            .Open "GET", strUrl, False
        End With
    Dim response As String
        response = hReq.ResponseText
    Set JSON = JsonConverter.ParseJson(response)
        sht.[Last_Price] = JSON("LastPrice")
        sht.[Change] = JSON("Change")
        sht.[Change_Percent] = JSON("ChangePercent") / 100
        sht.[Quote_Time] = JSON("MSDate")
        sht.[Market_Cap] = JSON("MarketCap") / 1000000
        sht.[Volume] = JSON("Volume")
        sht.[Change_YTD] = JSON("ChangeYTD")
        sht.[Change_YTD_Percent] = JSON("ChangePercentYTD") / 100
        sht.[High] = JSON("High")
        sht.[Low] = JSON("Low")
        sht.[Open] = JSON("Open")
    sht.[Search_For_Company].Value = companyName
    End Function

    As you can see, this code structure is very like the previous code structure so I won’t go through every detail. The first thing you might notice is that this isn’t a subroutine. I’ve made this a function and given it two parameters to receive. I first pass in the “company” parameter which will represent the stock ticker symbol. This is the parameter I pass to the strUrl variable to complete my Excel http get request string. Next, I pass in the company Name parameter which will be the formal name returned from our previous query.

    The next difference you might notice is that I don’t wrap the HTTP response like I did previously. Since I know this response will only return one object, I don’t need to count or loop through the response. I can simply parse that response into a dictionary and then access them through the respective keys. Also, I’ve created several more named ranges to write these values to. Again, this is not necessary but I find it helps tremendously with the readability of my code. Lastly, I just assign the formal name I used in the call back to the cell.

    To call this function, I used this code in the button click event handler:

    Dim i As Long
    For i = 0 To ufCompaniesReturned.lbCompaniesReturned.ListCount - 1
        If lbCompaniesReturned.Selected(i) = True Then
            Call GetStockData(lbCompaniesReturned.List(i, 0), lbCompaniesReturned.List(i, 1))
            'do nothing
        End If
    Next i

    This code is pretty straight forward. I’m first looping through each item in my list box and checking to see if that item has a selected property of true. If it does, I’m passing the two values in the list box to my GetStockData function. From there, the code is handled by the function in my Functions module. All of this together ends up with this result:


    I hope this article showed you the power of being able to make http requests from within an Excel application. You can customize the user experience, pull only the data you need, and it’s very fast. As always, the file I worked with today is available for download if you’re a subscriber to my site. It’s free, easy to sign-up, and I won’t be spamming you with dozens of emails each week. If you have questions on anything I went over, please drop them in the comments section below.

  • Function return_range(init_range As Range) As Range Set return_range = init_range.Resize(3, 4) End Function </code></pre> <p>When running the python code below the following traceback appears, ...
  • VBA调用C++DLL

    2021-01-06 05:44:00
    VBA调用C++DLL C++DLL示例 以下建立了一个比较简单的C++的DLL,它的输入是一个字符串,两个double类型的变量,输出double类型的计算结果。 CPP代码: #include stdafx.h #include test_excel.h double fun_property...
  • VBA用户自定义函数

    千次阅读 2018-12-08 22:30:53
    函数是一组可重复使用的代码,...一个VBA函数可以有一个可选的return语句。如果要从函数返回值,则可使用return语句。 例如,可以在一个函数中传递两个数字,然后从函数中返回它们的乘积。 注 - 函数可以返回由...




    注 - 函数可以返回由逗号分隔的多个值,作为分配给函数名称本身的数组。

    在使用函数之前,我们需要定义这个特定的函数。 在VBA中定义函数的最常见的方法是使用Function关键字,后跟一个唯一的函数名称,它可能会也可能不会带有一个带有End Function关键字的参数列表和一个语句,这表示函数的结束。以下是定义函数的基本语法。

    Function findArea(Length As Double, Optional Width As Variant)
       If IsMissing(Width) Then
          findArea = Length * Length
          findArea = Length * Width
       End If
    End Function









  • 22、GoSub...Return 语句 23、GoTo 语句 24、Input # 语句 25、Let 语句 26、Load 语句 27、Name 语句 28、On...GoSub、On...GoTo 语句 29、Open 语句 30、Rem 语句 31、Stop 语句 32、Time 语句 33、While...Wend ...
  • 只是将返回值设置为函数名称仍然与Java(或其他) return 语句完全不同,因为在java中, return 退出函数,如下所示:public int test(int x) {if (x == 1) {return 1; // exits immediately}// still here? return 0 ...

    只是将返回值设置为函数名称仍然与Java(或其他) return 语句完全不同,因为在java中, return 退出函数,如下所示:

    public int test(int x) {

    if (x == 1) {

    return 1; // exits immediately


    // still here? return 0 as default.

    return 0;


    在VB中,如果未在函数末尾设置返回值, exact 等效项将占用两行 . 所以,在VB中,确切的推论看起来像这样:

    Public Function test(ByVal x As Integer) As Integer

    If x = 1 Then

    test = 1 ' does not exit immediately. You must manually terminate...

    Exit Function ' to exit

    End If

    ' Still here? return 0 as default.

    test = 0

    ' no need for an Exit Function because we're about to exit anyway.

    End Function

    既然如此,那么知道你可以像使用方法中的任何其他变量一样使用return变量也是很好的 . 像这样:

    Public Function test(ByVal x As Integer) As Integer

    test = x '

    If test <> 1 Then ' Test the currently set return value

    test = 0 ' Reset the return value to a *new* value

    End If

    End Function

    或者,返回变量如何工作的极端例子(但不一定是你应该如何实际编码的一个很好的例子) - 那个会让你夜不能寐的一个例子:

    Public Function test(ByVal x As Integer) As Integer

    test = x '

    If test > 0 Then



    test = test(test - 1)

    End If

    End Function

  • 在讲VBA之初,我曾经利用很多的时间在讲工作表函数,其实,函数的利用就是做程序的开始,函数也是一个过程,在我的文章中我曾经给过函数的定义: 如果对于唯一性输入值value,输出(或者return)的值也具有唯一性(这种...



    在讲VBA之初,我曾经利用很多的时间在讲工作表函数,其实,函数的利用就是做程序的开始,函数也是一个过程,在我的文章中我曾经给过函数的定义: 如果对于唯一性输入值value,输出(或者return)的值也具有唯一性(这种输入或者输出的值没有个数的限制),那么具有这种变化的输入和输出的关系,就称为输出是输入的函数.在下这个定义的时候,其实就是考虑到函数也是一个过程.是为了实现某个目的的过程。



    VBA也就是Visual Basic for Applications,是一种寄生语言,主要能用来扩展Microsoft Office应用程序功能。在1994年发行的Excel5.0版本中,就具备了VBA的功能。所以我在文章中说:常和EXCEL打交道,掌握VBA是必须的,确实,当你掌握了VBA就会理顺输入和输出的关系,在保持这种关系正确的前提下,利用这种关系,可以让每次的输入都有唯一的输出与之对应,从而让自己的工作变的轻松愉快.比如,只要你把每日的报表放在同一个文件夹下,月报表就会自动的统计出来,这个月如此,下个月还是如此,而且,只要当你日报表放的一样时,输出的月报表必然是一样的.这就是输入和输出关系固定性的体现.

    当使用Excel为平台时,VBA程序还可以利用Excel现有的功能,也就是说让我们可以站在一个小巨人的肩膀上,大大减少开发的周期。VBA语言是相对容易的语言,很容易上手,如果你熟悉VB,那你发现它们在语言方面是相通的,而如果你对Excel比较了解,那你也就很容易理解VBA的各种对象了。工作薄,工作表,行,列,单元格很容易在你的VBA程序中得到自由的发挥,特别是EXCLE的使用者, 很好的利用VBA,可以让自己提高能力,并向自动化办公迈进.











  • 22、GoSub...Return 语句 23、GoTo 语句 24、Input # 语句 25、Let 语句 26、Load 语句 27、Name 语句 28、On...GoSub、On...GoTo 语句 29、Open 语句 30、Rem 语句 31、Stop 语句 32、Time 语句 33、...
  • <p>The VBA compiler cannot currently catch this sort of thing, because <code>Application.Worksheets</code> returns a reference to the <code>Sheets</code> collection, and <code>Sheets.Item</code> can ...
  • 2. passing these to an R function (which might return a list in case of multiple results) 3. organizing results, by writing each list component in various parts of the WB <p>1) 2) are already ok, ...
  • 函数是一组可重复使用的代码,可以在程序中的任何地方调用。这消除了一遍又一遍地编写相同的代码的需要。...一个VBA函数可以有一个可选的return语句。如果要从函数返回值,则可使用return语句。例如,可以在一个函数...
  • VBA基础学习之1.9用户自定义函数 函数是一组可重复使用的代码,可以在程序中的任何地方调用。这消除了一遍又一遍地编写相同的代码的需要。...一个VBA函数可以有一个可选的return语句。如果要从函数返回值,则可使用r...
  • Round in Oracle/VBA

    2014-11-17 18:36:00
    VBA的 Round采用的是银行家算法(rounds to the nearest even number) Round(1.5) = 2 Round(0.5) = 0 在Oracle中实现银行家算法 SQL> create or replace function bankers_round (val number, rnd_digit ...
  • VBA exit退出循环和函数

    千次阅读 2018-03-04 21:13:36
    VBA退出循环用Exit语句,在循环中使用Exit语句相当于C/C++的break语句,而在函数中使用Exit语句则相当于C/C++的return语句。  一、退出循环。exit for退出for循环,exit do用于退出do 或 do while循环: '获取非...
  • 对于函数,我给出了一个更为确切的定义,这个定义我在各个平台上均有发表和阐述,我给出的定义如下:如果对于唯一性输入值value,输出(或者return)的值也具有唯一性(这种输入或者输出的值没有个数的限制),那么具有...
  • vba 根据字节数截取字符串 'str 要截取的字符串 'byteLen 需要截取的字节长度 ' return byteLen长度的字符串 Public Function kiritoruStr(str As String,byteLen as Integer) As String Dim chA As String ...
  • PB 调用VBA方法 个人笔记

    千次阅读 2016-01-11 17:23:08
    PB VBA 常用方法 oleobject ole_word ole_word = Create oleobject IF ole_word.ConnectToNewObject("Word.Application")  MessageBox('提示','您的计算机没有安装word,请您安装后再试!')  RETURN END IF  ole_...
  • 模块2 VBA 挑战 使用tickerIndex用四个不同的数组重构Analysis代码 Sub AllStocksAnalysis()yearValue = InputBox(“您想在哪一年进行分析?”) Worksheets("All Stocks Analysis").Activate Range("A1").Value...
  • 研究了一下VBA序列化JSON,网上没现成的,自己参考人家写的改了一下。 实现功能是,传递一个Range区域的数据,自动生成JSON字符串,行首为列名。 函数如下: Function GetJSON(myrange) Dim returnStr As ...
  • return $strChr; } </code></pre> <p>However, this is returning blank. I have tried echoing this: <pre><code><?php echo EncryptEasy("test"); ?> </code></pre> <p>to no avail. <p>Is anyone ...
  • <div><p>I am beginner to vba selenium. i want to click a button which is inside a frame. i tried with below codes it didn't work. Can anyone help me? Thanks VBA Code: <code>ch.SwitchToFrame (1) ...
  • VBA Excel 对比两列数据

    2015-03-02 10:34:00
    使用 C# 写的数字转字母 private string ToColumn(int p) { if (p > 26) { char A = (char)('A' + (p - 1) / 26 - 1); char B = (char)('A' + (p - 1) % 26);... return new strin...
  • 新文本行的开始以两个专门的字符表示,一个叫做carriage return (回车),另一个叫line feed(换行)。当你使用顺序文件时,你从文件的开头始,一个字符一个字符的向前移动,一行接一行,直到文件的结尾。顺序文件...
  • Excel VBA学习笔记-3

    2012-08-18 22:58:16
    Visual Basic 语言参考的关键字分类篇 一.控制流关键字总结   1.分支   ...i.GoSub...Return 语句 ...在一个过程中跳到另一个子程序中执行,执行后再返回。...一个子程序中可以包含一个以上的 Return 语句,...
  • return rtna </code></pre> <p>When I import this function the two optional arguments are imported as required: <pre><code> Function xl_arange(startnum, stopnum, step, dtype) If TypeOf Application....
  • <p><a href="https://stackoverflow.com/questions/9444887/avoid-extra-carriage-return-in-print-statement-with-visual-basic">StackOverflow explanation</a></p> <p><a href=...
  • VBA-判读文件是否存在

    2015-09-17 13:50:09
    '判断文件是否存在 ...'return true:文件存在 false:文件不存在 Function IsFileExists(ByVal strFileName As String) As Boolean If Dir(strFileName, 16) <> Empty Then IsFileExists = True E
  • 对于函数,我给出了一个更为确切的定义,这个定义我在各个平台上均有发表和阐述,我给出的定义如下:如果对于唯一性输入值value,输出(或者return)的值也具有唯一性(这种输入或者输出的值没有个数的限制),那么具有...



1 2 3 4 5 ... 10
收藏数 181
精华内容 72